Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Number OK in multiplication, NOT in Sum ?
My A/c s/w exports data to Excel in Indian Number formats with lacs comma at
5th place. I have to remove (with Find - Replace command) this comma in order for excel to make it amenable for processing. Strangely, after removing the comma, I I can do valid multiplications on the numeric data. However, when I try to do the Sum of the data range, it gives 'Zero' result ! When I do the further refinement of the numbers by 'Value ( )' function, the data gives correct result with 'Sum ( )' function. I discovered this after sweating it out for a long time. -- Shrikant |
#2
|
|||
|
|||
Presumably this is because the values are text and the find/replace leaves
them as text. Summing is trying to sum as text, hence 0, whereas multiplying will coerce them to numbers. Instead of find/replace, try =VALUE(SUBSTITUTE(A1,",","") and copy down. Sum should work then -- HTH RP (remove nothere from the email address if mailing direct) "Shrikant" wrote in message ... My A/c s/w exports data to Excel in Indian Number formats with lacs comma at 5th place. I have to remove (with Find - Replace command) this comma in order for excel to make it amenable for processing. Strangely, after removing the comma, I I can do valid multiplications on the numeric data. However, when I try to do the Sum of the data range, it gives 'Zero' result ! When I do the further refinement of the numbers by 'Value ( )' function, the data gives correct result with 'Sum ( )' function. I discovered this after sweating it out for a long time. -- Shrikant |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for current month minus one = Quarter number in a macro. | Excel Discussion (Misc queries) | |||
Roundup to the nearest number that is divisible by 3 | Excel Worksheet Functions | |||
doubling a number X number of times | Excel Worksheet Functions | |||
Count Number of Characters in a cell? | Excel Discussion (Misc queries) | |||
Convert week number into calendar month? | Excel Worksheet Functions |