Formula rounding incorrectly but not consistently

[Guest]

I am trying to calculate a discount on an exchange rate (rate x .75%) but
when I do a manual calculation to verify the figures, some are rounding up or
down without rhymn or reason. Example:

Rate Manual Calc Excel Calc Should Be
1.8775 1.891581 1.8915 1.8916 (didn't round up)
1.2995 1.309246 1.3093 1.3092 (rounded up but shouldn't have)

I have formatted the cells and verified the correct number of decimals.

My exchange formula is: rate-(rate*discount)

Should I have to enter a rounding formula? I did create one and it worked
by itself but when I try to combine it with my exchange formula, it's not
working. I could have added the formula's incorrectly but am not sure how to
correct it.

The rounding formula is: If(rate>=5,1)*or(rate<5)

Thanks for any assistance that can be provided. Katherine

 

[Pete_UK]

You can make use of Excel's ROUND( ) function, to amend your formula
thus:

=ROUND(rate-(rate*discount),4)

which will round to 4 decimal places.

Hope this helps.

Pete

 

[Guest]

You've missed my point. Either 'ForEx Quotes Tables'!S10 <> 1.8775 or
'Client Discount'!D4 <> 0.75% or both, although they round to those values.
Your manual calculation is using the rounded values rather than the actual
values in the cells. Assuming that is the appropriate thing to do, then
your formula needs to be

=ROUND('ForEx Quotes Tables'!S10,4)*(1+ROUND('Client Discount'!D4,4))

Jerry

Some of the calculations were Buy (-) and some were Sell (+) so my formula
changes depending on which one I'm doing. I am actually not entering the
discount, but rather am calculating by pointing to the cell/sheet that
contains the discount which may change periodically.

I attempted the formula you mentioned [entered as ='ForEx Quotes
Tables'!S10*(1+'Client Discount'!D4) ] but it still gives me the incorrect
result of 1.8915 instead of 1.8916 (ie. Rate 1.8775 Manual Calc 1.891581
Excel Calc 1.8915 Should Be 1.8916).

I also tried rounding and it gives me 1.8915 instead of 1.8916:
=ROUND('ForEx Quotes Tables'!S10*(1+'Client Discount'!D4),4

Can you provide any other suggestions on how to obtain the correct rounded
rate? Thanks, Katherine

Your manually calculated results appear to be the result of
rate+(rate*discount) instead of your posted formula. This simplifies to
rate*(1+discount)
and Excel will calculate it to machine accuracy and round it correctly.
Assuming that the discount of 0.75% is exactly entered, the only place the
calculation could go wrong is if rate is calculated rather than entered, and
that calculated result has more than 4 decimal places. Note that formatting
a cell does not change the underlying value (which is what formulas will
use). If you want to calculate with the formatted value, you either need to
check "precision as displayed" in Tools|Options|Calculation (be very careful
with this, as it will impact all calculations, not just these), or explicitly
round the necessary values in subsequent calculations using Excel's ROUND()
function.

Jerry