Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
My IF function is saying something is false when it is true...
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
My IF function is saying something is false when it is true...
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
My IF function is saying something is false when it is true...
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. -- Hope this helps Martin Fishlock, www.nyfconsultants.com, Bangkok, Thailand Please do not forget to rate this reply. "dilbert16588" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup function problems with True/False | Excel Discussion (Misc queries) | |||
function help-keeping data from true when false | Excel Worksheet Functions | |||
I need =SPELL()True/False function in Excel | Excel Worksheet Functions | |||
Function to return True/False if all are validated as True by ISNU | Excel Worksheet Functions | |||
First part of "IF" function returns false even if true. ?? | Excel Worksheet Functions |