ABS and formulas never working.

[zoem]

originally i downloaded a csv file, then saved as excel workbook. in column G starting at cell 87 to 161 are negative numbers. i put in formula =ABS(c87) and dragged down. all return #value!, same if i paste special , multiply -1. i have checked the column is formatted as numbers. what am i doing wrong? i am slowly trying to improve my excel skills and not very learned. any formula that i have tried always gives me a #value! error. perhaps i am making the same fundamental mistake every time. please give me the basics.

 

[Kaper]

Even if it is formatted as numbers, it may contain text which only look like numbers.
make a column a bit wider and observe if the content is left (text) or right(numbers) justified (if you have not applied centering or justing yourself).
As the data is imported from CSV it can be for instance followed (or proceeded) by a space or non-dividing space (character code 160, while standard space code is 32).
If it's standard space you may get rid of it by TRIM(c87).
and make soure it's number by for instance double negation --TRIM(c87)
but if it's non-dividing one then substitute character 160 with empty text. So you may tray all in one formula:

=ABS(--TRIM(SUBSTITUTE(C87;CHAR(160);"")))

or revise your csv import process

 

[Kaper]

It's too late to edit, so Please Read this:
I've not changed my local parametres separator (semicolon), while in in many countries (i'd say: most often) it is comma .
So probably also in your case the formula shall read:

=ABS(--TRIM(SUBSTITUTE(C87,CHAR(160),"")))

 

[zoem]

Even if it is formatted as numbers, it may contain text which only look like numbers.
make a column a bit wider and observe if the content is left (text) or right(numbers) justified (if you have not applied centering or justing yourself).
As the data is imported from CSV it can be for instance followed (or proceeded) by a space or non-dividing space (character code 160, while standard space code is 32).
If it's standard space you may get rid of it by TRIM(c87).
and make soure it's number by for instance double negation --TRIM(c87)
but if it's non-dividing one then substitute character 160 with empty text. So you may tray all in one formula:

=ABS(--TRIM(SUBSTITUTE(C87;CHAR(160);"")))

or revise your csv import process

I had already tried that and i checked the cell nothing there it is "clean" for want of a better word. Sorry its actually G87, but i changed it in formula. i struggle with any formula, i put in formulas exactly as given and always get an error. what may be the common mistakes i make or common issues i may need to check first?

 

[zoem]

It's too late to edit, so Please Read this:
I've not changed my local parametres separator (semicolon), while in in many countries (i'd say: most often) it is comma .
So probably also in your case the formula shall read:

=ABS(--TRIM(SUBSTITUTE(C87,CHAR(160),"")))

thanks, i pasted that exactly and also got an error. Even when correcting c87 to g87. i just struggle with formulas. i copy as given and always get errors so very frustrating.

 

[zoem]

thanks, i pasted that exactly and also got an error. Even when correcting c87 to g87. i just struggle with formulas. i copy as given and always get errors so very frustrating.

i got it! i changed the original number to be a comma, not a point and the formula worked! thats painful. now to change lines of data to have comma not point!!

 

[zoem]

thanks, i pasted that exactly and also got an error. Even when correcting c87 to g87. i just struggle with formulas. i copy as given and always get errors so very frustrating.

inadvertently i fixed the problem! find and replace > with comma, and it all now working!! thank you.