SOLVED

Contributor

# 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% 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:

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)
Solution

# Re: Background Formula?

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.

# Re: Background Formula?

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