Calculate a Score from Yes or No Conditions in WPS Spreadsheets
Question details
Calculate a final score by adjusting a starting value in column B with coefficients from row 1 only when the corresponding cells in columns C:G contain Yes, leaving No and blank cells unchanged, with optional rounding.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user needs a formula that applies multiple row-1 coefficients to a base score based on Yes/No conditions in adjacent columns.
- Observed behavior
- The final result should reflect only the coefficients for columns marked Yes and may be rounded to the nearest whole number for a clean score.
Enter the coefficients 0.8, 0.7, 0.6, 0.5, and 0.9 in cells C1:G1 and keep the starting values in column B. Confirm that the condition cells in columns C:G contain only Yes, No, or blank values.
Use PRODUCT and IF to Multiply Coefficients for Yes Conditions and Round the Result
Use this one-formula method when you want a compact calculation that checks all condition columns, applies only the matching coefficients, and returns a rounded final score.
The formula multiplies the starting value by each coefficient in row 1 where the corresponding condition cell equals Yes. For No or blank cells, it multiplies by 1, leaving the value unchanged. The ROUND function then rounds the result to the nearest whole number.
Place the coefficients 0.8, 0.7, 0.6, 0.5, and 0.9 in C1:G1, and place the starting value for each record in column B.
Click the first cell in the result column where the calculated score should appear, such as H3.
Type =ROUND(B3*PRODUCT(IF(C3:G3="Yes",C$1:G$1,1)),0) and press Enter.
Drag the fill handle from the result cell down through the remaining rows to calculate the score for each record.
Calculate Yes/No Conditional Scores Directly in WPS Spreadsheets
WPS Spreadsheets supports the same PRODUCT and IF formula logic as Excel, so you can calculate conditional Yes/No scores and round the result without switching applications.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office and open the Excel or WPS spreadsheet file containing the starting values and condition columns.
- 2. Select the result cell: Click the first cell in the result column, such as H3, where the conditional score should appear.
- 3. Enter the formula: Type =ROUND(B3*PRODUCT(IF(C3:G3="Yes",C$1:G$1,1)),0) and press Enter.
- 4. Copy the formula down: Use the fill handle to copy the formula from the first result cell down through the remaining rows.
Frequently Asked Questions
Does the formula treat blank cells the same as No?
Yes. The IF condition only returns the matching row-1 coefficient when a cell equals Yes; blank and No cells return 1, so they do not change the starting value.
How can I keep decimal places instead of rounding to a whole number?
Remove the ROUND function or change its second argument. For example, use =B3*PRODUCT(IF(C3:G3="Yes",C$1:G$1,1)) to keep decimals, or =ROUND(...,2) to round to two decimal places.
Do I need to enter this formula as an array formula?
No. In WPS Spreadsheets and Excel, the PRODUCT and IF combination works as a normal formula. Just press Enter after typing it.
Can I place the coefficients in a different row?
Yes. Move the coefficients to another row and update C$1:G$1 in the formula to match that row, keeping the dollar signs so the row reference stays locked when you fill down.
Why is my calculated score not changing for rows with Yes conditions?
Check that the condition cells contain Yes without extra spaces, that the coefficients in C1:G1 are numeric values, and that the formula references the same row as the starting value.
