Nested IF w/ False

[gtd526]

Hello,
Im trying to get rid of the False statement from the formula. I think its because of the EXACT statement, if they are great, if not, FALSE? One of the nested IFs has to be true.
Can use 1 Exact up front instead of for every IF?
The Nested IF's are due to (+) and (-) numbers.
thanks.

NBA.xlsm
	V
5	FALSE
6	13.7
Favs

Cell Formulas
Range	Formula
V5:V6	V5	=IF(EXACT(A5,AA5),IF(AND(B5>0,O5>0),O5-B5,IF(EXACT(A5,AA5),IF(AND(B5<0,O5<0),ABS(O5)-ABS(B5),IF(EXACT(A5,AA5),IF(AND(B5<0,O5>0),O5+ABS(B5),IF(EXACT(A5,AA5),IF(AND(B5>0,O5<0),ABS(O5)+B5,""))))))))

 

[jasonb75]

Can use 1 Exact up front instead of for every IF?

You only need one, it is probably the repetition that is causing the problem.

=IF(EXACT(A5,AA5),IF(AND(B5>0,O5>0),O5-B5,IF(AND(B5<0,O5<0),ABS(O5)-ABS(B5),IF(AND(B5<0,O5>0),O5+ABS(B5),IF(AND(B5>0,O5<0),ABS(O5)+B5,"")))),"")

 

[gtd526]

You only need one, it is probably the repetition that is causing the problem.

=IF(EXACT(A5,AA5),IF(AND(B5>0,O5>0),O5-B5,IF(AND(B5<0,O5<0),ABS(O5)-ABS(B5),IF(AND(B5<0,O5>0),O5+ABS(B5),IF(AND(B5>0,O5<0),ABS(O5)+B5,"")))),"")

Perfect. Thank you.

 

[jasonb75]

You're welcome

As far as I can see, you can use a much shorter formula, checking if each value is < or > than 0 then making them absolute is not necessary, it can all be done in one step. I've done a quick test with 4 pairs of values (1 pair for each of the criteria in your formula) and the results for this method are the same.

=IF(AND(EXACT(A5,AA5),B5<>0,O5<>0),ABS(O5-B5),"")

If you don't need to eliminate possible zero values / empty cells then you could shorten it even more

=IF(EXACT(A5,AA5),ABS(O5-B5),"")

Questions with minimal information often lead to answers instead of solutions.