Subtracting Negative Numbers
 

I've looked at other similar questions and responses (# VALUE), etc, and
tried the suggestions (N(C2)-N(D2), ABS, Text to columns, =C2-D2, and still
get the #VALUE. Some of the numbers in C2 are negative, but even two
positive numbers in C2 and D2 result in the # VALUE response.

The cells are formatted as numbers. Any ideas?

Carole O

If the cells were originally text, and you changed format, then the value in
that cell is still text (that looks like numbers).

Try formatting as General (already done)
then re-entering the value (F2 followed by an enter key should be sufficient)

If that still fails, try retyping the value. If you've pasted your values from
a web page, you may have gotten extra characters in the cell Spaces or HTML
non-breaking spaces.

David McRitchie has a macro to clean this kind of stuff:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

And if you're new to macros, you may want to read David's intro:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Carole O wrote:

I've looked at other similar questions and responses (# VALUE), etc, and
tried the suggestions (N(C2)-N(D2), ABS, Text to columns, =C2-D2, and still
get the #VALUE. Some of the numbers in C2 are negative, but even two
positive numbers in C2 and D2 result in the # VALUE response.

The cells are formatted as numbers. Any ideas?

Carole O

--

Dave Peterson

Thanks, Dave. I wasn't told by the person who presented me with this problem
that he had downloaded it from the company intranet (thus the text). I
checked out David's site, and found the macro which cleared this up. I can
also use it on other similar situations (downloading from Great Plains
software), so you have helped me in more ways than one!

Thanks,
Carole
"Dave Peterson" wrote:

If the cells were originally text, and you changed format, then the value in
that cell is still text (that looks like numbers).

Try formatting as General (already done)
then re-entering the value (F2 followed by an enter key should be sufficient)

If that still fails, try retyping the value. If you've pasted your values from
a web page, you may have gotten extra characters in the cell Spaces or HTML
non-breaking spaces.

David McRitchie has a macro to clean this kind of stuff:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

And if you're new to macros, you may want to read David's intro:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Carole O wrote:

I've looked at other similar questions and responses (# VALUE), etc, and
tried the suggestions (N(C2)-N(D2), ABS, Text to columns, =C2-D2, and still
get the #VALUE. Some of the numbers in C2 are negative, but even two
positive numbers in C2 and D2 result in the # VALUE response.

The cells are formatted as numbers. Any ideas?

Carole O

--

Dave Peterson