Excel has gone crazy on me...

[albertc30]

Hello everyone.

I have just started to see excel behaving in a very weird way.

It says a simple formula like "=121.5+10+20" is incorrect and says there's an error on the cell that contains the formula, it says inconcistent formula.

Also, something like "=COUNTIF(D21:Q21,">0")" returns a 9 on the insert function but once you press enter on the formula on the cell, it returns zero.

What on earth is going on with excel?

Tried someting like Stellar Repair for Excel but still the issue persists.

Anyone had this issue? How to fix this? It's rather frustrating.

Many thanks everyone and be safe out there.

Thanks

 

[Logit]

I tried to replicate the issues here without success. My Excel 2007 / Win 10 performs as it should.

May I inject some levity into the discussion ? We all need a smile now and then.
Page 43 of the Excel User Manual states "After a reasonable time of usage, Excel will become
'familiar' with the user and exhibit unexpected outcomes. This is a means of demonstrating
to the use that Excel has become 'attached' and is expressing a fondness for the user.

My copy became REALLY attached ... to the point I needed to divorce it some months past. I
went with Excel 2019. Then returned to 2007 and it has been behaving ever since.

OK ... on a serious note ... I've seen Excel do some strange things here for no reason at all.
Invariably for me, deleting the troublesome workbook and rebuilding it new from scratch was
the solution.

I have also had luck copying the inner VBA code to Notepad. Deleting the code from the workbook.
Then pasting the Notepad code back into the troublesome workbook. Hope that made sense ? I only
used this method if the workbook had tons of code that I really did not want to re-type letter by letter.

Hope that helps ?

 

[albertc30]

I tried to replicate the issues here without success. My Excel 2007 / Win 10 performs as it should.

May I inject some levity into the discussion ? We all need a smile now and then.
Page 43 of the Excel User Manual states "After a reasonable time of usage, Excel will become
'familiar' with the user and exhibit unexpected outcomes. This is a means of demonstrating
to the use that Excel has become 'attached' and is expressing a fondness for the user.

My copy became REALLY attached ... to the point I needed to divorce it some months past. I
went with Excel 2019. Then returned to 2007 and it has been behaving ever since.

OK ... on a serious note ... I've seen Excel do some strange things here for no reason at all.
Invariably for me, deleting the troublesome workbook and rebuilding it new from scratch was
the solution.

I have also had luck copying the inner VBA code to Notepad. Deleting the code from the workbook.
Then pasting the Notepad code back into the troublesome workbook. Hope that made sense ? I only
used this method if the workbook had tons of code that I really did not want to re-type letter by letter.

Hope that helps ?

LOL...

Oh dear!!!

That put a smile on my smug face. It was much needed.

I had to rebuild it on a new file. How on earth can Microsoft justify that 1+1=0 rather than 2 is anyone's guess. The software is taking over as it already has a mind of it's own.

Thanks.

 

[Logit]

Well ... at least you found an answer. Not what someone would expect but ...

Best wishes.

 

[joeu2004]

It says a simple formula like "=121.5+10+20" is incorrect and says there's an error on the cell that contains the formula, it says inconcistent formula.
Also, something like "=COUNTIF(D21:Q21,">0")" returns a 9 on the insert function but once you press enter on the formula on the cell, it returns zero.

I had to rebuild it on a new file.

My guess is: "rebuilding" the file merely incidentally corrected the circumstances that were caused by self-induced mistakes.

We can duplicate the second symptoms that you describe as follows in a new workbook.

1. Enter =121.5+10+20 into A1, formatted as General (default in a new workbook).
2. Enter =COUNTIF(A1,">0") into B1. Note that B1 displays 1, as expected.
3. Change the format of A1 to Text. Note that B1 continues to display 1.
4. Select A1, press function key f2 to "edit" A1, and press Enter. Note that B1 now displays zero.

Explanation.... When we change the cell format from numeric to Text or vice versa, it does not immediately change the type of the data. In step 1, the data type is numeric. It remains numeric in step 3. Confirm that ISNUMBER(A1) returns TRUE. But in step 4, the data type is changed to text. Confirm that ISTEXT(A1) returns TRUE.

When you "rebuilt" the spreadsheet in a new file, you simply did not create those circumstances. Instead, you started with a "clean slate".

-----

As for the "inconsistent formula" "error", I presume that you are referring to a green triangle in the upper-left corner of the cell. For example:

These are warnings, not bona fide errors. They are caused by setting the "background error checking" option (a misnomer), to wit:

IMHO, this option should not be enabled. Usually it is misleading, distracting and easily misunderstood.

After disabling the option, be sure to click Reset Ignored Errors to clear any existing warnings -- ah, "errors".

The so-called "errors" (warnings) are merely drawing your attention to something that Excel considers questionable. But 99 times out of 100, they are simply purposeful on our part.

 

[MARK858]

there's an error on the cell that contains the formula, it says inconcistent formula.

Inconsistent formula just informs you that the formula doesn't follow the pattern of the formulas around the cell, it isn't really an error it is just saying "you might want to look at this".
You can turn it off in your options.

 

[joeu2004]

May I inject some levity into the discussion ? We all need a smile now and then. Page 43 of the Excel User Manual states "After a reasonable time of usage, Excel will become 'familiar' with the user and exhibit unexpected outcomes.

I hope the "levity" is because that's a load of crap, and you know it.

AFAIK, Excel is not adaptive; at least not your Excel 2007, nor my Excel 2010. I cannot speak for Office 365, which seems to "adapt" itself on a whim, if user complaints that I read are valid.

Curiosity: what "Excel User Manual"?!

AFAIK, there is no such MSFT document.

My google search turned up a document by that name from the Univ of Mary Washington (huh?!). But your quote does not appear on page 43. And of course, there are any number of books on the subject; but none has that exact title, AFAIK.

I wish there were such a MSFT document. But then again, it would probably be as inaccurate and sometimes blatantly incorrect as the help pages at support.microsoft.com. (sigh)

And those help pages are getting more unreliable over time. MSFT seems to keep rewriting them, often "santitizing" them of useful technical details. And some useful help pages seem to have disappeared. For example, I cannot find the one that describes Excel's sort order, which details like the relationship among logical values (TRUE, FALSE), text and numeric values.

 

[Logit]

joeu204 ... calm down brother. That was parody.​

From Wikipedia, the free encyclopedia"

A parody, also called a spoof, a send-up, a take-off, a lampoon, a play on (something), or a caricature, is a creative work designed to imitate, comment on, and/or make fun of its subject by means of satiric or ironic imitation.

 

[joeu2004]

(I meant to include the following comments. Apparently, we cannot Insert Quotes by editing.)

I've seen Excel do some strange things here for no reason at all. Invariably for me, deleting the troublesome workbook and rebuilding it new from scratch was the solution.

I usually find that it is sufficient to open a new workbook in the same Excel instance, and copy the worksheet(s) to the new workbook.

I have also had luck copying the inner VBA code to Notepad. Deleting the code from the workbook. Then pasting the Notepad code back into the troublesome workbook.

Often, I find that it is sufficient to complete exit and restart Excel.

 

[Logit]

Sometimes copying the code from the old to the new workbook has worked here as well.
Sometimes simply rebooting the computer is sufficient here or shutting down Excel and reopening it.

I agree.