Excel help with calculation.

[Bleek]

I need some assistance.

I have the following equation -

= IF(J2<K2, (K2-J2), (J2-K2) )

Which is fine, it forces the result to be a positive regardless of which order the numbers fall in the adjacent cell i.e. forces the smaller number to be subtracted from the larger number.

However I need the exact opposite to occur if I place the word 'negative' in an adjacent cell, so the above equation first checks for 'negative' and then does the reverse of the above.

check for the word 'Negative' if it's there then do= IF(J2<K2, (K2-J2), (J2-K2) ) otherwise do = IF(J2<K2, (K2-J2), (J2-K2) )

Is that possible? If it makes it simpler you could have it check for the word 'positive' as well.

 

[SpaghettiOs]

Yeah, just nest them within each other. Eg:

IF(I2="Negative", IF(J2<K2, (K2-J2), (J2-K2)),IF(J2<K2, (K2-J2), (J2-K2) ))

Assuming I2 is where you have the word negative written.

 

[Bleek]

Ah! Will try that, thank you.

 

[RastaManBob]

Above is correct, though I'd use the absolute (ABS()) function ("returns the absolute value of a number, a number without its sign"). It saves the nested if, though that is still perfectly fine.

For example:
=IF(I2="Negative", -ABS(J2-K2), ABS(J2-K2))

Assuming the same cell locations as used above.