Excel calculation

  • Thread starter Thread starter Sporty Q
  • Start date Start date
S

Sporty Q

I need help with the listed calculation. I have a group that total up to 39
hours and I am dividing it by 63. The percent that I get is 0.619 which
would let me to round up to .62%. Now if I mutiply .62 by $8,961.97 I should
get $5,556.42, but the cell provides $5,547.89. Can anyone help me?

PROGRAM # of Hours % $8,961.97
Group 1 39 0.62(39/63) $5,547.89
 
a couple of options

1) for the 62%, instead of doing =39/63, you should do
=ROUND(39/63,2), or =ROUND(A1/B1,2)
when you multiply this by 8,961.97 you will get the answer you need

2) in XL2003 use tools > options > calculation > precision as displayed. not
sure where this has gone in xl07 but probably somewhere in the options on the
main button.

First option is best.
 
At least one of A1:B1 is the result of an unspecified formula whose result
differs from your posted values beyond the 15th figure. That the formula
result may differ slightly from what you expected is normal behavior for
computers, where you should never test for strict equality of floating point
numbers; instead test whether their difference is suitably small (or
equivalently test whether their rounded values are equal).

The zero result in D1 is not accurate; but results from an assuption by MS
that final subtractions of numbers that are equal to 15 figures should be
considered zero. The comparison in C1 is similarly fuzzed. The subraction
in E1 returns the actual difference because wrapping it in an IF function
means that the subtraction is not the final operation -- you could simplify
to =(A1-B) and still get the actual difference. Similarly
=IF(A1-B1=0,"yes","no") in C1 would detect the difference.

AFAIK, the fuzz factor that causes this seemingly inconsistent behavior is
only documented in
http://support.microsoft.com/kb/78113
where it MS calls it an "optimization", despite the fact that it seems to
cause more confusion than it resolves.

Jerry
 
Back
Top