Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't show sum
I'm a novice...and self-taught, but I love Excel. Can someone please explain
why when I enter a formula to add a column of numbers, the formula is showing in the cell and not the sum. the formula is: =sum(e27:e42) Have they changed how a formula is written in Excel 2007? Even after I format the cells to be numbers versus text, the sum function won't show a sum...just the formula. Sometimes all I get is a zero. How can I fix this? It's very frustrating. Thx. -- Novice Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't show sum
Hi,
In earlier versions, Tools menu, Options. On the View tab, deselect Formulas. Not sure how this differs for 2007 Regards - Dave. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't show sum
Hi Dave...this worked for a couple of other formulas, but not for the ones in
question. I'm running Excel 2007. I'm stumped. Can you help? -- Novice Thanks "Dave" wrote: Hi, In earlier versions, Tools menu, Options. On the View tab, deselect Formulas. Not sure how this differs for 2007 Regards - Dave. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't show sum
Try this:
Select the cell with the text formula, then: <Ctrl <Shift < ~ Then <F2 Then <Enter The first is a keyboard shortcut to format the cell to General. The second is to enter the "Edit" mode. And the 3rd is to register the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Parry" wrote in message ... Hi Dave...this worked for a couple of other formulas, but not for the ones in question. I'm running Excel 2007. I'm stumped. Can you help? -- Novice Thanks "Dave" wrote: Hi, In earlier versions, Tools menu, Options. On the View tab, deselect Formulas. Not sure how this differs for 2007 Regards - Dave. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't show sum
On Jun 20, 10:48 am, Parry wrote:
Hi Dave...this worked for a couple of other formulas, but not for the ones in question. I'm running Excel 2007. I'm stumped. Can you help? -- Novice Thanks "Dave" wrote: Hi, In earlier versions, Tools menu, Options. On the View tab, deselect Formulas. Not sure how this differs for 2007 Regards - Dave. Reformatting from Text to Number doesn't fix the problem of the cells being treated as Text. Enter a 1 into a free cell that is definitely General format then copy it then select E27:E42 and Paste Special using the Multiply operation. The Sum should then be OK if that was the problem. Ken Johnson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't show sum
It sounds like the cell was formatted as Text when the formula was entered.
Try this... delete the entry in the cell, then change its format to General, and then type the formula over again. Rick "Parry" wrote in message ... Hi Dave...this worked for a couple of other formulas, but not for the ones in question. I'm running Excel 2007. I'm stumped. Can you help? -- Novice Thanks "Dave" wrote: Hi, In earlier versions, Tools menu, Options. On the View tab, deselect Formulas. Not sure how this differs for 2007 Regards - Dave. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't show sum
Tried that...several times and it still shows the formula and not the sum.
Sorry. -- Novice Thanks "Rick Rothstein (MVP - VB)" wrote: It sounds like the cell was formatted as Text when the formula was entered. Try this... delete the entry in the cell, then change its format to General, and then type the formula over again. Rick "Parry" wrote in message ... Hi Dave...this worked for a couple of other formulas, but not for the ones in question. I'm running Excel 2007. I'm stumped. Can you help? -- Novice Thanks "Dave" wrote: Hi, In earlier versions, Tools menu, Options. On the View tab, deselect Formulas. Not sure how this differs for 2007 Regards - Dave. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't show sum
I found a general cell, put a one in it, copied it to the cell in question
that kept showing the formula...and now I have a total, but it's wrong. Instead of 17.00 it's 0.00. I also don't know what you mean by Paste Sepcial using the Multiply operation...so I couldn't do that. Can you be more specific? -- Novice Thanks "Ken Johnson" wrote: On Jun 20, 10:48 am, Parry wrote: Hi Dave...this worked for a couple of other formulas, but not for the ones in question. I'm running Excel 2007. I'm stumped. Can you help? -- Novice Thanks "Dave" wrote: Hi, In earlier versions, Tools menu, Options. On the View tab, deselect Formulas. Not sure how this differs for 2007 Regards - Dave. Reformatting from Text to Number doesn't fix the problem of the cells being treated as Text. Enter a 1 into a free cell that is definitely General format then copy it then select E27:E42 and Paste Special using the Multiply operation. The Sum should then be OK if that was the problem. Ken Johnson |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't show sum
Thanks, but this didn't work either. Why is this so hard? I've even tried
to use a formula in another cell to total it, and still can't get a sum. -- Novice Thanks "RagDyer" wrote: Try this: Select the cell with the text formula, then: <Ctrl <Shift < ~ Then <F2 Then <Enter The first is a keyboard shortcut to format the cell to General. The second is to enter the "Edit" mode. And the 3rd is to register the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Parry" wrote in message ... Hi Dave...this worked for a couple of other formulas, but not for the ones in question. I'm running Excel 2007. I'm stumped. Can you help? -- Novice Thanks "Dave" wrote: Hi, In earlier versions, Tools menu, Options. On the View tab, deselect Formulas. Not sure how this differs for 2007 Regards - Dave. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't show sum
Even if I find a general cell, and enter the formula for the sum of that
range, I still get 0.00. The numbers in the range are formatted as numbers...unless they were once text? Could that be the problem? -- Novice Thanks "RagDyer" wrote: Try this: Select the cell with the text formula, then: <Ctrl <Shift < ~ Then <F2 Then <Enter The first is a keyboard shortcut to format the cell to General. The second is to enter the "Edit" mode. And the 3rd is to register the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Parry" wrote in message ... Hi Dave...this worked for a couple of other formulas, but not for the ones in question. I'm running Excel 2007. I'm stumped. Can you help? -- Novice Thanks "Dave" wrote: Hi, In earlier versions, Tools menu, Options. On the View tab, deselect Formulas. Not sure how this differs for 2007 Regards - Dave. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't show sum
That's it...the numbers I was trying to add, had at one time been formatted
as text. I found a one that was general, copied it into these cells, and now they are adding. Thanks a lot...great little puzzle. -- Novice Thanks "Ken Johnson" wrote: On Jun 20, 10:48 am, Parry wrote: Hi Dave...this worked for a couple of other formulas, but not for the ones in question. I'm running Excel 2007. I'm stumped. Can you help? -- Novice Thanks "Dave" wrote: Hi, In earlier versions, Tools menu, Options. On the View tab, deselect Formulas. Not sure how this differs for 2007 Regards - Dave. Reformatting from Text to Number doesn't fix the problem of the cells being treated as Text. Enter a 1 into a free cell that is definitely General format then copy it then select E27:E42 and Paste Special using the Multiply operation. The Sum should then be OK if that was the problem. Ken Johnson |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't show sum
On Jun 20, 12:40 pm, Parry wrote:
I found a general cell, put a one in it, copied it to the cell in question that kept showing the formula...and now I have a total, but it's wrong. Instead of 17.00 it's 0.00. I also don't know what you mean by Paste Sepcial using the Multiply operation...so I couldn't do that. Can you be more specific? -- Novice Thanks "Ken Johnson" wrote: On Jun 20, 10:48 am, Parry wrote: Hi Dave...this worked for a couple of other formulas, but not for the ones in question. I'm running Excel 2007. I'm stumped. Can you help? -- Novice Thanks "Dave" wrote: Hi, In earlier versions, Tools menu, Options. On the View tab, deselect Formulas. Not sure how this differs for 2007 Regards - Dave. Reformatting from Text to Number doesn't fix the problem of the cells being treated as Text. Enter a 1 into a free cell that is definitely General format then copy it then select E27:E42 and Paste Special using the Multiply operation. The Sum should then be OK if that was the problem. Ken Johnson Hi Parry, I know you've solved the problem. Just thought I'd try to clear up any confusion about my suggestion. If cells (E27:E42) are formatted as Text and then have numbers entered into them, a formula summing those cells will return 0. If you reformat the cells to either General or Number, even though the format has changed, Excel still treats them as Text and the sum remains 0. My suggestion was to get Excel to treat them as numbers by copying a 1 from a General formatted cell, then select E27:E42 then go Edit|Paste Special... to bring up the Paste Special dialog. On that dialog there is an area with the heading "Operation". The choices are None (default), Add, Subtract, Multiply and Divide. If you choose Multiply (or Divide), then pasting multiplies (or divides) each cell by 1. This has no effect on the cells' values but from that point on Excel treats them as numbers and the sum formula should return the expected result. If you have a cell with a formula and instead of seeing the calculated result you see the formula, then that can be caused by the cell being formatted Text before the formula was entered. The above method will not work in this case. You can instead reformat the cell to General, select the whole formula in the Formula Bar (or double click the cell and select it in the cell) then copy and paste (Ctrl C then Ctrl V) then Enter. Having said all that, I prefer RagDyer's method. It works the same way for both formulas and values. Ken Johnson |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't show sum
Wow, thanks for taking the time for the complete explanation. I understand now.
-- Novice Thanks "Ken Johnson" wrote: On Jun 20, 12:40 pm, Parry wrote: I found a general cell, put a one in it, copied it to the cell in question that kept showing the formula...and now I have a total, but it's wrong. Instead of 17.00 it's 0.00. I also don't know what you mean by Paste Sepcial using the Multiply operation...so I couldn't do that. Can you be more specific? -- Novice Thanks "Ken Johnson" wrote: On Jun 20, 10:48 am, Parry wrote: Hi Dave...this worked for a couple of other formulas, but not for the ones in question. I'm running Excel 2007. I'm stumped. Can you help? -- Novice Thanks "Dave" wrote: Hi, In earlier versions, Tools menu, Options. On the View tab, deselect Formulas. Not sure how this differs for 2007 Regards - Dave. Reformatting from Text to Number doesn't fix the problem of the cells being treated as Text. Enter a 1 into a free cell that is definitely General format then copy it then select E27:E42 and Paste Special using the Multiply operation. The Sum should then be OK if that was the problem. Ken Johnson Hi Parry, I know you've solved the problem. Just thought I'd try to clear up any confusion about my suggestion. If cells (E27:E42) are formatted as Text and then have numbers entered into them, a formula summing those cells will return 0. If you reformat the cells to either General or Number, even though the format has changed, Excel still treats them as Text and the sum remains 0. My suggestion was to get Excel to treat them as numbers by copying a 1 from a General formatted cell, then select E27:E42 then go Edit|Paste Special... to bring up the Paste Special dialog. On that dialog there is an area with the heading "Operation". The choices are None (default), Add, Subtract, Multiply and Divide. If you choose Multiply (or Divide), then pasting multiplies (or divides) each cell by 1. This has no effect on the cells' values but from that point on Excel treats them as numbers and the sum formula should return the expected result. If you have a cell with a formula and instead of seeing the calculated result you see the formula, then that can be caused by the cell being formatted Text before the formula was entered. The above method will not work in this case. You can instead reformat the cell to General, select the whole formula in the Formula Bar (or double click the cell and select it in the cell) then copy and paste (Ctrl C then Ctrl V) then Enter. Having said all that, I prefer RagDyer's method. It works the same way for both formulas and values. Ken Johnson |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
not working for me
I have tried all of the suggestions/guidance here and in a KB article of the same subject and I still have the same prob. All I get is a formula in the intended destination. How do I get a simple SUM? Very frustrated. Any help you can provide is greatly appreciated
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
not working for me
It sounds like the cell that holds the formula is formatted as TEXT. Change
the format to GENERAL then double click the cell then hit ENTER. Another possibility is that you may have formula view activated. Navigate to ToolsOptionsView tab. Under Window options, uncheck FormulasOK -- Biff Microsoft Excel MVP <justin something wrote in message ... I have tried all of the suggestions/guidance here and in a KB article of the same subject and I still have the same prob. All I get is a formula in the intended destination. How do I get a simple SUM? Very frustrated. Any help you can provide is greatly appreciated |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
It could be due to an error
Excel sometimes does not evaluate formulas due to errors. I had a similar problem, which I could fix by correcting some circular cell references in some of my cells.
Try the "Error Checking" option in the Furmala tab to find and fix errors. |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't show sum
Try going to 1. excel option 2. formulas right side
calculation options click on Automatic |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't show sum
I ran into a similar problem when I imported a csv file. I found that there
was a character (probably space(s)) in front of the numbers. I did a find (pasted in characters from one of the cells) and replace with an empty string (replace with) and it fixed my problem. I had tired previous suggestions to no affect. It was/were those unprintable characters in front of my numbers that maintained the text format, once gone everything worked. "Parry" wrote: Tried that...several times and it still shows the formula and not the sum. Sorry. -- Novice Thanks "Rick Rothstein (MVP - VB)" wrote: It sounds like the cell was formatted as Text when the formula was entered. Try this... delete the entry in the cell, then change its format to General, and then type the formula over again. Rick "Parry" wrote in message ... Hi Dave...this worked for a couple of other formulas, but not for the ones in question. I'm running Excel 2007. I'm stumped. Can you help? -- Novice Thanks "Dave" wrote: Hi, In earlier versions, Tools menu, Options. On the View tab, deselect Formulas. Not sure how this differs for 2007 Regards - Dave. |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't show sum
Good Afternoon! I am having a similar problem and have tried all of the
suggestions below and nothing worked. I copied some values from my cell phone bill on the net and tried to sum them. The values copied very nicely into Excel but do not sum. If i retype the numbers over the original number they will then start to sum which to me suggests formatting. Keep in mind I've tried all of the below mentioned tricks. Any more thoughts? Thanks! Marcie "Parry" wrote: I'm a novice...and self-taught, but I love Excel. Can someone please explain why when I enter a formula to add a column of numbers, the formula is showing in the cell and not the sum. the formula is: =sum(e27:e42) Have they changed how a formula is written in Excel 2007? Even after I format the cells to be numbers versus text, the sum function won't show a sum...just the formula. Sometimes all I get is a zero. How can I fix this? It's very frustrating. Thx. -- Novice Thanks |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't show sum
I have this problem also. I even tried moving the spreadsheet to different
PCs, and opening it in 2007. In the end I substituted =SUM(A1:A5) for =A1+A2+A3+A4+A5 And low and behold it works. So there seems to be "SUMthing" wrong with this function under certain conditions. Den "Marcie" wrote: Good Afternoon! I am having a similar problem and have tried all of the suggestions below and nothing worked. I copied some values from my cell phone bill on the net and tried to sum them. The values copied very nicely into Excel but do not sum. If i retype the numbers over the original number they will then start to sum which to me suggests formatting. Keep in mind I've tried all of the below mentioned tricks. Any more thoughts? Thanks! Marcie "Parry" wrote: I'm a novice...and self-taught, but I love Excel. Can someone please explain why when I enter a formula to add a column of numbers, the formula is showing in the cell and not the sum. the formula is: =sum(e27:e42) Have they changed how a formula is written in Excel 2007? Even after I format the cells to be numbers versus text, the sum function won't show a sum...just the formula. Sometimes all I get is a zero. How can I fix this? It's very frustrating. Thx. -- Novice Thanks |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't show sum
More on this......
One possible cause is copying/pasteing/linking to a cell with embedded £, $ etc signs in them. They will still add up using A1+A2 etc but SUM cannot hack the embedded signs. The solution is to use the VALUE function which strips out the embedded signs and SUM will now work. This will probably mean duplicate cells to make it work. EG =VALUE(enter in here the link or cell the problem number is in). Then SUM this column/row. This doesn't seem to have anything to do with Options or cell formats. The Menace "Dennis (The Menace) Hayden" wrote: I have this problem also. I even tried moving the spreadsheet to different PCs, and opening it in 2007. In the end I substituted =SUM(A1:A5) for =A1+A2+A3+A4+A5 And low and behold it works. So there seems to be "SUMthing" wrong with this function under certain conditions. Den "Marcie" wrote: Good Afternoon! I am having a similar problem and have tried all of the suggestions below and nothing worked. I copied some values from my cell phone bill on the net and tried to sum them. The values copied very nicely into Excel but do not sum. If i retype the numbers over the original number they will then start to sum which to me suggests formatting. Keep in mind I've tried all of the below mentioned tricks. Any more thoughts? Thanks! Marcie "Parry" wrote: I'm a novice...and self-taught, but I love Excel. Can someone please explain why when I enter a formula to add a column of numbers, the formula is showing in the cell and not the sum. the formula is: =sum(e27:e42) Have they changed how a formula is written in Excel 2007? Even after I format the cells to be numbers versus text, the sum function won't show a sum...just the formula. Sometimes all I get is a zero. How can I fix this? It's very frustrating. Thx. -- Novice Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show and copy values in formula bar containing a formula | Excel Discussion (Misc queries) | |||
Dont show formula in formula bar | Excel Discussion (Misc queries) | |||
formula to show a formula in a different cell | Excel Worksheet Functions | |||
I want the results of a formula to show in cell, NOT THE FORMULA! | Excel Discussion (Misc queries) | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |