COMPLEX Nested IF statement that uses AND, OR's and other conditions

**concatch** · 10-25-2011, 04:02 AM

Hi,

Here is my statement to date which returns FALSE.

=IF(AND(ISBLANK(Q16),ISBLANK(O16),ISBLANK(S16),ISBLANK(M16)),0,IF(OR(ISBLANK(M16),ISBLANK(O16),(Q16*S16)<100),IF(OR(ISBLANK(Q16),ISBLANK(S16),(M16*O16))<100,IF((+(O16*M16)+(Q16*S16))<100,100,+(O16*M16)+(Q16*S16)))))

I have attached the excel spreadsheet.

Basically the answer in U6 should be this: =IF((+(O6*M6)+(Q6*S6))<100,100,+(O6*M6)+(Q6*S6)).

BUT I now need to account for if the cells O,M,Q,S are blank or if other combinations are blank. For instance if O6 is blank OR M6 is blank it needs to then perform the calculation (Q6*S6) and if this is <100 then it must return 100, if not then it must return the answer. But it also needs to do a check on Q and S much like the way I have just described above.

If you follow my nested if statement you should get the gist of what I'm trying to achieve. I think the problem comes in when some of the statement are true.

Thank-you very much!!!

**nilem** · 10-25-2011, 06:21 AM

A very long formula

. Try it.

Please Login or Register  to view this content.

**concatch** · 10-25-2011, 07:45 AM

Hi thnax for trying but it breaks if you input 0 in the cells. What I came up with was the following:

=IF(AND(ISBLANK(Q6),ISBLANK(O6),ISBLANK(S6)),0,IF(AND(M6*O6=0,Q6*S6=0),0,IF((+(O6*M6)+(Q6*S6))<100,100,+(O6*M6)+(Q6*S6))))

I think it covers most of the cases without breaking. If you can find a case where it breaks and fix it I would really appreciate that. Thanx!!

**TMS** · 10-25-2011, 07:55 AM

Maybe:

=IF(((MAX(O6,0)*MAX(M6,0))+(Q6*S6))<100,100,(MAX(O6,0)*MAX(M6,0))+(Q6*S6))

Regards

**nilem** · 10-25-2011, 08:14 AM

concatch, and if so:

Please Login or Register  to view this content.

**concatch** · 10-25-2011, 10:06 AM

Sorry I should have mentioned that the value must remain 0 if there is no data and not default to 100. It must only default to 100 if the calculation using columns M,O,Q,S is < 100

**concatch** · 10-25-2011, 10:22 AM

If I enter 1 for all the columns M,O,Q,S the result returned is 200 which is incorrect. This happens for both your formulas. So far my second formula seems to hold. Thanks though

**TMS** · 10-25-2011, 10:39 AM

Why is it incorrect? 1 isn't blank, which is what you said you needed to adapt your formula to cater for.

Regards

**Whizbang** · 10-25-2011, 10:43 AM

Instead of IF() functions, try this:

=((ISBLANK(Q16)*ISBLANK(O16)*ISBLANK(S16)*ISBLANK(M16))>0) * ((ISBLANK(M16)+ISBLANK(O16)+((Q16* S16)<100))>0) * (((ISBLANK(Q16)+ISBLANK(S16)+((M16*O16)<100))>0) * (((O16*M16)+(Q16*S16))<100)*100 + (((O16*M16)+(Q16*S16))<100)*((O16*M16 )+(Q16*S16))

The above formula goes on the assumption that IF() functions are precious and should not be wasted. By changing your IF() tree into straight math and taking advantage of Excel's behavior of coercing TRUE/FALSE values to 1/0 when in a math equation, we can eliminate the nested IF() limit of 7, speed up calculation, and add complexity.

See this article that explains how many uses of IF() can be adapted to other functions to get the same result, but faster and easier to read.

**Whizbang** · 10-25-2011, 11:20 AM

Oops. Forgot to paste the link to the article.

http://www.excelhero.com/blog/2010/01/i-heart-if.html

**concatch** · 10-26-2011, 03:50 AM

@TMShucks your formula works fine but the value doesn't default to 0 if there are no values inputed.
@Whizbang, that article is very useful, I learnt a lot thank-you!

**TMS** · 10-26-2011, 03:24 PM

Your requirement seems to be changing. You originally said:

For instance if O6 is blank OR M6 is blank it needs to then perform the calculation (Q6*S6) and if this is <100 then it must return 100, if not then it must return the answer.

I have to admit that I didn't pick up on the second part but the same approach could be applied.

It won't default to zero if no values are input. That will result in a value which is less than 100 and will apply the true part of the formula which will return 100.

Regards

COMPLEX Nested IF statement that uses AND, OR's and other conditions

LinkBack

Thread Tools

Rate This Thread

Display

COMPLEX Nested IF statement that uses AND, OR's and other conditions

Re: COMPLEX Nested IF statement that uses AND, OR's and other conditions

Re: COMPLEX Nested IF statement that uses AND, OR's and other conditions

Re: COMPLEX Nested IF statement that uses AND, OR's and other conditions

Re: COMPLEX Nested IF statement that uses AND, OR's and other conditions

Re: COMPLEX Nested IF statement that uses AND, OR's and other conditions

Re: COMPLEX Nested IF statement that uses AND, OR's and other conditions

Re: COMPLEX Nested IF statement that uses AND, OR's and other conditions

Re: COMPLEX Nested IF statement that uses AND, OR's and other conditions

Re: COMPLEX Nested IF statement that uses AND, OR's and other conditions

Re: COMPLEX Nested IF statement that uses AND, OR's and other conditions

Re: COMPLEX Nested IF statement that uses AND, OR's and other conditions

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions