Rounding Errors in 2007

[Jacqueline]

I am running some simple percentage formulas, but the rounding errors are
driving me nuts..

If I add the numbers and use that to find my percentage it gets closer to
the 100% total each should have. However, if I use a formula such as:

=G16/(SUM(G16:I16))
The rounding can be off by a couple of numbers. Cells G16 and I16 are total
cells, for a column of numbers.
=SUM(G7:G15)
I have made sure the analysis tool pack is installed, and yet my numbers
continue to be off. I do not remember running into this problem in 2003. Is
there a fix, is anyone else having these problems?

Thanks,
Jacqueline

 

[BobT]

Check to see if your option for Calculation is set to "Precision as
displayed". In 2003 it's off by default.

 

[Jacqueline]

Sorry Bob, I muddied the water I guess, I am actually using 2007 and
lamenting that I did not have these problems in 2003...

Is this something I need to check for in 2007? If so, where do I find it; I
looked in Excel options but did not find anything?
Thanks
Jacqueline

 

[JoeU2004]

If I add the numbers and use that to find my percentage it gets closer to
the 100% total each should have. However, if I use a formula such as:
=G16/(SUM(G16:I16))
The rounding can be off by a couple of numbers.

Rest assured that it is almost certainly not an error in Excel 2007, unless
you can demonstrate that Excel 2003 has different results with exactly the
same workbook and calculation options.

Without seeing the numbers and at least the formulas, we can only offer
educated guesses. If you truly would like some help, you will need to
provide the obviously missing information.

My guess: you have "Precision as displayed" set, or when you add the
numbers, you incorporate some form of rounding, so that the sum of G16:I16
that you compute separately ("add the numbers") is not the same as
SUM(G16:I16).

However, I do not know what you mean by "off by a couple of numbers". What
is "a couple of numbers"? Why don't you say exactly how far off the
percentages or the sum of the percentages is?


----- original message -----

 

[JoeU2004]

Is this something I need to check for in 2007? If so, where do I find it;
I looked in Excel options but did not find anything?

I hope Bob or someone else can answer the Excel 2007 question. I cannot.

In Excel 2003, click on Tools > Options > Calculation. Determine whether or
not "Precision as displayed" is checkmarked.

Caveat: D O N O T experiment with the PAD option unless you first make
a back-up copy of the workbook. Setting PAD can have irreversible effects,
notably on a constant entered into a cell that has a format other than
General.


----- original message -----

 

[Jacqueline]

In one case the numbers were off by 4%, a differance between 6% and 10%. When
I hand calculated the total, and used that number to obtain my perctage the
correct number was displayed.

My fear, is that with the mucking around they did with the app, I have
something that is turned on or not turned on that is effecting the way my
numbers are calculating. I do not like the feeling that I can no longer trust
my spreadsheet, and don't have the time to run all the totals by hand.

I was wondering if because I was running the formula off another formula
that it was somehow effecting my results. I have formated all the numbers the
same.

I have used Excel for a long time, so rest asured I have checked everything
I know to check believing the problem is with me, and not the app. However,
with the changes they made in 2007, I am unsure of my self.

If you have a suggestion that can help I woul love to hear it.
J

 

[Meebers]

I think the only way we can help you is for you to give us some sample
numbers. The result you get and the result you expect. Most likely between
the real number and the number being displayed? Looking forward to your
sample.

 

[Meebers]

It is under Excel options/Advanced about 3/4 down the page "When

 

[JoeU2004]

In one case the numbers were off by 4%, a differance between 6% and 10%.

That is a significant difference. Without seeing the individual values, I
can only guess. But I do believe the difference can be explained by
rounding "errors". However, although I share you skepticism for anything
new from MS, my money is on a user error (yours!), not Excel 2007.

On the other hand, I do not fully understand. Your explanation here seems
inconsistent with your first explanation. In the beginning, I believe you
were concerned about percentages not summing to 100%. Now you are talking
about the difference between 6% and 10%.

Moreover, it just occurred to me that you are distributing the 100% over
only 3 cells. Kinda hard to get a difference of 4 pct points simply due to
rounding in that case. 3 pct points, yes; but 4, hmm...? I am beginning to
suspect that this has less to do with rounding issues, and more to do with
the formulas you are using.

Oh well, this is all wild speculation anyway, shooting in the dark (the
absence of concrete data). Don't take any of it seriously.

When I hand calculated the total, and used that number to obtain my
perctage
the correct number was displayed.

Is that what you meant when you wrote initially, "If I add the numbers and
use that to find my percentage it gets closer to the 100% total each should
have"? That is, are you comparing computations by hand with compuations by
formulas?

