So, I have a spreadsheet that calculates what cash you currently have (A) and compares it with the cash that you started with (B) minus the value of all transactions (C); if A-(B-C)=0, then the system is in balance. I'm trying to express this with a formula whereby if the number is plus or minus it's displayed in different colour text (using conditional formatting). That part is fine, but if the number is zero (i.e the system is in balance), instead of displaying the number zero it should display text "BALANCING".
For example, let's say I started with £516,260.72 cash and starting cash takeaway transactions also adds up to £516,260.72. So the system is balanced. The pertinent part of the spreadsheet is thus:
cell E16 is £516,260.72 (A)
cell G16 is £516,260.72 (B-C)
My formula for the final equation (the 'status' cell) is =IF(E16-G16=0,"BALANCING",E16-G16)
Now, to my mind, that looks correct. If current cash - (starting cash - transactions) is equal to zero, the status cell should display the text "BALANCING". If the logical test results in a number above or below zero, the cell simply returns the value of E16-G16. However, in practise, when the logical test does return zero it's just displaying the number zero with a minus in front of it, in the conditional formatting that I'm using for a negative number (red, in this case).
My thought was that there was a number somewhere with several digits after the decimal point that was creating a small variance that couldn't be seen because the formatting of the status cell was set to currency and displaying only two decimal places. So I tried increasing the displayed decimal places to twenty in all of the cells in the spreadsheet that would amount to that final status cell. All of the input numbers only have information in two digits after the decimal place (i.e. the remaining eighteen digits all say zero) but my final balancing cell has 0.00000000005820766091.
I found this very confusing! In case it was a problem with the formatting in my original spreadsheet, I opened up a brand new workbook and rebuilt the balancing spreadsheet from scratch without copy + pasting anything from the original. And I get the same result. All of the input values only have information two places after the decimal point, but the status cell still displays 0.00000000005820766091.
In fact, if I set the displayed decimal places to thirty in the status cell, it returns precisely
0.000000000058207660913467400000
So, it looks as though Excel can't do basic arithmetic. Seriously, what gives?
For example, let's say I started with £516,260.72 cash and starting cash takeaway transactions also adds up to £516,260.72. So the system is balanced. The pertinent part of the spreadsheet is thus:
cell E16 is £516,260.72 (A)
cell G16 is £516,260.72 (B-C)
My formula for the final equation (the 'status' cell) is =IF(E16-G16=0,"BALANCING",E16-G16)
Now, to my mind, that looks correct. If current cash - (starting cash - transactions) is equal to zero, the status cell should display the text "BALANCING". If the logical test results in a number above or below zero, the cell simply returns the value of E16-G16. However, in practise, when the logical test does return zero it's just displaying the number zero with a minus in front of it, in the conditional formatting that I'm using for a negative number (red, in this case).
My thought was that there was a number somewhere with several digits after the decimal point that was creating a small variance that couldn't be seen because the formatting of the status cell was set to currency and displaying only two decimal places. So I tried increasing the displayed decimal places to twenty in all of the cells in the spreadsheet that would amount to that final status cell. All of the input numbers only have information in two digits after the decimal place (i.e. the remaining eighteen digits all say zero) but my final balancing cell has 0.00000000005820766091.
I found this very confusing! In case it was a problem with the formatting in my original spreadsheet, I opened up a brand new workbook and rebuilt the balancing spreadsheet from scratch without copy + pasting anything from the original. And I get the same result. All of the input values only have information two places after the decimal point, but the status cell still displays 0.00000000005820766091.
In fact, if I set the displayed decimal places to thirty in the status cell, it returns precisely
0.000000000058207660913467400000
So, it looks as though Excel can't do basic arithmetic. Seriously, what gives?
Last edited:
