If function not working correctly

[Relle]

Hi I'm using Excel 2003
Formula
=IF(F2=SUM(G2:U2),"Balanced","Not Balanced")
F2 and G2:U2 are all sum functions.

The formula seems to work fine then all of a sudden I get a "Not Balanced"
when the values are correct. I'm using 2 decimal places and have ensured the
whole page is set as this.

I use a separate spreadsheet each month and the problem normally comes up
towards the bottom of the page, (normally about 300 row entries), but this
month it's cropped up after 6 rows. Previously I've been able to delete a few
rows, type in a few other entries, then re-type the amounts that were causing
the problem again and it seems to fix it.
Not today though.
Any suggestions.
Thanks

 

[Mike H]

Hi,

Because you have your cells set to 2 decimal places the sums may 'appear'
balanced but may not be. For example
1.0234
1.0235
set to 2 decimal places bith display in the cell as 1.02 but they arent the
same. So it depends on what level of equality you want. If 2 decimal places
is enough try

=IF(ROUND(F2,2)=ROUND(SUM(G2:U2),2),"Balanced","Not Balanced")

You can increase the rounding to 3 if you want greater accuracy.

Mike

 

[JoeU2004]

=IF(ROUND(F2,2)=ROUND(SUM(G2:U2),2),"Balanced","Not Balanced")

Just to expand on Mike's comments a bit....

The above solution will probably work.

But it might be good practice to use ROUND in formulas prolifically, albeit
prudently, throughout the worksheet; for example, in F2 and G2:U2, which
Relle says are "all sum functions". Then the formula here can be simplified
somewhat:

=IF(F2=ROUND(SUM(G2:U2),2),"Balanced","Not Balanced")

I use a separate spreadsheet each month and the problem normally
comes up towards the bottom of the page, (normally about 300 row
entries), but this month it's cropped up after 6 rows.

To appreciate the issue, try this as an experiment: enter =IF(10.1 - 10 =
0.1, TRUE). The result is FALSE (!).

As you see, the problem can arise with as few as 2 values being added or
subtracted.

The reason is: most numbers with decimal fractions cannot be represented
exactly. The approximated internal representations cause very subtle
differences when combining constants in arithmetic expressions.

Sometimes you can see these differences by formatting the cells so that you
can see 15 significant digits (i.e. ignoring leading zeros). But sometimes
even that does not reveal the differences.

Sometimes the differences correct themselves when performing the arithmetic.
Sometimes Excel adjusts the arithmetic results in a half-baked attempt to
correct some subtle differences.

But often, the differences are simply carried along and magnified from
computation to computation until they make a significant difference.

I'm using 2 decimal places and have ensured the
whole page is set as this.

Formatting only affects the appearance of values in cells, not their actual
values.

Previously I've been able to delete a few rows, type in a few other
entries,
then re-type the amounts that were causing the problem again and it seems
to fix it.

Probably just by coincidence, depending on which values you entered
manually.

What the ROUND function does is: it ensures that the result matches the
internal representation of the number as if you had entered it manually.


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

 

[Relle]

The decimals is not the problem, the problem even seems to be occuring when
entering whole numbers without decimals - all data entered onto the
spreadsheet are only two decimals as its actual money.
Any other suggestions?

 

[David Biddulph]

If it's not the decimals, I could only suggest that you've made a mistake in
your data (or that you don't have calculation mode set to Auto).

What values do you have for the following formulae:
=F2
=ROUND(F2,2)
=SUM(G2:U2)
=ROUND(SUM(G2:U2),2)
=F2-ROUND(SUM(G2:U2),2)
=ROUND(F2,2)-ROUND(SUM(G2:U2),2) ?

 

[Relle]

Hi David

I've tried all them formulas - the first four all give me the same answer
2348.84, the last two give me the result 0.
Double checked and the calculation mode is set to automatic.

Any other suggestions - I feel I'm ready to pull my hair out.

 

[David Biddulph]

I thought that you were saying that there were problems with whole numbers?
2348.84 doesn't sound like whole numbers (and, of course, 2348.84 cannot be
expressed exactly in fixed point binary, just as 1/3 cannot be expressed
exactly in fixed point decimal). You will therefore need to look at more
decimal places on those numbers. Try formatting to show 15 decimal places.

While you are there, what do the following show:
=F2=ROUND(SUM(G2:U2),2)
=ROUND(F2,2)=ROUND(SUM(G2:U2),2) ?
--
David Biddulph

Hi David

I've tried all them formulas - the first four all give me the same answer
2348.84, the last two give me the result 0.
Double checked and the calculation mode is set to automatic.

Any other suggestions - I feel I'm ready to pull my hair out.

 

[Relle]

Sorry for the confusion David. I'm working in 2 decimal places (it's actual
money the figures that I'm using so there's no calculations to obtain the 2
decimals except addition of funds). I tried an example of whole numbers just
to ensure it was the formula and not some error I had made somewhere that I
couldn't find.

I've tried both of those formulas and they respond true even though I'm
still getting "Not-balanced"

 

[JoeU2004]

Any other suggestions - I feel I'm ready to pull my hair out.

If you cannot resolve the problem yourself, I suggest that you send me the
Excel file.

In the email message, let me know where to look in the Excel file.

Send email to joeu2004 "at" hotmail.com.

the first four all give me the same answer 2348.84

How can you get 84 cents from arithmetic involving only "whole numbers"?
(2348 is a "whole number". 2348.84 is not.)

Are you no longer talking about "whole numbers"?

Does your arithmetic include division? Multiplication with fractional
numbers (like interest)?

In any case, the real question is: did you try Mike's solution using ROUND?
If so, with what result (works or not)?

the last two give me the result 0

Perhaps. But be careful. What I think you really mean is: the last two
__appear__ to be zero.

Remember: what you see displayed is usually __not__ what the actual value
is, except for constants that you enter manually.

If you enter 0.10, it __is__ 0.10 (within 15 significant digits). But if
you subtract 10 from 10.10, it may or may not be exactly the same as the
constant 0.10, even though it __appears__ to be, especially when formatted
to only 2 decimal places.


As I demonstrated, even simply arithmetic combinations of "actual money"
with only two decimal places can have unexpected results.

Did you try my example (rewritten to look like "actual money"): =IF(10.10 -
10.00 = 0.01, TRUE)

That returns FALSE (!). The following corrects the problem:
=IF(ROUND(10.10 - 10.00, 2) = 0.01, TRUE)


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

Hi David

I've tried all them formulas - the first four all give me the same answer
2348.84, the last two give me the result 0.
Double checked and the calculation mode is set to automatic.

Any other suggestions - I feel I'm ready to pull my hair out.

 

[Joe User]

FYI, after looking at the Excel file, I can see that the problem was exactly
what Mike, David and I had described. Mike's suggestion was sufficient for
the short-term. My suggestion also works as a long-term approach.

(Note: Relle sent me the worksheet with the original problem. I have not
yet seen a worksheet with the problem she had with "whole numbers".)


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

 

[Relle]

Thank you all for your help - sorry it took me so long to get the
understanding of what you were suggesting.....
I'm having one of those weeks........