Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sum function not actually summing??????
Excel 03
OK, I'm trying to sum numbers a column. This is exact representation, cells F19 to F22: $11,087 $3,400 $2,400 $1,211 I sum into F23 and get the result of only F19 *$11,087.* F23 cell formula shows as =SUM(F19:F22) This is a sheet I downloaded. I added a row (F21) to insert the $2400. When first sum action didn't work, I thought maybe it was based on cell format, so I reformatted to label them currency (this shows in cell properties), so it's not an issue of cell format standardization. Also, I copied the numbers from F column to a new one and pasted as *Special -- values only* and STILL get only the result of the top #, $11,087. WTF??? and What can I do???? |
#2
|
|||
|
|||
sum function not actually summing??????
Let's make sure that Excel knows that you are dealing with numbers:
1, in an un-used cell put 1. 2. copy this cell 3. select the cells containing the numbers $11,087 thru $1,211 4. do an Edit Paste Special and check the multiply button This will fix the situation is Excel is confused numbers vs text. -- Gary''s Student "RedHeadedMenace" wrote: Excel 03 OK, I'm trying to sum numbers a column. This is exact representation, cells F19 to F22: $11,087 $3,400 $2,400 $1,211 I sum into F23 and get the result of only F19 *$11,087.* F23 cell formula shows as =SUM(F19:F22) This is a sheet I downloaded. I added a row (F21) to insert the $2400. When first sum action didn't work, I thought maybe it was based on cell format, so I reformatted to label them currency (this shows in cell properties), so it's not an issue of cell format standardization. Also, I copied the numbers from F column to a new one and pasted as *Special -- values only* and STILL get only the result of the top #, $11,087. WTF??? and What can I do???? |
#3
|
|||
|
|||
sum function not actually summing??????
Hi,
Click on each cell and ensure that the $ is not included in the cell. When copying and pasting, it looks like the $ was also pasted into the cells and excel didn't recognize this as numbers. When a cell is formatted as currency, the currency symbol isn't actually added to the cell. I removed the $ symbols and the sum function worked. Cheers, -- Kevin Lehrbass www.spreadsheetsolutions4u.com "RedHeadedMenace" wrote: Excel 03 OK, I'm trying to sum numbers a column. This is exact representation, cells F19 to F22: $11,087 $3,400 $2,400 $1,211 I sum into F23 and get the result of only F19 *$11,087.* F23 cell formula shows as =SUM(F19:F22) This is a sheet I downloaded. I added a row (F21) to insert the $2400. When first sum action didn't work, I thought maybe it was based on cell format, so I reformatted to label them currency (this shows in cell properties), so it's not an issue of cell format standardization. Also, I copied the numbers from F column to a new one and pasted as *Special -- values only* and STILL get only the result of the top #, $11,087. WTF??? and What can I do???? |
#4
|
|||
|
|||
sum function not actually summing??????
did not work. I'd reformatted cells to be both currency and number, and
neither worked then either. But cell props do show formattign worked so no text confusion. (multiplying got me the same numbers, btw, but still no correct sum :-( ) Also need to mention this is only the first section of this doc I started on -- there are many more that will be identical in scope (i.e., insert row, copy cell into new row, sum number range) so I don't really want ot have to hand enter the numbers. "Gary''s Student" wrote: Let's make sure that Excel knows that you are dealing with numbers: 1, in an un-used cell put 1. 2. copy this cell 3. select the cells containing the numbers $11,087 thru $1,211 4. do an Edit Paste Special and check the multiply button This will fix the situation is Excel is confused numbers vs text. -- Gary''s Student "RedHeadedMenace" wrote: Excel 03 OK, I'm trying to sum numbers a column. This is exact representation, cells F19 to F22: $11,087 $3,400 $2,400 $1,211 I sum into F23 and get the result of only F19 *$11,087.* F23 cell formula shows as =SUM(F19:F22) This is a sheet I downloaded. I added a row (F21) to insert the $2400. When first sum action didn't work, I thought maybe it was based on cell format, so I reformatted to label them currency (this shows in cell properties), so it's not an issue of cell format standardization. Also, I copied the numbers from F column to a new one and pasted as *Special -- values only* and STILL get only the result of the top #, $11,087. WTF??? and What can I do???? |
#5
|
|||
|
|||
sum function not actually summing??????
Kevin -- went thru and did this, but each cell was formatted as currency or
number (I tried both) previously. But i mauanlly deleted $'s and still get same result, and cell formatting shows type of last format (curr or num). I have never seen anything like this! Not a single suggestion has worked and it's just mystifying on my end. "Kevin Lehrbass" wrote: Hi, Click on each cell and ensure that the $ is not included in the cell. When copying and pasting, it looks like the $ was also pasted into the cells and excel didn't recognize this as numbers. When a cell is formatted as currency, the currency symbol isn't actually added to the cell. I removed the $ symbols and the sum function worked. Cheers, -- Kevin Lehrbass www.spreadsheetsolutions4u.com "RedHeadedMenace" wrote: Excel 03 OK, I'm trying to sum numbers a column. This is exact representation, cells F19 to F22: $11,087 $3,400 $2,400 $1,211 I sum into F23 and get the result of only F19 *$11,087.* F23 cell formula shows as =SUM(F19:F22) This is a sheet I downloaded. I added a row (F21) to insert the $2400. When first sum action didn't work, I thought maybe it was based on cell format, so I reformatted to label them currency (this shows in cell properties), so it's not an issue of cell format standardization. Also, I copied the numbers from F column to a new one and pasted as *Special -- values only* and STILL get only the result of the top #, $11,087. WTF??? and What can I do???? |
#7
|
|||
|
|||
sum function not actually summing??????
But i manually deleted $'s ...
If you were able to manually delete $s from individual cells, then you *have* to be working with text values (even after you deleted the $s). You wouldn't be able to delete a $ in a cell where that was part of a numerical format (it isn't really a part of the cell contents, it just displays). Sum() is ignoring your text values. Applying number formatting "over" an existing text value won't change that value from text to number, regardless of what format the cell says it has now (a cell can have numerical formatting but still contain text). Since you say Gary's Student prior response didn't help, lets try a different approach. - Add a new column - Select the new column and format it as number - Select a column with "bad" data and copy it -Select the new column and PasteSpecialValues and then PasteSpecialFormulas (but *not* cell formats or number formats). Calculate Better? Delete the "bad" data column (or CopyPaste the New column over Bad) HTH, -- George Nicholson Remove 'Junk' from return address. "RedHeadedMenace" wrote in message ... Kevin -- went thru and did this, but each cell was formatted as currency or number (I tried both) previously. But i mauanlly deleted $'s and still get same result, and cell formatting shows type of last format (curr or num). I have never seen anything like this! Not a single suggestion has worked and it's just mystifying on my end. "Kevin Lehrbass" wrote: Hi, Click on each cell and ensure that the $ is not included in the cell. When copying and pasting, it looks like the $ was also pasted into the cells and excel didn't recognize this as numbers. When a cell is formatted as currency, the currency symbol isn't actually added to the cell. I removed the $ symbols and the sum function worked. Cheers, -- Kevin Lehrbass www.spreadsheetsolutions4u.com "RedHeadedMenace" wrote: Excel 03 OK, I'm trying to sum numbers a column. This is exact representation, cells F19 to F22: $11,087 $3,400 $2,400 $1,211 I sum into F23 and get the result of only F19 *$11,087.* F23 cell formula shows as =SUM(F19:F22) This is a sheet I downloaded. I added a row (F21) to insert the $2400. When first sum action didn't work, I thought maybe it was based on cell format, so I reformatted to label them currency (this shows in cell properties), so it's not an issue of cell format standardization. Also, I copied the numbers from F column to a new one and pasted as *Special -- values only* and STILL get only the result of the top #, $11,087. WTF??? and What can I do???? |
#8
|
|||
|
|||
sum function not actually summing??????
Actually, ignore my suggested solution, it won't help (the values will still
be "numbers stored as text" after PasteSpecialValues). Sorry, should have tested it before posting. Are you sure Gary's Student PasteSpecialMultiply suggestion doesn't solve your problem? It should (make sure your empty cell is formatted as a number before you enter 1). HTH, -- George Nicholson Remove 'Junk' from return address. "George Nicholson" wrote in message ... But i manually deleted $'s ... If you were able to manually delete $s from individual cells, then you *have* to be working with text values (even after you deleted the $s). You wouldn't be able to delete a $ in a cell where that was part of a numerical format (it isn't really a part of the cell contents, it just displays). Sum() is ignoring your text values. Applying number formatting "over" an existing text value won't change that value from text to number, regardless of what format the cell says it has now (a cell can have numerical formatting but still contain text). Since you say Gary's Student prior response didn't help, lets try a different approach. - Add a new column - Select the new column and format it as number - Select a column with "bad" data and copy it -Select the new column and PasteSpecialValues and then PasteSpecialFormulas (but *not* cell formats or number formats). Calculate Better? Delete the "bad" data column (or CopyPaste the New column over Bad) HTH, -- George Nicholson Remove 'Junk' from return address. "RedHeadedMenace" wrote in message ... Kevin -- went thru and did this, but each cell was formatted as currency or number (I tried both) previously. But i mauanlly deleted $'s and still get same result, and cell formatting shows type of last format (curr or num). I have never seen anything like this! Not a single suggestion has worked and it's just mystifying on my end. "Kevin Lehrbass" wrote: Hi, Click on each cell and ensure that the $ is not included in the cell. When copying and pasting, it looks like the $ was also pasted into the cells and excel didn't recognize this as numbers. When a cell is formatted as currency, the currency symbol isn't actually added to the cell. I removed the $ symbols and the sum function worked. Cheers, -- Kevin Lehrbass www.spreadsheetsolutions4u.com "RedHeadedMenace" wrote: Excel 03 OK, I'm trying to sum numbers a column. This is exact representation, cells F19 to F22: $11,087 $3,400 $2,400 $1,211 I sum into F23 and get the result of only F19 *$11,087.* F23 cell formula shows as =SUM(F19:F22) This is a sheet I downloaded. I added a row (F21) to insert the $2400. When first sum action didn't work, I thought maybe it was based on cell format, so I reformatted to label them currency (this shows in cell properties), so it's not an issue of cell format standardization. Also, I copied the numbers from F column to a new one and pasted as *Special -- values only* and STILL get only the result of the top #, $11,087. WTF??? and What can I do???? |
#9
|
|||
|
|||
sum function not actually summing??????
Format affects the display, not the value of a cell. In particular, if
the cell was text orignally, changing the format to a numeric format will not change the contents from text to a number. What value does =COUNT(F19:F22) return? If it returns 1 instead of 4, then you should try the suggestion of Gary's Student, instead of dismissing it out of hand. Jerry RedHeadedMenace wrote: did not work. I'd reformatted cells to be both currency and number, and neither worked then either. But cell props do show formattign worked so no text confusion. (multiplying got me the same numbers, btw, but still no correct sum :-( ) Also need to mention this is only the first section of this doc I started on -- there are many more that will be identical in scope (i.e., insert row, copy cell into new row, sum number range) so I don't really want ot have to hand enter the numbers. "Gary''s Student" wrote: Let's make sure that Excel knows that you are dealing with numbers: 1, in an un-used cell put 1. 2. copy this cell 3. select the cells containing the numbers $11,087 thru $1,211 4. do an Edit Paste Special and check the multiply button This will fix the situation is Excel is confused numbers vs text. -- Gary''s Student "RedHeadedMenace" wrote: Excel 03 OK, I'm trying to sum numbers a column. This is exact representation, cells F19 to F22: $11,087 $3,400 $2,400 $1,211 I sum into F23 and get the result of only F19 *$11,087.* F23 cell formula shows as =SUM(F19:F22) This is a sheet I downloaded. I added a row (F21) to insert the $2400. When first sum action didn't work, I thought maybe it was based on cell format, so I reformatted to label them currency (this shows in cell properties), so it's not an issue of cell format standardization. Also, I copied the numbers from F column to a new one and pasted as *Special -- values only* and STILL get only the result of the top #, $11,087. WTF??? and What can I do???? |
#10
|
|||
|
|||
Versions of Excel up to 97 had a command on the 'Data' menu called 'Text To Columns' which used to do a good job of converting troublesome 'numbers as text' columns like this. I haven't seen it in later versions though. Was it a 'Valuepak' Add-in?
My suggestion would be to copy the troublesome cells, paste them somewhere else. Set the format of the cells you have copied TO to 'Genereal' and, if any $ symbols remain, manually remove them. Then re-select the original troublesome cells and go to 'Edit' - 'Clear' - 'All', then to copy the values from where you pasted them to, re-select the original troublesome cells, then go to 'Edit' - 'Paste Special' and paste Values (not Format, Formula or anything else). Then, re-apply the currency format. BizMark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date & Time | New Users to Excel | |||
Conversion | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions | |||
Find a Function to use accross different worksheets | Excel Worksheet Functions |