Nested IF w/ False

gtd526

Active Member
Joined
Jul 30, 2013
Messages
434
Office Version
  1. 2019
Platform
  1. Windows
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
5FALSE
613.7
Favs
Cell Formulas
RangeFormula
V5:V6V5=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,""))))))))
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,549
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
=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,"")))),"")
 
Solution

gtd526

Active Member
Joined
Jul 30, 2013
Messages
434
Office Version
  1. 2019
Platform
  1. Windows
You only need one, it is probably the repetition that is causing the problem.
Excel Formula:
=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

Well-known Member
Joined
Dec 30, 2008
Messages
13,549
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
=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
Excel Formula:
=IF(EXACT(A5,AA5),ABS(O5-B5),"")
Questions with minimal information often lead to answers instead of solutions.
 

Forum statistics

Threads
1,141,613
Messages
5,707,402
Members
421,508
Latest member
Jalayne

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top