Why Won't My Cells Calculate?

This post requires no response. I've had a problem for a while that I've finally solved and I thought I'd post about it to save others wasted time.

The problem was that sometimes I'd try to enter a formula into a cell that had already been used as a date field with the intent to calculate something entirely different for that cell. But the formula wouldn't calculate, despite being displayed properly in the formula bar and as if it were text in the cell itself.

The only way I could ge this to work was to go to a fresh column, enter the formula and then cut/insert the column next to the column I'd tried to get to work and then delete the column I couldn't get to work.

Well, it turned out to be a formatting problem.

Solution, use the Edit, Clear, All command in the cell before trying to enter another formula. That clears pre-existing formulae and you can proceed.

LongFisher

The cells were formatted as text. The Edit > Clear All reset the format to whatever format was set for style Normal, which would generally be General. Setting the cell style to Normal would have achieved the same result.

I wish this would turn off the html! Anyway, I just wanted to thank you so much for this information because I was litteraly screaming at the computer very childishly. I just could not get it to work but because of you it did. I hope you get this. Thank you truly from my heart.
Serena

Originally Posted by longfisher

This post requires no response. I've had a problem for a while that I've finally solved and I thought I'd post about it to save others wasted time.

The problem was that sometimes I'd try to enter a formula into a cell that had already been used as a date field with the intent to calculate something entirely different for that cell. But the formula wouldn't calculate, despite being displayed properly in the formula bar and as if it were text in the cell itself.

The only way I could ge this to work was to go to a fresh column, enter the formula and then cut/insert the column next to the column I'd tried to get to work and then delete the column I couldn't get to work.

Well, it turned out to be a formatting problem.

Solution, use the Edit, Clear, All command in the cell before trying to enter another formula. That clears pre-existing formulae and you can proceed.

LongFisher

Just like it was already said above, the problem is due to formatting. And it happens a lot of time when you open a text file on excel:
When the cell is formatted as text, it considers every content as text (string). Even if you change the formatting to "general" the formula still doesn't calculate because changing the cell formatting doesn't change the format attribute of the content (Soto speak). In case you cannot insert columns just like it was said in this forum, you can follow the below steps to solve the problem:
1) select all cells with a formula that are affected, go to cell formatting (Ctrl+1), in number take "general".
2) do a find and replace (Ctrl+h), and replace the equal sign (=) by a string that's not present in any of your formulas.
3) Next, replace that string back with the equal sign
That should do.
Example:
=sum(A1:B6) - replace the equal sign by a string that's not present in the formula like "Patching". then it becomes
Patchingsum(A1:B6) - now we replace that string back with the equal sign. then it becomes
=sum(A1:B6) - and this time it will calculate

I had encountered the problem twice before. In both instances, it was due to the cells being formatted as Text. In the worksheet I'd generated, it was easy to fix. The solution wasn't as simple in the worksheet I'd imported, and I can't recall what I did in that case.
However, I ran into the problem again yesterday, and this time I found that it was because I had a circular reference which I hadn't taken care of when warned. It wasn't until I had closed and reopened the worksheet and saw the warning again that I realized something needed fixing. And once I did, the cell(s) functioned properly.
Just wanted to pass the info along....