Strange Calculations in Excel 2007

Hello, I have been puzzling over this problem for some time. I use Excel 2007 and have several workbooks linked together. (I keep track of apartment renters and how much they pay/owe). I have an if formula set up so that if the amount they paid equals the amount they owe then it will show up as blank

=IF(SUM(D35:G35)-SUM(H35:Q35)<>0,SUM(D35:G35)-SUM(H35:Q35)," ")

Otherwise it will show me what they owe/overpaid. In this formula is a cell that pulls an amount from a previous spread sheet. It is the amount owing for the last month and show up on the current months workbook as previous month past due.

The problem is that sometimes the formula will work correctly and will show a blank, other times it will show 0.00 which should be the same as a blank. As I evaluate the specific formula I notice that it gets caught up with the cell whose amount is dictated by the previous workbook. For example, it will come up to the specific formula and shows this:

=if(1199.07-1199.07<>0,...)
=if(-2.27373675443232E-13<>0,...)

If this was happening just once I wouldn't think too much of it. But this is happening much more often, with many different workbooks where a seemingly simply equation turns into a scientific number. I have tried re-typing the formulas and also cut and pasting into different cells with no luck.

Thank you in advance

 

I tried playing with the formating, they are all set to accounting, 2 decimal places

I have to admit though that I didn't thoroughly check that. As I was changing them, it didn't seem to change anything so I didn't check all of my workbooks.

 

It looks to me like you need another IF nesting to cover the possibility that the cells being compared via the <> operators also considers the = comparison.

Try this:

Code:

=IF(SUM(D35:G35)-SUM(H35:Q35)<>0,SUM(D35:G35)-SUM(H35:Q35),IF(SUM(D35:G35)-SUM(H35:Q35)=0," "))

I put that in a code field so it would be continuous but here's how it looks with line wrapping applied by the BB here:

=IF(SUM(D35:G35)-SUM(H35:Q35)<>0,SUM(D35:G35)-SUM(H35:Q35),IF(SUM(D35:G35)-SUM(H35:Q35)=0," "))

That may be easier to copy/paste. Either way will work.

 

The possibility still exists that you have a calculation somewhere in a linked workbook that doesn't come out even and you may need to use the a ROUND formula to reconcile it. Formatting the display to show only 2 decimal places won't solve that issue. The fact that you can't see the digits beyond 2 places doesn't render it out of any calculations.

You should be able to test that by formatting the formula cell so as to display all decimal places, without limiting it. If it shows any decimal under the 0.00 conditions you mentioned, it will confirm the diagnosis.

 

Let's cut it down and simplify it:

=ROUND(SUM(D35:G35),2)-ROUND(SUM(H35:Q35),2)

Put that in the offending answer cell and see what you get.

 

Interesting

Thank you for your input. I tried the first formula and got:

-2.27374E-13

I put in the second formula and got:

0.00000000

No matter how many decimal places I still get 0's. Now I did try reformating it and it didn't help. I find this to be a problem in multiple instances because I use these formulas very often in tracking payables and receivables and water billing. I will map out my links here for you to see:

December 2009

A35 =IF(SUM(D35:G35)-SUM(H35:Q35)<>0,SUM(D35:G35)-SUM(H35:Q35)," ")

D35 955.00
E35 27.84
F35 36.4
G35 91.03
I35 955

A35 therefore equals 155.27

January 2010

A35=IF(SUM(D35:G35)-SUM(H3535)<>0,SUM(D35:G35)-SUM(H3535)," ")

D35 955.00
E35 30.68
F35 ='[December 2009.xlsm]Receivables 7208'!$A$35
I35 955
J35 30.68

A35 therefore equals 155.27

February 2010

A35=IF(SUM(D35:G35)-SUM(H35:Q35)<>0,SUM(D35:G35)-SUM(H35:Q35)," ")

D35 955.00
E35 38.8
F35 ='[January 2010.xlsm]Receivables 7208'!$A$35
G35 50
J35 1005
P35 194.07

A35 therefore equals (0.00)

The problem is I don't see anything wrong with this formula, I have entered exact numbers as shown in the cell, not in the sheet. Everything is formated to accounting 2 decimals.

 

I can't duplicate your problem since it works fine for me. I input your cell values and get $- in the A35 cell when it zeroes out. That's with the cell formatted for accounting. If I format it for currency, 2 places, I get $0.00 displayed.

I've tried every conceivable entry that might create your type of problem but can't dupe it. It's probably something simple but that can be elusive on spreadsheets. I have been known to go through and delete each cell and reenter the value when troubleshooting. You cell number inputs shouldn't be causing any deeply embedded decimal digit so one of them isn't a whole number. Another weird possibility could be that a cell has gotten formatted using the date format instead of the accounting or currency. That can render some strange numbers under certain conditions. Check for that.

The ROUND formula I offered may be what you have to go with until times get better. This is obviously some obscure corruption that may take longer to find than it's worth.

 

Thanks again for looking at it. Ultimately it is just an annoyance. This particular instance I simply want it to show nothing instead of showing a 0.00. That is interesting that you have it showing a 0.00 though, per the formula, it should be showing nothing. I have this for 40 residents, some show a 0.00 and some show nothing so I know it works, but it hasn't affected my bottom line yet.

I will try the reentering data, I thought I had already done that but maybe I hadn't. Also, I will look at the cell formatting again.

 

Let's combine both formulas and go for another test:

=IF(ROUND(SUM(D35:G35),2)-ROUND(SUM(H35:Q35),2)=0," ",SUM(D35:G35)-SUM(H35:Q35))