Thread: formula does not calculate

Help!

Something is weird with my Excel sheet. When I enter a formula, for example
=AB2+AB3
Excel does not calculate the result. Instead the formula just stays as it
is. I am sure there is some strange option that has been chosen by accident,
but I can't come up with where.

Any help out there!

Check cell format! It is probably set to Text. Set it to General!
Regards,
Stefi


„TaiwanSwedeâ€� ezt Ã*rta:

> Help!
>
> Something is weird with my Excel sheet. When I enter a formula, for example
> =AB2+AB3
> Excel does not calculate the result. Instead the formula just stays as it
> is. I am sure there is some strange option that has been chosen by accident,
> but I can't come up with where.
>
> Any help out there!
>
>
>

Also check your options. Auto Calc maybe switch off

Tools > Options > Calcualtion Tab then click Auto Calc

VBA Noob

> Check cell format! It is probably set to Text. Set it to General!

Just a little add-on to Stefi's note above (and as hinted in Steel Monkey's
response) ... The formula needs to be re-confirmed* before it'll work after
re-formatting to General (or Number). *Eg: click inside the formula bar,
press ENTER. The re-formatting alone will not fire the formula.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Select entire column and go Data-->Text to Column, then just click finish.
your problem will solve.

"TaiwanSwede" wrote:

> Help!
>
> Something is weird with my Excel sheet. When I enter a formula, for example
> =AB2+AB3
> Excel does not calculate the result. Instead the formula just stays as it
> is. I am sure there is some strange option that has been chosen by accident,
> but I can't come up with where.
>
> Any help out there!
>
>
>

My kingdom for a CLEAR response...

The issue is this: When you use a formula to reference a cell value (=b3), Excel 2007 also references the format of the cell and copies that as well. Most of the time this is what you want. The vexing problem happens when you don't realize the STARTING format of the cells you're working with...pretty common in existing worksheets. So...here are all the bits and pieces you need to know:

1. A formula entered in a cell formatted as TEXT will not calculate. (the crux of your problem)
2. Changing the cell to General seems like it SHOULD work, but an EXISTING formula still doesn't calculate.
3. Reentering or editing the formula will make it work...BUT (and this is the detail that I chased around for a while) watch out if the referenced cell is formatted as TEXT too. When Excel copies the cell along with the formatting, it (you guessed it) reverts the formula cell back to TEXT again. Not a problem unless and until you need to edit the formula. (more on this in a second)
4. With the Home ribbon bar selected, the Number panel displays the current cell format. Try this: set both A1 and B1 to TEXT and type Hello in B1. In A1 enter =B1...it simply displays the entered text. Now change A1 to General format...nothing changes. Now reenter the formula in a1 (=B1) and now A1 contains Hello...BUT...it is also now formatted as TEXT again. If you edit the formula, guess what...it no longer calculates!!!!
5. From what I can determine, this issue only happens with a direct cell reference such as =b1. It any other calculation or manipulation occurs, the formatting is not copied so the issue is less recurring. In the above example, after changing the format of A1 to General, edit the formula, but instead of simply =b1, make it =left(b1,4). Now A1 will contain "Hell" (couldn't resist), but the format remains General.

The way I got trapped was that I wanted to extract a product item number out of a UPC code. When the formula didn't work initially, I simplified the entry to just =c1 and stumbled into the TEXT format issue. Changing it to General and reentering the formula put the whole code in into C1 so I thought I had fixed it. Of course, then I edited the formula to the necessary =mid(f1,7,5) and BAM...it stayed that way (due to the format copy described above.) The behavior appeared erratic, but it's actually VERY consistent and the logic is: If at the time a formula is entered in a cell, if that cell is formatted as TEXT, don't calculate it.

I hope this saves you more time than it took you to read this...but now yous should have enough details to get back to work!! ;-)

MoreDrums

Originally Posted by Muhammed Rafeek M

Select entire column and go Data-->Text to Column, then just click finish.
your problem will solve.

"TaiwanSwede" wrote:

> Help!
>
> Something is weird with my Excel sheet. When I enter a formula, for example
> =AB2+AB3
> Excel does not calculate the result. Instead the formula just stays as it
> is. I am sure there is some strange option that has been chosen by accident,
> but I can't come up with where.
>
> Any help out there!
>
>
>

HaHa...yeah, I noticed after I posted. Still, I just struggled with this, so perhaps someone else will benefit.

MoreDrums - that helped me a lot - kept running into that same problem. It's always something simple to fix, isn't it?

I've recently run into a real conundrum. I have large spreadsheets that have always worked. I am well versed in the cell formatting and auto calc options. However, recently I did something which has caused all of my spreadsheets to require manual calculation. The F9 does not work to recalculate, calculation is set to auto, cells are all 'number' formatted. I have to calculate every field by editing with F2 and then clicking enter. I can also copy a row and then paste the same. But I am wondering what the problem might be? What could I have possibly done on an application level for this to happen? I have hundreds of spreadsheets and they are all doing the same thing. Any ideas would be appreciated.
Thanks

I wanted to chime in. I have seen this behavior ocurr on workbooks that previously operated without issue. I am wondering if it is a result of a recent MS Office update.

Originally Posted by nicksto

I've recently run into a real conundrum. I have large spreadsheets that have always worked. I am well versed in the cell formatting and auto calc options. However, recently I did something which has caused all of my spreadsheets to require manual calculation. The F9 does not work to recalculate, calculation is set to auto, cells are all 'number' formatted. I have to calculate every field by editing with F2 and then clicking enter. I can also copy a row and then paste the same. But I am wondering what the problem might be? What could I have possibly done on an application level for this to happen? I have hundreds of spreadsheets and they are all doing the same thing. Any ideas would be appreciated.
Thanks

I got exactly the same problem. Working with F2 + Enter works, but if you have to recalculate a whole column, it can take quite some time. F9 doesn't work and auto calc is on. What should I do? Does anyone have any clue of the problem?

BTW, I'm on Excel 2003 SP3 (11.8335.8333) running on Windows XP.

Thanks!

I have a similar problem, also under XP. I have a spreadsheet which has worked for years, containing several udfs. I recently bought a laptop, running XP Pro SP 3 as does my desktop. I installed on the laptop the same copy of Office 2000 as is installed on my desktop. My spreadsheet on my desktop machine calculates correctly. The very same spreadsheet on my laptop does not: the functions that calculate correctly on my desktop machine give #VALUE errors on the laptop. It is the same spreadsheet with the same formatting of cells. The error crops up when any cell in the data section is changed, and changing the cell back to its original value does not clear the error.

Reinstalling MS Office on the laptop does not cure the problem.

I am baffled.

Thanks.