Formula =if(sumif,... Not calculating properly

[snwbrder01]

Hi everyone,

here is my formula,

=IF(SUMIF(Table1[State], "TEXAS",Table1[Tax Amount])-SUM(BH4820:BH4900)=0, SUMIF(Table1[State], "TEXAS",Table1[Tax Amount]), "INCORRECT " & SUMIF(Table1[State], "TEXAS",Table1[Tax Amount])-SUM(BH4820:BH4900))

In a nutshell, I have a table, Table1, that lists every state with subjurisdictions and tax amounts in each location. As a check figure, to make sure no new locations were added, I am taking the sum of the tax amounts on Table1 and subtracting the sum of tax amounts on my current worksheet (within the same workbook, BH4820:BH4900 in the formula). IF the equation equals 0, then put the sum range of the state's tax amount (TEXAS in the example). IF the equation does not equal 0 then put the difference. I am getting a difference, even though when I highlight the cells on Table1 and BH4820:BH4900 the amounts equal. For example, they both sum to 492.76 but I get a difference of -5.767683416088. Any ideas of why I am getting a difference when the amounts equal?


Thanks much

 

[ypma]

Not too knowledgable about excel formula so will not comment , but have you considered that there might be some rounding up or rounding down to produce the -5 difference you are getting. As a test ,If you were to format all cells involved in the calulation and increase the decimal places and see if that spreads some light .

If you get no joy from this forum have you tried the excel forum ?

ww.excelforum.com/search.php?searchid=72599

Regards Ypma