Simple Excel Formulas Not Showing Correct Answer (shows Dash or 0 depending on formatting) NOT a "Numbers Stored As Text" issue!

[jaham00]

I have a very large excel workbook that runs the financials for a marina business. If you input your budget projections it will forecast cash flows, by month, for the year ahead. I am fairly savvy with excel basics, but I feel like I am encountering some weird formatting issues where formulas will not calculate correctly. For example - I will try to use sum formula on some rows that have various values and do not add to zero, yet excel is showing a "-" or a "0" no matter how i adjust the formatting.

Again, I recognize that 99% of the time when people post about 0 or - cells it is due to a "numbers stored as text" issue but that is not the case here.

I have uploaded the file for reference. Specifically, right now I am work on the sheet named "cf" which is for cash flow. I am trying to get LE28 to perform a sum calculation of (LE11 thru LE27) but it shows a dash or zero even though mathematically that is not the case.

To take this example further please note that LE25 is incorrect in showing (48,284). This formula (which is =bs!LE49-bs!LD49) draws from two cells on the balance sheet; if you look on the "bs" sheet tab at LE49 and LD49 they are 130,474 and 164,324, respectively. A simple subtraction calculation shows that the answer should be -33,850 and not the -48,284 that the cf tab shows. What's worse is when I goto the "bs" tab and try to run a test calculation using a simple subtraction formula of =LE49-LD49 it again gives me the 0 or - in the cell which is obviously incorrect. You can see this test calculation for this on the "bs" sheet in cell LF2.

I even tried to test the answer with the =ISNUMBER function and I can't even get it to read TRUE OR FALSE....even that cell just gives me the dash or zero. Setting formatting to general gives me a 0 and setting it to accounting gives me the dash.

Please help! Do I have a corrupted file or something? See attached screenshots; I wish io could upload the whole workbook but it won't allow and XL2BB failed when I tried to download and open it (it says this file type not supported in protected view but won't let me open the file to remove protections).

 

[felixstraube]

The only thing I can think of is that you have calculations turned to Manual.
Formulas/Calculation/Calculation Options

Could you upload you Workbook (or a sample of it) to a cloud storage like google drive an post the link? Then it would be easier to determine what the problem is.

 

[Alex Blakenburg]

Your screenshot is showing that you have circular references. They can cause this sort of behaviour. See if you can fix the circular references.

 

[jaham00]

Felix - I should have mentioned this in my original post but this is something I did already check and it was already set to “automatic” calculations. Thank you for pointing that out though.

Alex - I noticed the circular references error message but had not focused on rectifying it because there are no circular references errors in the particular cells or sheets I’m trying to work on at the moment. For context, this excel file was originally built by some consultant like 20 years ago and many accountants here at the marina have mangled it further since then. This is my first foray into trying to fix this workbook and there are so many sheets I was trying to avoid digging into fixing them all. But if circular references in other parts of the workbook can somehow affect the portion I’m focused on (even though there are no such circular reasoning issues in the area I’m working on) then I may have to just bite the bullet and go one by one and fix them all.


Could it be that this excel file was written so long ago that the 2016 excel upgrade (or other upgrades) may have corrupted it or broken some formulas?


When I first worked on this file it was an .xlsm file but I have subsequently saved it as .xlsx which stopped the macros from functioning - but I can’t see how macros would cause this issue? And the issue existed and was the same in .xlsm format. Being as it was made so long ago it might have originally just been an .xls….don’t know if that matters at all

And YES - I will drop this full file into a Google drive and provide the link tomorrow when I get back to the office.

Thanks!

 

[felixstraube]

Now that it's clear that you have circular references it definitely is the cause of your problem. I had this in the past, and you can even have this behavior when having more than one book open and the circular reference is in a book you are not currently working on.
In case you didn't know already about it you can easily find the circular references by going to the formulas tab / error checking / circular references.

 

[jaham00]

I've uploaded the file to google drive. See the following link:

https://docs.google.com/spreadsheets/d/1TKRRiCeavhI3ewaLUjziwFBaI-Pc6qv-V_g4NZXAb5I/edit?usp=drive_link

And BTW I do plan on fixing the circular references and then reporting back here if that fixed the overall problem. It's just weird to me that something as simple as adding two cells together cannot be successfully calculated simply because there's a circular reference elsewhere on another sheet. If the formula or cells I was using were actually linked to the circular reference errors that would make perfect sense. I've had many excel files with circular references that did not impact the ability of other cells or formulas to calculate correctly; that's why I started thinking maybe the file was corrupt.

Anyways, thanks again for all the input!

 

[Alex Blakenburg]

We don't have access to that file. Did you allow access to anyone with the link ?