Software

General discussion

Gravatar
Locked

Excel prob - formulas do not show result

By DiggerJackson ·
In some problem workbooks, formulas do not display their results. My question is how do I display the formula result instead of the formula itself? I am using Excel 2003.

I am having this problem with SUM and CONCATENATE -- I can't get the result to display, only the formula itself. I have run into this problem in several workbooks before, but I never have been able to rectify it. It is also difficult to reproduce the problem. I have kept the worksheets that have these problems and can send them to anyone willing to look. My only workaround is to open a new workbook and re-enter the data. I can even copy the data from the problem workbook and paste it unchanged into a new workbook and the formulas display their results as expected.

Here are some more details:

With SUM, I have set the format to number for both the data and the formula, but this does not fix the problem. Additionally, I have made sure that I do not have "numbers stored as text" by using the paste special "add 0" or "multiply by 1" workarounds. When I use Formula Auditing | Evaluate Formula, it shows me the correct formula and result. When I exit out of formula Auditing, only the formula is displayed in my workbook.

I can hit [CTRL+`] and that changes the formula to show the value. But it has the unfortunate side-effect of messing up the formatting of the entire workbook.

What is going on here?

One observation: when I type in numbers in these problem worksheets, they stay as number category "General" but they become left-justified in the cell. The strange thing is that I cannot change the justification -- it stays left-justified no matter what I do. Normally, when you enter a number into a "General" category cell, it automatically becomes right-justified. I can set the cells to category type "Number" and when I enter data (either numbers or text), it still is fixed left-justified. This does not happen in non-problem workbooks.

Help!

This conversation is currently closed to new comments.

38 total posts (Page 2 of 4)   Prev   01 | 02 | 03 | 04   Next
Thread display: Collapse - | Expand +

All Comments

gravatar
Collapse -

by zimonar In reply to Excel prob - formulas do ...

And if not, try to retype your data(s) on another sheet in a new workbook, and not copy.

gravatar
Collapse -

by curtis.hearn In reply to Excel prob - formulas do ...

I found success with a very simple but dumb step. If the names of functions in your formula are lowercase, try manually capitalizing them. This seems to "trick" the system into showing the formula result instead of the formula itself. It worked once for me, but I didnt test it extensively to see if it always works.

Here's an example: Change "=if(isblank(A1)=true," ","It's Blank!") to "=IF(ISBLANK(A1)=TRUE," ","It's Blank!")

Hope it works for you!

gravatar
Collapse -

It works

by sireynolds In reply to

What a farce. Was reading this forum because of this problem (obviously!) and yes using capitals to retype the formula works perfectly, weird, because when typing it excel gives you the 'hints' but then displays as text!

gravatar
Collapse -

Format the cell first, then enter the formula

by Cjohnsonmn In reply to Excel prob - formulas do ...

I realize it's been months since the original message, but I ran into this twice recently and I haven't seen this solution written down. For me, Excel displays the formula, not the result, occasionally in COLUMNS THAT I HAVE INSERTED. When I looked at the format for that column (select column, Format, Cells..., Number tab), no format was selected. Set the format to General, click OK, then delete and retype the formula and it should now display the result.

gravatar
Collapse -

THIS WORKED

by cecilia In reply to Format the cell first, th ...

Like a dummy I was changing the formating of the cells with the data, not the cells with the actual FORMULA. Once I turned those cells into GENERAL formating, everything worked like a charm. Thank you.

gravatar
Collapse -

Fix

by hcmccombie In reply to Format the cell first, th ...

I think that everyone is correct on the issue here. The issue I believe is with Excel thinking it is a text string. After you change the format to general, go to the formula and just add parantheses around a term within the cell and hit return. This works for me.

gravatar
Collapse -

Worked for me too!

by chrisubus In reply to Format the cell first, th ...

That did the trick for me too! Thanks so much!

gravatar
Collapse -

I created a new worksheet - It still did not work

by john_rivers In reply to Excel prob - formulas do ...

Hi All, I have just experiance this problem on my wifes laptop and I thought I was going mad. I have tried most (if not all) of the suggestions that have been posted, and NOTHING worked everytime as a fix. Sometimes re-formatting the cell did the trick, sometimes capitalizing the formula worked. I have even retyped the formula in a new worksheet that I added to the file, and this only had about 25% success. If anyone has any sure fire methods to fix this issue, please let me know.

gravatar
Collapse -

remove formatting?

by elongp In reply to I created a new worksheet ...

What about removing all of the formatting, then applying the General formatting. Just a thought. All of this apply this or that formatting could be confusing it's little Excel brain.

gravatar
Collapse -

Does anyone know the cause of this problem?

by LocoLobo In reply to Excel prob - formulas do ...

I have been using Excel 2003 for a few months and haven't seen it yet.

The only way I can get this problem is if I format a cell as text then enter the formula. Then the cell displays the formula. As soon as I changed the format to general the result displayed.

Back to Software Forum
38 total posts (Page 2 of 4)   Prev   01 | 02 | 03 | 04   Next

Start or search

Related Discussions

Related Forums