Number OK in multiplication, NOT in Sum ?

[Guest]

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.

 

[Bob Phillips]

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)