If that is the case -- and again, I can only guess without seeing numbers --
I would guess that it is less likely to be an issue with "Precision as
displayed", although that it is still a strong possibility.

The simpler explanation might be WYSI(not)WYG. That is, you are being
confused by displayed numbers that are different from their values due to
formatting.

You might be able to confirm this by formatting G16:I16 as Number with more
decimal places than you normally use. For example, if you normally use 2
decimal places, format with 4 decimal places. Also reformat your
percentages (G17:I17?) with more decimal places. Then redo your hand
calculations to see if you come closer to the Excel values.

If that does not pan out, for a quicker and more dispositive explanation,
you can send me your workbook in email. Send to joeu2004 "at" hotmail.com.
Be sure the subject line is similar to this thread's subject line. Also,
please include an explanation that points me to the questionable range, if
your workbook has more than one worksheet.


----- original message -----

 

[JoeU2004]

Errata....

Kinda hard to get a difference of 4 pct points simply due to rounding in
that case. 3 pct points, yes; but 4, hmm...?

Wrong! I was thinking of rounding the percentages. But rounding in the raw
data (G16:I16) can cause huge percentage differences, depending on the
magnitude of the numbers and the precision of the rounding.

It's a moot point anyway, without sufficient information.


----- original message -----

 

[Jacqueline]

Thanks for the tip on "set numbers as displayed" it was not turn on, and from
what everyone has said I think it

should remain off, correct? I am sending a portion of number I am working
with to give you an example as requested.

Spring 09 (A894)
FND 1 FND 4 FND 5
1,213.50 1.00
585.00 90.65
5.00
190.25
25.00
1,279.52 18.00 1.20
19.50 2.00
31.00
50.00 98.75

Total: 3,398.77 207.40 4.20 These numbers were obtained
example first column: SUM(I23:I32) etc.

Percent by quarter:

94% 6% 0%
Example of formula here: I33/(SUM(I33:K33)) I wanted to obtain the percent
of each fund from the total of all funds.

In the middle column is where I got the widest difference. Using the formula
my percentage kept coming up as 10%.

When I added the numbers manually and divided the fund total by all the
funds totals it worked fine.

Because I know that the total percentage for all the funds should equal 100%
it was easy to spot that for these

numbers I had 104%... that is why I started looking at the rounding issue.

All numbers were except the percentage were formatted using comma style, and
percentage was rounded to no zeros as

you can see.

My biggest worry is that something will go out that is not so easy to spot.
If I have a setting wrong, I sure would

love to find it for my piece of minds sake!

As always, everyone's help is greatly appreciated.
Jacqueline

 

[JoeU2004]

PS....

That is a significant difference. Without seeing the individual values, I
can only guess. But I do believe the difference can be explained by
rounding "errors".

Another wild-ass guess: perhaps one or more of the cells in G16:I16
contains text that looks like a number. That can even make the results of
G16+H16+I16 differ from SUM(G16:I16). Consider this example, a common
mistake:

A1: =if(B1="",3,"3")
A2: 2
A3: 1
A4: =SUM(A1:A2)
A5: =A1+A2+A3

With nothing in B1, A4 and A5 have the same result. Not so after you put
something into B1.

Seems unlikely since you said that each of G16:I16 is a formula of the form
=SUM(G7:G15). But we are not getting the whole picture.

Also, strange things happen (in Excel 2003) when cell formats are changed to
Text, which can happen inadvertently because Excel does this automatically
in some instances. And of course, such anomalous behavior might change in
Excel 2007, for better or worse. I do not think this would account for the
differences that you observe. But again, since we are not seeing the data,
we cannot know for sure what side-effects might and might not apply.

So, you might double-check the format of all cells involved (G7:I15).


----- original message -----

 

[Jacqueline]

I feel so stupid! I guess when you put in a ten hour day this is what I get.
I relooked at the formulas, and what I was doing was coping the formul from
the first cell to the other two, well the cell referance was changeing....
duh!

Such a rookie error... I feel bad that I wasted your an everyone elses
time...

Thanks everyone, I feel much better that I now know it was my error, and a
dumb one at that, and I can trust my spreadsheets...

Thanks again
Jacqueline

 

[Jacqueline]

I have to confess, I looked at the formula again today, I had not set the
referanced total cells to constants, so when I copied from the first fund to
the other two, the cell referance was changing.

I feel so dumb, such a beginner mistake, my only excuse is the ten hour day
I had put in... thanks for all the help, I'm sorry for taking up your time...
Jacqueline

 

[Meebers]

Not a problem....it happens to the best of us....I have been doing Excel for
20 yrs and learn something new just about every day.