Nested IF statements using my current formula?

[qrtback10]

Hi guys! Marcelo Branco gave an excellent answer to another user on here at this thread of which I am using the formula. However, I need to take it one step further: I need to nest two IF statements to my current formula.

[COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]INDEX[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]$O6:$O30[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]MAX[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]INDEX[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#7E3794][FONT=Inconsolata]H6:L30[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]MAX[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#7E3794][FONT=Inconsolata]H6:L30[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]*[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]ROW[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#7E3794][FONT=Inconsolata]H6:L30[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]-[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]ROW[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#11A9CC][FONT=Inconsolata]H6[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]+[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]1[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR]

Need to add the following criteria:

• IF P6:P30="AA"
• IF H6:L30<H2

How do I add those in there without screwing the whole thing up? Or is there a better alternative? Please advise!

Thanks.

 

[zergcow]

That is sort of confusing ... If statements need a thing that evaluates out to either true or false. so when you say "IF H6:L30", that makes no sense. Here is a basic IF statement though...

If(A1="AA","This happens if it is true","This happens if it is false")

You can also use an AND or an OR statement to combine multiple things. Like this:

IF(AND(A1="AA",A2="AA"),"Both are True","One is False")

If you want to check if more than one cell in P6:P30 = "AA" then maybe try COUNTIF()... In the following example if it finds AA in that range it does your formula. Otherwise it tells you it was false.

=IF(COUNTIF(P6:P30,"AA")>0,INDEX($O6:$O30,MAX(INDEX((H6:L30=MAX(H6:L30))*(ROW(H6:L30)-ROW(H6)+1),))),"It was false")

 

[qrtback10]

Hi Zergcow thanks for replying. You're right, that doesn't make any sense. What I meant I need to add is:

<h2,p630="aa")
<h2,p630="aa")[ code]

IF(AND(H6:L30<H2,P6:P30="AA")

Hopefully that makes more sense.</h2,p630="aa")[></h2,p630="aa")

 

[qrtback10]

Somehow i'm not able to add what i want?? I keep typing it in but when i post it, it shows up like it is. Weird.

I need "if and h6:l30 is less than h2 and if p630 equals "aa"" ---> guess i'll just type it out lol

 

[zergcow]

When you say less than h2 do you mean the sum of h6:L30 or the largest value in that range like this:

Sum<h2
=IF(AND(SUM(H6:L30)<H2,COUNTIF(P6:P30,"AA")>0),INDEX($O6:$O30,MAX(INDEX((H6:L30=MAX(H6:L30))*(ROW(H6:L30)-ROW(H6)+1),))),"It was false")

max value<h2
=IF(AND(MAX(H6:L30)<H2,COUNTIF(P6:P30,"AA")>0),INDEX($O6:$O30,MAX(INDEX((H6:L30=MAX(H6:L30))*(ROW(H6:L30)-ROW(H6)+1),))),"It was false")