General discussion


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.


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Uncheck in options

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

Go to tools, options, and the View tab. Uncheck Formulas under windows options. Then your cells will show the results, not the formulas.

Collapse -

by vicvergara In reply to Uncheck in options

This is the only solution that worked for me, I tried all the others previously posted.
-Do you know why does this "Formulas" checkbox is checked even when the workbook is new and i never entered the options menu?

Collapse -

Now THIS worked!

by delafe In reply to Uncheck in options

Thanks, this was the problem.

Collapse -

Excel Problem - Text formulas display as 0

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

Restart XL2K3. Open your previous spreadsheet.
Copy the fields that you need. Create a blank worksheet, paste the records. Add new formulas as per requirement eg =A2&"."&B2
Worked for me. Nothing else did.

Collapse -

Similar Solution

by mrchrstn In reply to Excel Problem - Text form ...

I also tried everything up to the previous post, and got nothing.

Only the last post solved my problem (though I'd been able to work through similar problems with the previously offered solutions).

I closed and reopened the file, and when I brought it back up, Excel told me I had a circular reference (which was true). I fixed the circ ref, and everything displayed fine.


Collapse -

Similar Problem

by jtackers In reply to Similar Solution

I have a similar problem with worksheets that are exported from software that controls an instrument in our lab (BioRad iQ5 Real-Time Thermocycler). None of the easy fixes seem to work (F9, formatting, etc.) A couple of times I have managed to fix the problem, but it is usually not clear exactly what I did. Sometimes if I hit ctrl + `, the formula (which won't evaluate) will outline the reference cells in color, other times it won't. Manually typing in caps has seemed to work, but not recently. The autocomplete (yellow box with formula syntax hint) function seems to recognize what I type as a formula, but still the formula won't evaluate. One time I looked at settings for "allow users to edit ranges", without changing anything, and that seemed to help; but I couldn't replicate it. What worked most recently was when the non-evaluating formula cell was active (i.e. I was editing the formula), I clicked the green check icon button in the formula bar and the formula evaluated.

I am not sure why this is happening, but the workbooks exported from the iQ5 software have some strange properties. First off they are protected (luckily no password), and have frozen panes and sometimes hidden columns. They also have shaded areas and colored fonts, which are very unusual properties for exported files from instrument software. The other thing that is unusual is the way in which they are exported; you just right click and a workbook (with multiple, populated sheets) opens up in Excel. Usually you save data tables to a csv format or something similar and then open it up. It's fancy and convenient until you try to edit the sheet. Obviously this formula problem is a hangover from the export function. The software has the look of Labview, but I am not certain that it was developed with that.

I was using RIGHT and LEFT functions to create new variable columns in Excel before importing into a statistical analysis program.

Collapse -

Auto Calc not set

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

In Excel2k3, goto Tools>Options>Calculation. Set the Radio button from Manual to Automatic.


Collapse -

by wolvers In reply to Auto Calc not set

The Control and tilda will change your formulae back and forth from text to values: Ctrl~

Collapse -

by wolvers In reply to Auto Calc not set

The Control and tilda will change your formulae back and forth from text to values: Ctrl~

Collapse -

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

I had the same issue. It seems that the SUM display worked when I made a single column of numbers SUM in a given new file. Then after I opened the file up and created a second SUM column, I had the display issue. I found that after I entered the formula for that second column and saved the file, when I opened the file up again the result (SUM) would display....go figure :)

Related Discussions

Related Forums