Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula shown as a formula rather than it's result
Hi everyone
I have Excel 2003 and a large spreadsheet with many formulae. I had to edit one of them and, when I did, it displayed the formula in the cell, rather than the result. I made sure that there were no spaces in the formula, nor was there a leading apostrophe. None of the other formulae were displayed in this way. In an effort to find the cause, I closed the workbook and started a new one. I typed something into cell A2 then typed the formula <=A2 into D4. It returned the correct result in D4. I tried the similar experiment with the big workbook that's causing problems but it didn't calculate the value of A2 and return it to D4. It merely displayed <=A2 in D4. The questions I have a Is the problem related to the fact that there are several formulae in the workbook or is there a setting that's been applied (somehow) which is causing newly entered formulae to be displayed, rather than evaluated? It's bizarre behaviour, and I want it to stop! Thanks for your time. |
#2
|
|||
|
|||
Since you say that it is only a single formula (cell) that's displaying this
problem, try this: Select the problem cell, then: <Ctrl <Shift <~ Then <F2 Then <Enter What you did was format the cell to General (keyboard shortcut), and then re-entered it. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Tosca" wrote in message ... Hi everyone I have Excel 2003 and a large spreadsheet with many formulae. I had to edit one of them and, when I did, it displayed the formula in the cell, rather than the result. I made sure that there were no spaces in the formula, nor was there a leading apostrophe. None of the other formulae were displayed in this way. In an effort to find the cause, I closed the workbook and started a new one. I typed something into cell A2 then typed the formula <=A2 into D4. It returned the correct result in D4. I tried the similar experiment with the big workbook that's causing problems but it didn't calculate the value of A2 and return it to D4. It merely displayed <=A2 in D4. The questions I have a Is the problem related to the fact that there are several formulae in the workbook or is there a setting that's been applied (somehow) which is causing newly entered formulae to be displayed, rather than evaluated? It's bizarre behaviour, and I want it to stop! Thanks for your time. |
#3
|
|||
|
|||
Perfect!
However - I checked the format of the cell that caused the problem and it was <Text. I did as you suggested and, whilst it displays the result correctly now, the format is still <Text. What exactly does the series of keystrokes do? Thanks "RagDyeR" wrote in message ... Since you say that it is only a single formula (cell) that's displaying this problem, try this: Select the problem cell, then: <Ctrl <Shift <~ Then <F2 Then <Enter What you did was format the cell to General (keyboard shortcut), and then re-entered it. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== |
#4
|
|||
|
|||
Try clicking Format/Cells and changing the formatting [D4] to General. It
sounds like D4 is formatted as text. "Tosca" wrote: Hi everyone I have Excel 2003 and a large spreadsheet with many formulae. I had to edit one of them and, when I did, it displayed the formula in the cell, rather than the result. I made sure that there were no spaces in the formula, nor was there a leading apostrophe. None of the other formulae were displayed in this way. In an effort to find the cause, I closed the workbook and started a new one. I typed something into cell A2 then typed the formula <=A2 into D4. It returned the correct result in D4. I tried the similar experiment with the big workbook that's causing problems but it didn't calculate the value of A2 and return it to D4. It merely displayed <=A2 in D4. The questions I have a Is the problem related to the fact that there are several formulae in the workbook or is there a setting that's been applied (somehow) which is causing newly entered formulae to be displayed, rather than evaluated? It's bizarre behaviour, and I want it to stop! Thanks for your time. |
#5
|
|||
|
|||
I don't believe that you're looking at the same cell when you say that the
cell is *still* Text. Maybe the cell beneath it, after you hit <Enter? As I originally said, <Ctrl <Shift <~ is a keyboard shortcut to format the cell(s) to "General". That formatting alone, however, will *not* produce a working formula. Try it the long way: Format a cell to Text. Enter a formula. You'll see the text displayed *only*. Then, <Format <Cells <Number tab, Click on General, then <OK. You'll *still* see only the text of the formula. Go back and re-check the current format of the cell, and you'll see it now shows as General. Now, with the cell selected, click anywhere in the formula bar, and hit <Enter. OR Double click in the cell (if you have "Edit In Cell" enabled), and hit <Enter. You've re-entered (registered) the formula, and it will now display the results of the calculation. <F2 is a keyboard shortcut to enter the "Edit" mode of a selected cell. So ... all I posted was keyboard shortcuts to: Change format to General, Enter Edit mode, Register the change. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Tosca" wrote in message ... Perfect! However - I checked the format of the cell that caused the problem and it was <Text. I did as you suggested and, whilst it displays the result correctly now, the format is still <Text. What exactly does the series of keystrokes do? Thanks "RagDyeR" wrote in message ... Since you say that it is only a single formula (cell) that's displaying this problem, try this: Select the problem cell, then: <Ctrl <Shift <~ Then <F2 Then <Enter What you did was format the cell to General (keyboard shortcut), and then re-entered it. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== |
#6
|
|||
|
|||
Thank you for the explanation of the shortcuts! It not only made it easier
to understand what you were suggesting as a fix, but better explained what the problem was as well!! Best, Charyn "RagDyeR" wrote in message ... I don't believe that you're looking at the same cell when you say that the cell is *still* Text. Maybe the cell beneath it, after you hit <Enter? As I originally said, <Ctrl <Shift <~ is a keyboard shortcut to format the cell(s) to "General". That formatting alone, however, will *not* produce a working formula. Try it the long way: Format a cell to Text. Enter a formula. You'll see the text displayed *only*. Then, <Format <Cells <Number tab, Click on General, then <OK. You'll *still* see only the text of the formula. Go back and re-check the current format of the cell, and you'll see it now shows as General. Now, with the cell selected, click anywhere in the formula bar, and hit <Enter. OR Double click in the cell (if you have "Edit In Cell" enabled), and hit <Enter. You've re-entered (registered) the formula, and it will now display the results of the calculation. <F2 is a keyboard shortcut to enter the "Edit" mode of a selected cell. So ... all I posted was keyboard shortcuts to: Change format to General, Enter Edit mode, Register the change. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Tosca" wrote in message ... Perfect! However - I checked the format of the cell that caused the problem and it was <Text. I did as you suggested and, whilst it displays the result correctly now, the format is still <Text. What exactly does the series of keystrokes do? Thanks "RagDyeR" wrote in message ... Since you say that it is only a single formula (cell) that's displaying this problem, try this: Select the problem cell, then: <Ctrl <Shift <~ Then <F2 Then <Enter What you did was format the cell to General (keyboard shortcut), and then re-entered it. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== |
#7
|
|||
|
|||
You're quite welcome!
Appreciate the feed-back. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Charyn" wrote in message ... Thank you for the explanation of the shortcuts! It not only made it easier to understand what you were suggesting as a fix, but better explained what the problem was as well!! Best, Charyn "RagDyeR" wrote in message ... I don't believe that you're looking at the same cell when you say that the cell is *still* Text. Maybe the cell beneath it, after you hit <Enter? As I originally said, <Ctrl <Shift <~ is a keyboard shortcut to format the cell(s) to "General". That formatting alone, however, will *not* produce a working formula. Try it the long way: Format a cell to Text. Enter a formula. You'll see the text displayed *only*. Then, <Format <Cells <Number tab, Click on General, then <OK. You'll *still* see only the text of the formula. Go back and re-check the current format of the cell, and you'll see it now shows as General. Now, with the cell selected, click anywhere in the formula bar, and hit <Enter. OR Double click in the cell (if you have "Edit In Cell" enabled), and hit <Enter. You've re-entered (registered) the formula, and it will now display the results of the calculation. <F2 is a keyboard shortcut to enter the "Edit" mode of a selected cell. So ... all I posted was keyboard shortcuts to: Change format to General, Enter Edit mode, Register the change. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Tosca" wrote in message ... Perfect! However - I checked the format of the cell that caused the problem and it was <Text. I did as you suggested and, whilst it displays the result correctly now, the format is still <Text. What exactly does the series of keystrokes do? Thanks "RagDyeR" wrote in message ... Since you say that it is only a single formula (cell) that's displaying this problem, try this: Select the problem cell, then: <Ctrl <Shift <~ Then <F2 Then <Enter What you did was format the cell to General (keyboard shortcut), and then re-entered it. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation applied to formula result | Excel Discussion (Misc queries) | |||
Copying result of formula into another worksheet??? | Excel Discussion (Misc queries) | |||
Is there a formula to spell out a number in excel? | Excel Worksheet Functions | |||
Convert Numeric into Text | Excel Worksheet Functions | |||
Formula Result Correct but value in the cell is wrong | Excel Worksheet Functions |