Background Formula?


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%ABCDEFG


 InadequateBelow StdStandardBelow StdSuperiorRating
2Question 1x     
3Question 2 x    
4Question 3  x   
5Question 4   x  
6Question 5    x 
9PROJECT TOTALS5075100125150 

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?




2 Replies
best response confirmed by Kim-Kay (Contributor)


No, 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.

Thank you, Hans. I didn't think so, but thank you for the work-around. That should work.