My IF function is saying something is false when it is true...

[dilbert16588]

I have a basic budget spreadsheet set up. The IF formula I have set up (which
works accurately for other parts of the spreadsheet) involves three columns:
Cost Budgeted, Actual Cost and Difference. The Difference column is like it
says, the difference between the Cost Budgeted and the Actual Cost. I also
have to simple SUM functions at the base of the Cost Budgeted and Actual Cost
columns.

The IF function I have set up is as follows:

=IF((C10-F10)=SUM(H3:H8),SUM(C10-F10),"Error")

C10 is the Cost Budgeted total, F10 is the Actual Cost total, and the
SUM(H3:H8) is the sum of all the differences (each of those are simple
formulas. I'm basically verifying that the numbers match up like they should.
However in this instance they are not - thus giving me the "Error" as the
False.

When calculated separately, C10-F10= -5.58 and SUM(H3:H8)= -5.58 as well yet
the function keeps telling me it's false. Is this because there are negative
numbers? Or is there something wrong? As I stated before, I use this same
exact funtion in other parts of the spreadsheet and they all work accurately
except in this case. Thanks and I can provide a copy of the spreadsheet if
needed.

 

[Jim Cone]

There probably is a difference out in the 3rd to 15th decimal place.
You can use the Round function to ignore negligible differences when
comparing totals.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"dilbert16588"
wrote in message
I have a basic budget spreadsheet set up. The IF formula I have set up (which
works accurately for other parts of the spreadsheet) involves three columns:
Cost Budgeted, Actual Cost and Difference. The Difference column is like it
says, the difference between the Cost Budgeted and the Actual Cost. I also
have to simple SUM functions at the base of the Cost Budgeted and Actual Cost
columns.

The IF function I have set up is as follows:

=IF((C10-F10)=SUM(H3:H8),SUM(C10-F10),"Error")

C10 is the Cost Budgeted total, F10 is the Actual Cost total, and the
SUM(H3:H8) is the sum of all the differences (each of those are simple
formulas. I'm basically verifying that the numbers match up like they should.
However in this instance they are not - thus giving me the "Error" as the
False.

When calculated separately, C10-F10= -5.58 and SUM(H3:H8)= -5.58 as well yet
the function keeps telling me it's false. Is this because there are negative
numbers? Or is there something wrong? As I stated before, I use this same
exact funtion in other parts of the spreadsheet and they all work accurately
except in this case. Thanks and I can provide a copy of the spreadsheet if
needed.

 

[Martin Fishlock]

Hi Dilbert:

You may be getting rounding errors on the decimal places. it shows up on the
screen ok but one of the actual numbers may be 5.5800001 or so.

A couple of methods to solve this is to round off using the round function
or check that the absolute difference is less that 0.05:

IF(round(C10-F10,2)=round(SUM(H3:H8),2),round(C10-F10,2),"Error")

or

IF(abs((C10-F10)-SUM(H3:H8))<0.005,(C10-F10),"Error")

If you have a lot of these rows in your speadsheet you may prefer to add an
additional column for the error check and this saves calculating.

the sum goes in as normal =round(c10-f10,2)
then in the error column put =if abs(g10-SUM(H3:H8))<0.005,"","Error") and
this will highlight the errors clearly.