| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula problems relating to Format
I copied a list of numbers from an on-line database. Everything looks legit,
I even converted all formats to Numbers, but when I apply the math functions (e.g. Sum or Avg), I get zero or errors. I believe this has to do with formating - a friend of mine got one column to temporarily work after going somewhere and clicking "convert", but promptly forgot how she did it. On the other hand, if I hand-type these numbers in, and do the function, everything works fine. It is only with the numbers I pasted it (and doing paste special values doesn't help). What a mystery -- help! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula problems relating to Format
First get rid of any blanks or chr(160)'s in your data using find/replace.
Second enter 1 in an un-used cell, copy that cell, and then paste/special/multply on top of your data. This should make them "real" numbers. -- Gary's Student gsnu200703 "Exceldoesnotknowitall" wrote: I copied a list of numbers from an on-line database. Everything looks legit, I even converted all formats to Numbers, but when I apply the math functions (e.g. Sum or Avg), I get zero or errors. I believe this has to do with formating - a friend of mine got one column to temporarily work after going somewhere and clicking "convert", but promptly forgot how she did it. On the other hand, if I hand-type these numbers in, and do the function, everything works fine. It is only with the numbers I pasted it (and doing paste special values doesn't help). What a mystery -- help! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula problems relating to Format
WOW, that's AWESOME! I had the same problem and this worked like a charm!!
Thanks!! Gary''s Student wrote: First get rid of any blanks or chr(160)'s in your data using find/replace. Second enter 1 in an un-used cell, copy that cell, and then paste/special/multply on top of your data. This should make them "real" numbers. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula problems relating to Format
You are very welcome!
-- Gary's Student gsnu200703 "Rita Palazzi" wrote: WOW, that's AWESOME! I had the same problem and this worked like a charm!! Thanks!! Gary''s Student wrote: First get rid of any blanks or chr(160)'s in your data using find/replace. Second enter 1 in an un-used cell, copy that cell, and then paste/special/multply on top of your data. This should make them "real" numbers. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Is it possible to copy a conditional format formula as paste value | Excel Discussion (Misc queries) | |||
| Match then lookup | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| I have problems keeping the merge formula to use as a template. | Excel Discussion (Misc queries) | |||
| SUMPRODUCT and format problems (2) | Excel Discussion (Misc queries) | |||
Linear Mode
