retaining equations

**sisterpete** · 12-21-2007, 06:41 PM

made an estimate sheet with the following columns.

a= qty
b= item
c= item amount (i input this amount)
d= ext amount (=A18*C18 / qty x item amount)
e= discounted price (=D18*(1-F18) / % in column F x ext amount)
f= % off (i input this amount ie 25% off)

all works ok if i just use things as is....

here's where i have problems....

let's say i have an item that is qty of 1, item amount 1200.00 and i give 16% off, which comes to 1008.00. so i decide to round this down to 1000.00 even.... no problem, i just fo to my e column and type in 1000.00 all is well.

but.... if i need to go back and change something on that column... lets say it been awhile since the quote, i need to change the price to 1300.00 as the item went up.... now when i go to the percent column and add my percent, the discounted price does not change as it has lost the (=D18*(1-F18) since i typed into that cell prior.

i guess what i'm asking is there a way to have excel "retain" the initial equation if i needed it, after i have typed into the cell?

i hope this is clear.....

thanks for your help

**protonLeah** · 12-22-2007, 03:07 AM

No.
You will have to decide either to:

accept the calculated value as is,
round the calculated value to the nearest 100 using "=floor()" or "=round()" function,
redesign your sheet with columns for qty, unit price, cost of order, discount rate(%), discount amt (cost X d-rate), additional discount ($) and final sale price (ttl cost - discount amt - additional discount)

**sisterpete** · 12-22-2007, 05:03 AM

i guess i'll try the round... can i round to the nearest number, be it up or down? i'm grateful for any help... pretty new to all this code stuff

thanks for your help

**protonLeah** · 12-22-2007, 06:02 PM

I don't know what your sheet looks like but, I would suggest adding the additional columns. That would make future mods and tweaks easier. However, here example of floor and round: