General discussion

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 1 of 4)   01 | 02 | 03 | 04   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

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

Select your worksheet and click Format from the Menu bar, click Cells?, activate the Number tab and select General from Category. Activate Alignment tab and select General from Horizontal drop down arrow, in Text alignment. Then select Bottom from Vertical drop down arrow, in Text alignment and click OK. Try again Ctrl+` if the formula result is not displayed.

Collapse -

by DiggerJackson In reply to

Poster rated this answer.

Collapse -

by pmwork1 In reply to

No this doeas not work, and beware if you have a range of different formats throughout, percentage etc selecting the worksheet changes ALL to that format.

Not a godd recommendation sorry

Collapse -

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

Thank you for your response Zimonar {Answer 1 from 05/24/2006}.

I selected the entire worksheet and set format cells as follows:
[category="Number", horizontal alignment="General", and vertical="Bottom"]

Nothing changed. All of my numbers stayed left-justified and the formulas displayed as entered, not with their results. CTRL+` does correct the problem (right-justify all of the numbers and display the formula results), but the rest of the workbook's formatting changes from the way I set it and it requires a great deal of work to reset it all.

As I mentioned in my post, I can copy the numbers and formulas in this problem worksheet and paste them into a new worksheet and everything works as expected (the problems no longer exist). However, when I do this, most of the formatting is lost and I have a lot of specially formatted text and numbers in the worksheet. Thus I cannot simply copy and paste a formatted worksheet (or a portion of one) into a new one. If I do, I then have to go back and re-format everything. If I copy the entire worksheet, the problem travels with it!

I also tried copying the numbers and SUM formula and then Paste Special ! Values. The result (the sum of the numbers) is then correctly displayed, but the numbers (and the sum) remain left-justified and the formula is no longer there, just the result. The category of everything remains numeric, but you cannot use any of these numbers in any formulas (not even =A1+B1 -- the addition formula is displayed, but not the result). If I do a CTRL+`, the formulas work but all of the formatting is lost.

Sometimes hitting F2 & Enter fixes these numbers not being treated as numbers problems, but that is not working in this problem worksheet.

So I am still searching for the answer.

Collapse -

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

Sorry, I forget to tell you to select also Context from Text direction drop down arrow under Right-to-left, in Format Cells dialog box. Select your worksheet, reactivate the Alignment tab and select Context.
- [category="Number", horizontal alignment="General", and vertical="Bottom"], leave it as it is and click OK.
- Try again Ctrl+` if the formula result is not displayed.
- I think your worksheet is already formatted incorrectly and saved. That?s why you are having problems.

Collapse -

by DiggerJackson In reply to

Thank you for your response Zimonar {Answer 2 from 05/25/2006}.

I tried setting context in the text direction dialog box, but that did not fix the problem.

The bottom line is that turning on formula auditing gets the formulas to display the results, but the formatting is way off.

Here are some other things that I have tried:

1)
At any time, if I turn off the formula auditing mode (CTRL+`) the formatting comes back but the formulas do not work!

2)
Coping and pasting the contents into a new worksheet does not make the problem go away. The new worksheet has working formulas (displaying results), but the formatting is lost. Incidentally, turning formula auditing off (CTRL+`) causes the formatting to come back perfectly, but the formulas no longer display their results.

3) After I copy the problem worksheet to a new one I then tried to copy & paste the formats -- I copied the contents again from the original sheet and did a Paste Special | Formats -- the formatting does not come back -- it appears to have no effect.

4)
I also tried deleting all Excel startup files by exiting Excel and then deleting the C:\Documents and Settings\DJ\Application Data\Microsoft\Excel folder (contains XLSTART\PERSONAL.XLS).

I then started Excel and if I had closed the problem workbook with formula auditing on, it opens up with it on. I repeated it with leaving formula auditing off and it opens back up off. Losing my PERSONAL.XLS did not appear to help.

I cannot get the formulas to display their results and keep the formatting that I explicitly set.

Is this one of those unfixable problems? I'm still searching for a solution and I have some problem Excel files if anyone wants to see these problems happen on their own computer.

Collapse -

by DiggerJackson In reply to

Thank you for your response Zimonar {Answer 2 from 05/25/2006}.

I tried setting context in the text direction dialog box, but that did not fix the problem.

The bottom line is that turning on formula auditing gets the formulas to display the results, but the formatting is way off.

Here are some other things that I have tried:

1)
At any time, if I turn off the formula auditing mode (CTRL+`) the formatting comes back but the formulas do not work!

2)
Coping and pasting the contents into a new worksheet does not make the problem go away. The new worksheet has working formulas (displaying results), but the formatting is lost. Incidentally, turning formula auditing off (CTRL+`) causes the formatting to come back perfectly, but the formulas no longer display their results.

3) After I copy the problem worksheet to a new one I then tried to copy & paste the formats -- I copied the contents again from the original sheet and did a Paste Special | Formats -- the formatting does not come back -- it appears to have no effect.

4)
I also tried deleting all Excel startup files by exiting Excel and then deleting the C:\Documents and Settings\DJ\Application Data\Microsoft\Excel folder (contains XLSTART\PERSONAL.XLS).

I then started Excel and if I had closed the problem workbook with formula auditing on, it opens up with it on. I repeated it with leaving formula auditing off and it opens back up off. Losing my PERSONAL.XLS did not appear to help.

I cannot get the formulas to display their results and keep the formatting that I explicitly set.

Is this one of those unfixable problems? I'm still searching for a solution and I have some problem Excel files if anyone wants to see these problems happen on their own computer.

Collapse -

by DiggerJackson In reply to

Thank you for your response Zimonar {Answer 2 from 05/25/2006}.

I tried setting context in the text direction dialog box, but that did not fix the problem.

The bottom line is that turning on formula auditing gets the formulas to display the results, but the formatting is way off.

Here are some other things that I have tried:

1)
At any time, if I turn off the formula auditing mode (CTRL+`) the formatting comes back but the formulas do not work!

2)
Coping and pasting the contents into a new worksheet does not make the problem go away. The new worksheet has working formulas (displaying results), but the formatting is lost. Incidentally, turning formula auditing off (CTRL+`) causes the formatting to come back perfectly, but the formulas no longer display their results.

3) After I copy the problem worksheet to a new one I then tried to copy & paste the formats -- I copied the contents again from the original sheet and did a Paste Special | Formats -- the formatting does not come back -- it appears to have no effect.

4)
I also tried deleting all Excel startup files by exiting Excel and then deleting the C:\Documents and Settings\DJ\Application Data\Microsoft\Excel folder (contains XLSTART\PERSONAL.XLS).

I then started Excel and if I had closed the problem workbook with formula auditing on, it opens up with it on. I repeated it with leaving formula auditing off and it opens back up off. Losing my PERSONAL.XLS did not appear to help.

I cannot get the formulas to display their results and keep the formatting that I explicitly set.

Is this one of those unfixable problems? I'm still searching for a solution and I have some problem Excel files if anyone wants to see these problems happen on their own computer.

Collapse -

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

I believe what will work, and has worked in my case, is a simple recalculation. Select sheet or problem areas, format as general cell catagory, press F9 to recalculate, then retype cell formula if needed. Hope this works for you.

Collapse -

I've tried this one also and it works!

by romdel94 In reply to

Thanks a lot man. You can also go to Tools, Options, Calculation then click Calc_Now(F9).

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

Related Discussions

Related Forums