Another Excel Problem...

[amy21322]

Hi,
Once again, I'm hoping someone can give me a hint with this Excel 2003 problem.
I have one cell that has a (currency) value derived from a simple subtraction of two other (currency) cells.
When I try to multiply the value in this cell, (eg =A22*52) it is always 'out' by several cents.
For example, the result of multiplying $1076.63 by 52 gives me $55,984.60.
However, when I use (eg =1076.63*52), the answer is correct.
Any help, once again, would be appreciated.
Cheers
Amy

 

[Propertunity]

When you 'right mouse click' on the cells concerned and choose 'format', make sure under the currency option that you have asked for accuracy to '2' decimal places.

(It sounds like you only have '1' in the formating of at least one of the cells)

 

[AlmostBob]

displayed value vs, calculated value

cell a2 shows 1076.63 because it is formatted to 2 decimal places
the value in a2 is (example) 1076.6349 which rounds down on display but the extra $0.0049 ( half a cent ) gets included in the later calculation and throws out the expected total (the dodgy one is probably more accurate)

change the formula used to calculate A2
old, +a1*1.014
new +round(a1*1.014,2) <== just wrap it in a round(number,decimals)
which will round the value in a2 to 2 decimals (or however big the number after the comma) and throw away those fractions of a cent
or
change the display of A2 to show more decimal places, if those fractional cents are important

banks will throw away those fractions of a cent if it is to your advantage(paying you interst), but will keep them if it is to their advantage(charging you interest)

 

[amy21322]

Thanks, Prop. and AB.
I'll give those hints a go and see what happens.
Many thanks
Amy