If function not working correctly

  • Thread starter Thread starter Relle
  • Start date Start date
R

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
 
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
 
Mike H said:
=IF(ROUND(F2,2)=ROUND(SUM(G2:U2),2),"Balanced","Not Balanced")
Click to expand...

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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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 -----
 
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?
 
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) ?
 
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.
 
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

Relle said:
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.
Click to expand...
 
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"
 
Relle said:
Any other suggestions - I feel I'm ready to pull my hair out.
Click to expand...

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
Click to expand...

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
Click to expand...

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 -----

Relle said:
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.
Click to expand...
 
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 -----
 
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........
 
I am working on a spreadsheet to calculate the formulas for service hours completed by people we need to track.

you will get 5 cells that have a .5 in the numbers and it comes up with a whole number just dropping the 5th .5. Here is a sample of my issue.

I am doing =SUM(B3:S3)which actually equals 37 and it gives an answer of 36.5 here are the numbers:

2,2,3,2,2,6,3.5,2,2,1.5,2,9,0,0,0,0,0,0

Can anyone please help me.

It is set to general for the format not with any set number of decimals.

Help it is very important and needs to get done by Monday
 
Help it is very important and needs to get done by Monday
Click to expand...

Appending your question to a year-old thread is the best way to get
immediate attention. Also, see the important PS below.

I am doing =SUM(B3:S3)which actually equals 37 and it gives
an answer of 36.5 here are the numbers:
2,2,3,2,2,6,3.5,2,2,1.5,2,9,0,0,0,0,0,0 [....]
It is set to general for the format not with any set number of decimals.
Click to expand...

I suggest that you temporarily format all of B3:S3 to Number with 15
decimal places, and you might see the problem.

I presume that the value of one or more cells is not exactly how they
display.

The following might remedy your immediate problem:

=SUMPRODUCT(ROUND(B3:S3,1))

But in the long-run, if B3:S3 actually contains formulas, it would
behoove you to round each formula to the required precision. For
example, change =formula to =ROUND(formula,1); and change
=IF(condition,formula,"") to =IF(condition,ROUND(formula,1),"").

For more specific guidance, if needed, it would be helpful if you
posted the exact contents of all or some of B3:S3. Copy and paste
from the Formula Bar into a follow-up posting. If any of B3:S3
contains formulas, also copy and paste the displayed values, formatted
temporarily to 15 decimal places.

PS: For broader participation, you might want to post future
inquiries using the MS Answers Forums at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.
 
I am doing =SUM(B3:S3)which actually equals 37 and it gives
an answer of 36.5 here are the numbers:
2,2,3,2,2,6,3.5,2,2,1.5,2,9,0,0,0,0,0,0
Click to expand...
[....]
I suggest that you temporarily format all of B3:S3 to Number with 15
decimal places, and you might see the problem.

I presume that the value of one or more cells is not exactly how they
display.
Click to expand...

FYI, =SUM(2,2,3,2,2,6,3.5,2,2,1.5,2,9,0,0,0,0,0,0) results in 37,
copying the text from your posting. Also, =SUM(B3:S3) results in 37
when I copy the text from your posting into B3 and use Text To Columns
to split it across B3:S3.

I think that "proves" that your problem is WYSInotWYG due to some
formatting issue -- or you have a typo in your posting that you
repeated when you checked the total yourself.

If you continue to fail to find the problem, I suggest that you post
an Excel file that demonstrates the problem to a file-sharing web site
and post its URL (http://...) in a follow-up response here. Some
people have suggested the following free file-sharing web sites. I
have no experience with any of them.

MediaFire: http://www.mediafire.com/
FileFactory: http://www.filefactory.com/
FileSavr: http://www.filesavr.com/
FileDropper: http://www.filedropper.com/
RapidShare: http://www.rapidshare.com/
 
I am working on a spreadsheet to calculate the formulas for service hours completed by people we need to track.

you will get 5 cells that have a .5 in the numbers and it comes up with a whole number just dropping the 5th .5. Here is a sample of my issue.

I am doing =SUM(B3:S3)which actually equals 37 and it gives an answer of 36.5 here are the numbers:

2,2,3,2,2,6,3.5,2,2,1.5,2,9,0,0,0,0,0,0

Can anyone please help me.

It is set to general for the format not with any set number of decimals.

Help it is very important and needs to get done by Monday
Click to expand...

I just opened Excel 2010 and entered those numbers from B1 to B18, and they
summed to 37 in B19, no special formatting needed.
 
Back
Top