price change affects cost previous data

[avanti13]

Hi, I am using Excel 7. I am working on a database to inventory maple syrup.
I use this formula to set the price according to grade. GA, AM, ect.

IF(D22="GA",$N$3,IF(D22="AM",$N$4,IF(D22="DR",$N$5,IF(D22="VD",$N$6,IF(D22="OF",$N$7,"")))))

and then calculate the running cost, weight/barrel * price/lb. My problem is when the set price is changed for a grade the whole spread sheet reflects the new price and the total cost is changed. How do I prevent the price change from affecting the previous data? Thanks. Avanti13

 

[candybg]

Depending on how your data is set up, one way would be to use a new cell for the new set price and adjust to formula going forward. Rather then N3 for the GA price, maybe you could use O3, etc.

 

[avanti13]

Hi, I am trying to avoid changing the formula as the program is used in the field by non programmers. Thanks for the reply

 

[candybg]

Then you could change all previous formula results to a VALUE (Copy, Paste-special, Values) so it won't re-computer when your figure changes.