CODEWITHSUNDEEP
excel-formula
Stoepsteen
Stoepsteen

Reputation: 21

Combining multiple IF-statements into one formula

I am constructing a formula to act as an extra 'control cell' for my excel worksheet.

The if statements in text with the expected result:

If D2=0 and E2=0 and F2=0 =>""

If D2=0 and E2>0 and F2=0 =>"m of m2"

If D2>0 and E2>0 and F2=0 =>"m2 of m3"

If D2>0 and E2>0 and F2>0 =>"m3"

If D2>0 and E2=0 and F2>0 =>"m2"

If D2=0 and E2>0 and F2>0 =>"m2 of m3"

If D2=0 and E2=0 and F2>0 =>"m"

If D2>0 and E2=0 and F2=0 =>"m"

I'll converting this formula to vba afterwards, but my knowledge of vba is pretty limited so I like to start with just the excel formula.

Thanks in advance.

*edit: so far the formula always returns "m3" so it acts like all the cells are >0 even if they are empty/have a 0 value.

Formula so far (it's in dutch so als=if)

=ALS(D3=0&E3=0&F3=0;"";ALS(D3=0&E3>0&F3=0;"m of m2";ALS(D3>0&E3>0&F3=0;"m2 of m3";ALS(D3>0&E3>0&F3>0;"m3";ALS(D3>0&E3=0&F3>0;"m2";ALS(D3=0&E3>0&F3>0;"m2 of m3";ALS(D3=0&E3=0&F3>0;"m";ALS(D3>0&E3=0&F3=0;"m";""))))))))```

Upvotes: 0

Views: 138

Answers (1)

Forward Ed
Forward Ed

Reputation: 9874

One way to interpret your table of results is that the value is equal to 0 or its not. your table does not cover the possibility of of values being less than 0. With this understanding one possible NESTED IF function would be:

=IF(D2=0,IF(E2=0,IF(F2=0,"","m"),IF(F2=0,"m of m2","m2 of m3")),IF(E2=0,IF(F2=0,"m","m2"),IF(F2=0,"m2 of m3","m3")))

Alternatively in excel you could use the CHOOSE function. Since each result is unique and its based based on binary results you could use the following formula to generate an index number from 1 to 8:

1+(F2>0)+(E2>0)*2+(D2>0)*4

Drop that in a CHOOSE function and its much more shorter manageable then nested IF. It could look as follows:

=CHOOSE(1+(F2>0)+(E2>0)*2+(D2>0)*4,"","m","m of m2","m2 of m3","m","m2","m2 of m3","m3")

POC

now not being a VBA guru either, I am not sure how CHOOSE would translate over to VBA. But that would be another question!

UPDATE: ALTERNATE IF function

=IF(AND(D2=0,E2=0,F2=0),"",IF(AND(E2=0,D2<>F2),"m",IF(AND(D2=0,E2>0,F2=0),"m of m2",IF(AND(E2>0,D2<>F2),"m2 of m3",IF(AND(D2>0,E2=0,F2>0),"m2","m3")))))

There are many ways to go through the logic. In this case I was able to group the IF functions by results.

Upvotes: 1

Related Questions