nested if calculation in excel

Question

I have column I as a calulation column and this is what I currently wrote. and this gives me nothing.

=IF(B2<>""&D2<>"",B2*D2,IF(B2<>""&D2=""&C2<>"",B2*C2,IF(A2<>""&C2<>""&AND(B2&D2=""),A2*C2,IF(A2<>""&C2=""&D2<>""&B2="",A2*D2,A2*C2))))

The logic is if B2 and D2 are not null multiply b2*d2

if B2 is not null and D2 null then b2*c2

If B2 is null and D2 is not null then a2*d2

else a2*c2

Is any ways to make this code work?

Thank you

Answer 1

Alternative ways or rewriting your formula:

=IF(AND(B2<>"",D2<>""),B2*D2,IF(D2="",IF(B2<>"",B2*C2,A2*C2),IF(D2<>"",A2*D2,A2*C2)))

=IF(AND(B2<>"",D2<>""),B2*D2,IF(AND(B2="",D2=""),A2*C2,IF(D2="",B2*C2,A2*D2)))

They will make negligible difference in performance and what not. BruceWayne's answer is probably more readable in terms of following your logic and therefore easier to maintain or understand in the future. The above answers should provide the same results but are a few characters shorter in length.

And as a wacky alternative for thinking potentially outside the box:

=CHOOSE(SUM((B2<>"")*2+(D2<>""))+1,A2*C2,A2*D2,B2*C2,B2*D2)

Expanding (not just my waist size)

I had time on my hands so I was fooling around with the concept of TRUE and FALSE being equal to 1 and 0 when sent through a math operation. When I started looking at the options this reminded me of how a binary number works. Not that I have bgiven it too much thought, but I think it works because the options for each cell are binary or TRUE/FALSE. Since every possible combination was covered with a unique out come, I just had to come up with a formula that would produce unique results. In this case I just took the converting a a binary number approach. The key is TRUE = 1 after a math operation and FALSE = 0. Now going the other direction is not quite the same but as Jeeped once put it, 0 is FALSE and everything else is TRUE. so 3, -3, and 3.14 are all treated as TRUE if using the numerical values as the outcome of a logic check.

=IF(3.14,"THIS NUMBER IS TRUE","ONLY 0 IS FALSE")

So less side tracking and back on point (not sure how much I need to expand to!).

Looking at the table above, you will note in the yellow area, all possible combination for BLANK and NOT BLANK are listed. If you then assign a value to the column the same way binary numbers are (note row A) you can then start generating all the possible numbers

I started by generating the list I needed in E2:E5 for numbers that CHOOSE could work with. I assumed 0 would beat up CHOOSE and cause it to fail. I knew that FALSE+FALSE=0 and I also knew that TRUE+TRUE=2 and that both TRUE+FALSE=1 and FALSE+TRUE=1. I needed a way to distinguish the later two and I knew I needed a total of 4 results. That is when binary counting/conversion whatever you want to call it kicked in. I placed the following formula in D2 and copied down

=SUM((A2<>"")*2+(B2<>""))
Note the brackets around the logic check and
that the logic checks are sent through a math
operation before being summed.    

technically speacking it is really:
=SUM((A2<>"")*2+(B2<>"")*1)

however the *1 is not needed

once I had that list generate, it was a simple +1 added to it to get into the 1 to 4 range seen in E2:E5.

Now that I had a way of generating the index number the only thing left to do was to match up the required results/formula with the right combination.

=CHOOSE(SUM((A2<>"")*2+(B2<>""))+1,"A","B","C","D")

Well I felt like I was beating a dead horse there for a bit, so if I over explained, my apologies. If there is something still missing ask for more explination.

UPDATE TID BIT

IF there were more columns to check it may be possible to adjust the choose formula by simply adding the next binary value to the column and making sure there is an appropriate number of results in the choose list. There should be 2^(# of columns to check) options

=CHOOSE(SUM((A2<>"")*4+(A2<>"")*2+(B2<>""))+1,"A","B","C","D","E","F","G","H")

Which kind of beats multiple nested IFs for brevity, but I think I finds the nested IFs easier to understand.

Answer 2

You seem to be mixing operators from other programming languages:
In Excel:

AND : binary operator : AND(TRUE, FALSE) => FALSE
&   : concatenation   : "Hello " & "World" => "Hello World"

Answer 3

You should be using AND():

=IF(AND(B2<>"",D2<>""),B2*D2,IF(AND(B2<>"",D2=""),B2*C2,IF(AND(B2="",D2<>""),A2*D2,A2*C2)))