(Msg. 1) Posted: Thu Jul 24, 2008 7:23 am
Post subject: Excel calculation Add to elertz Archived from groups: microsoft>public>excel>crashesgpfs (more info?)
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
(Msg. 2) Posted: Thu Jul 24, 2008 7:30 am
Post subject: RE: Excel calculation Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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.
--
Allllen
"Sporty Q" wrote:
> 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
>
>
(Msg. 3) Posted: Thu Jul 24, 2008 3:46 pm
Post subject: RE: Excel calculation Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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
"Sporty Q" wrote:
> 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
>
>
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Search
Profile
Private Messages
Log in/Register/Password
Office