Comparing numbers, ignoring sign

Hi,

I have a very long list laid out in the following manner,

Item 1~~Item 2~~Difference
3.5~~~~3.5~~~~0
4.2~~~~3.7~~~~0.5
-5~~~~-5~~~~~0

I need to see if the difference is more than 1% of the original value in list 1.
I can work this out for the positive numbers, but when I copy the formula through cells it doesnt work on negative numbers.

=(A2038*0.01)>=(C2038)

Any ideas on how I can ignore the sign of the numbers in order to ensure I get correct results?

Thanks in advance.

Try this..

=(ABS(B15*0.01)>=ABS(D15))
=ABS function ignores the signs of numbers

Originally Posted by steev_jd

Hi,

I have a very long list laid out in the following manner,

Item 1~~Item 2~~Difference
3.5~~~~3.5~~~~0
4.2~~~~3.7~~~~0.5
-5~~~~-5~~~~~0

I need to see if the difference is more than 1% of the original value in list 1.
I can work this out for the positive numbers, but when I copy the formula through cells it doesnt work on negative numbers.

=(A2038*0.01)>=(C2038)

Any ideas on how I can ignore the sign of the numbers in order to ensure I get correct results?

Thanks in advance.

Hi,

You could use absolute numbers:

=(ABS(A2038)*0.01)>=ABS(C2038)

Regards,
Bondi

=(A2038*0.01)>=ABS(C2038)

--
Kind regards,

Niek Otten

"steev_jd" <[email protected]> wrote in message
news:[email protected]...
|
| Hi,
|
| I have a very long list laid out in the following manner,
|
| Item 1~~Item 2~~Difference
| 3.5~~~~3.5~~~~0
| 4.2~~~~3.7~~~~0.5
| -5~~~~-5~~~~~0
|
| I need to see if the difference is more than 1% of the original value
| in list 1.
| I can work this out for the positive numbers, but when I copy the
| formula through cells it doesnt work on negative numbers.
|
| =(A2038*0.01)>=(C2038)
|
| Any ideas on how I can ignore the sign of the numbers in order to
| ensure I get correct results?
|
| Thanks in advance.
|
|
| --
| steev_jd
| ------------------------------------------------------------------------
| steev_jd's Profile: http://www.excelforum.com/member.php...o&userid=33107
| View this thread: http://www.excelforum.com/showthread...hreadid=533659
|