Forum Discussion
Solved
Calculate value of a cell based on the 'text value' assigned on other cells
Hi, I'd like to be able to calculate the value of cells in a column based on the what has been selected on other columns.
Each item has a starting numerical value (column be), that value should be modified based on how the columns from C to G (Conditions 1-5) have been filled. If the value in one of the those Condition columns is 'yes' then a coefficient should be applied to the initial number, if there's a no or is blank then no coefficient should be applied.
Coefficients are: 0.8 for Condition 1, 0.7 for Condition 2, 0.6 for Condition 3, 0.5 for Condition 4, 0.9 for Condition 5.
So in the sample table below Item 1 (Cell A2) has a starting score of 23. This score should be multiplied by 0.8 (Yes on Condition 1 column, cell C2) x 0.6 (Yes on Condition 3, cell E2) x 0.5 (Yes on Condition 3, cell F2) x 0.9 (Yes on Condition 3, cell G2) and thus returned 4.968 on Cell H2.
Is it also possible to round this 4.968 to the nearest integer?
| Item ID | Starting Sore | Condition 1 | Condition 2 | Condition 3 | Condition 4 | Condition 5 | Final Score |
| Item 1 | 23 | Yes | No | Yes | Yes | Yes | |
| Item 2 | 13 | Yes | Yes | No | No | No | |
| Item 3 | 15 | Yes | No | No | Yes | Yes | |
| Item 4 | 20 | No | No | Yes | Yes | Yes | |
| Item 5 | 11 | No | Yes | Yes | No | No | |
| Item 6 | 10 | Yes | Yes | No | Yes | No | |
| Item 7 | 14 | No | Yes | Yes | Yes | No | |
| Item 8 | 13 | Yes | No | No | Yes | No | |
| Item 9 | 18 | No | No | No | Yes | Yes |
I inserted a row with the coefficients at the top.
3 Replies
=ROUND(B3*PRODUCT(IF(C3:G3="Yes",C$1:G$1,1)),0)- Thanks for the reply, the formula doesn't seem complete. If I apply this formula the different coefficients won't be applied to the initial starting score. Is there a way I can have the starting score multiplied by the different coefficient when the value in the relevant cell is 'yes'?
I inserted a row with the coefficients at the top.
