Oct 07 2022 09:25 AM
I have two tabs on my spreadsheet - one for my data and one for my values. In my data sheet, I have a chart like this:
Section = 25% | A | B | C | D | E | F | G |
1 | Inadequate | Below Std | Standard | Below Std | Superior | Rating | |
2 | Question 1 | x | |||||
3 | Question 2 | x | |||||
4 | Question 3 | x | |||||
5 | Question 4 | x | |||||
6 | Question 5 | x | |||||
7 | |||||||
8 | |||||||
9 | PROJECT TOTALS | 50 | 75 | 100 | 125 | 150 |
Here are my values:
Inadequate: 10
Below Std: 15
Standard: 20
Above Std: 25
Superior: 30
People will answer the questions by placing an "x" in their chosen category. Then, in the rating column, I need a formula to do this: (Project Total for the selected category) x (the section percentage) x (the value from the chart). For example, in question 1, inadequate was chosen, so 50 x 25% x 10. No problem there. BUT... if the rating for a particular question doesn't appear high enough (meaning, it still need to be "Inadequate" but maybe question 1 should be valued higher than question 2) and someone wants to change it, is there a way to keep the formula (per the example, in G2) in the background so that it isn't deleted, but it just isn't used and they can type a different number into the cell (G2)? Then, if they delete the number, the formula will show and be used again?
Oct 07 2022 09:47 AM
SolutionNo, that is not possible.
I'd use column H as "Corrected Rating".
If the calculated rating in column G is OK, leave column H blank.
If you want a "manual" rating, enter it in column H.
To calculate the total score, use the value in column H if available, otherwise the value in column G.
Oct 07 2022 09:50 AM
Oct 07 2022 09:47 AM
SolutionNo, that is not possible.
I'd use column H as "Corrected Rating".
If the calculated rating in column G is OK, leave column H blank.
If you want a "manual" rating, enter it in column H.
To calculate the total score, use the value in column H if available, otherwise the value in column G.