losiing pennies

[Don]

I have a spreadsheet that calculates a percentage of a
fiqure and places the result in another column. when I
use the =SUMIF to add all amounts in this column that
relate to a certain set of initials this answer is always
pennies out?????

Any help will be appreciated.

Don

 

[Harald Staff]

Hi Don

Excel is not WYSIWYG. A cell can be formatted to display something different from its real
content.

Say a formula returns 1/3 , 0.33333333333... , you can format it to display 0.33 but real
content is still 1/3. Add three of these cells and you will see 1.00 instead of the
expected 0.99 . This is desired behavior in most cases;
1/3*3 = 1

You must either ROUND each calculation result to two decimals,
=ROUND(A1*B1,2)
or use "precision as displayed" in the Tools > Options menu.

 

[Norman Harker]

Hi Don!

As an alternative to rounding your individual figures or the rather
drastic "Precision as displayed" route you can use an array formula to
calculate the sum based upon the current formatting:

Assume data in A1:A10 and assume that all are currently formatted to
two decimal places:

=SUM(ROUND(A1:A10,2))

Entered as an array formula by pressing and holding down Ctrl + Shift
and then pressing Enter.



When correctly entered appears as:



{=SUM(ROUND(A1:A10,2))}



This gives the best of both worlds. You can have a sum that "looks
correct" using the array and retain the individually correct
calculations of your data. If necessary, a simple sum formula will
give you a sum that "is correct".


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Sunday: Myanmar (Full Moon of Waso); Sri
Lanka (Poson Full Moon Poya Day); Thailand (Asalha Puja); Yugoslavia
(Freedom Rising Day Montenegro). Ashala Puja (Buddhism); O-Bon /
Festival of Souls (Shinto)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Harlan Grove]

As an alternative to rounding your individual figures or the rather
drastic "Precision as displayed" route you can use an array formula to
calculate the sum based upon the current formatting:

Assume data in A1:A10 and assume that all are currently formatted to
two decimal places:

=SUM(ROUND(A1:A10,2))

Entered as an array formula by pressing and holding down Ctrl + Shift
and then pressing Enter.

....

This is a classic error. Almost always the sum of unrounded but formatted
numbers is correct, and it's the unrounded but formatted numbers themselves
that contain the errors. Example: consider the sum of

10.666... formatted as 10.67
10.666... formatted as 10.67
10.666... formatted as 10.67
--------- -----
32.000... =SUM(A1:A3) 32.01 =SUM(ROUND(A1:A3,2))

So if the OP cares about these pennies, then the sum of the unrounded values
is the CORRECT sum. The sum of rounded values passes the rounding error from
the individual values to the sum.

If you're dealing with entered numeric constants, then a macro may be needed
to cleanse the data. Otherwise, it's easy enough to add a correction term to
one of the individual values.

 

[Harald Staff]

=SUM(ROUND(A1:A10,2))

This is a classic error. Almost always the sum of unrounded but formatted numbers is

correct,

My thought exactly. But as I read Don's posting he either isn't aware of the phenomenon
and expects 32.01 to be correct, or he deliberately wants to perform the error for some
reason.

Since he mentions percentage and money, I guess that 10.67 would be the "real" amount to
be paid or earned in this model, and therefore each percentage calculation should round
itself, not only by formatting the cell.

Best wishes Harald
Excel MVP

Followup to newsgroup only please.

 

[Harlan Grove]

My thought exactly. But as I read Don's posting he either isn't aware of the
phenomenon and expects 32.01 to be correct, or he deliberately wants to
perform the error for some reason.

Since he mentions percentage and money, I guess that 10.67 would be the "real"
amount to be paid or earned in this model, and therefore each percentage
calculation should round itself, not only by formatting the cell.

Maybe the OP does want rounded values produced by percentages, but it's often
impossible to arrange for both values and corresponding percentages to
correspond AND total exactly.

Using my previous example of 10.666... as 1/3 of 32,

Unrounded:
Percentage Amount
33.33... 10.66...
33.33... 10.66...
33.33... 10.66...
-------- ---------
100.00 32.00

Rounded without adjustment:
Percentage Amount
33.33 10.67
33.33 10.67
33.33 10.67
-------- ---------
99.99 32.01

Rounded with one sort of adjustment:
Percentage Amount
33.34 10.67
33.33 10.67
33.33 10.66
-------- ---------
100.00 32.00


This is a classic case: rounded percentages slightly understate the total
percentage while rounded amounts slightly overstate the total amount. Perhaps
the best way to handle this would be

Rounded with another sort of adjustment:
Percentage Amount
33.34 10.68
33.33 10.66
33.33 10.66
-------- ---------
100.00 32.00

which forces the percentage and amount adjustments into the same row, but
misstates the individual amounts by more than rounding error. There is no ideal
way of dealing with this sort of thing.