another formula to shorten

[SQUIDD]

Hi guys, again

I have spent now 2 hours trying to shorten this formula, please help.

=
IF(AND(LARGE(AAX2:ABC2,ADG!$K$1)=AAX2,MAX(AAX2:ABC2)>LARGE(AAX2:ABC2,2)+ADG!$K$3,AAX2>ADG!$K$2),1,
IF(AND(LARGE(AAX2:ABC2,ADG!$K$1)=AAY2,MAX(AAX2:ABC2)>LARGE(AAX2:ABC2,2)+ADG!$K$3,AAY2>ADG!$K$2),2,
IF(AND(LARGE(AAX2:ABC2,ADG!$K$1)=AAZ2,MAX(AAX2:ABC2)>LARGE(AAX2:ABC2,2)+ADG!$K$3,AAZ2>ADG!$K$2),3,
IF(AND(LARGE(AAX2:ABC2,ADG!$K$1)=AAA2,MAX(AAX2:ABC2)>LARGE(AAX2:ABC2,2)+ADG!$K$3,ABA2>ADG!$K$2),4,
IF(AND(LARGE(AAX2:ABC2,ADG!$K$1)=ABB2,MAX(AAX2:ABC2)>LARGE(AAX2:ABC2,2)+ADG!$K$3,ABB2>ADG!$K$2),5,
IF(AND(LARGE(AAX2:ABC2,ADG!$K$1)=ABC2,MAX(AAX2:ABC2)>LARGE(AAX2:ABC2,2)+ADG!$K$3,ABC2>ADG!$K$2),6,
""))))))

last one now, thanks.

Dave

 

[wigi]

Dave

First take out that condition in the middle:

MAX(AAX2:ABC2)>LARGE(AAX2:ABC2,2)+ADG!$K$3

it's the same for all 6 cells, so you can separate it from the formula.

 

[ukmikeb]

Dave

Possibly :-

=IFERROR(IF(INDEX(AAX2:ABC2,MATCH(LARGE(AAX2:ABC2,ADG!$K$1),AAX2:ABC2,0))>ADG!$K$2,MATCH(LARGE(AAX2:ABC2,ADG!$K$1),AAX2:ABC2,0)),"")

If not, you have an error in the formula btw :-

Fourth line
IF(AND(LARGE(AAX2:ABC2,ADG!$K$1)=AAA2,MAX(AAX2:ABC2)>LARGE(AAX2:ABC2,2)+ADG!$K$3,ABA2>ADG!$K$2),4,

shouldn't it be
IF(AND(LARGE(AAX2:ABC2,ADG!$K$1)=ABA2,MAX(AAX2:ABC2)>LARGE(AAX2:ABC2,2)+ADG!$K$3,ABA2>ADG!$K$2),4,

hth

 

[ukmikeb]

Dave

The above was based on Wigi's suggestion of removing part of the formula.

This :-

=IFERROR(IF(AND(INDEX(AAX2:ABC2,MATCH(LARGE(AAX2:ABC2,ADG!$K$1),AAX2:ABC2,0))>ADG!$K$2,MAX(AAX2:ABC2)>LARGE(AAX2:ABC2,2)+ADG!$K$3),MATCH(LARGE(AAX2:ABC2,ADG!$K$1),AAX2:ABC2,0)),"")

is with it reinstated.

hth

 

[SQUIDD]

Hi Guys

Thanks for that, well spotted on the mistake in my formula, no wonder my results was not matching, "mine was wrong"

Mike, your last formula is exactly what I wanted. I just need to adjust it so it does not say FALSE if conditions are not met, I need "".
I am sure I can figure this out.

Cheers Guys.

Dave

 

[kvsrinivasamurthy]

This formula exactly replaces the formula.

=IF(MAX(AAX2:ABC2)>LARGE(AAX2:ABC2,2)+ADG!$K$3,SUMPRODUCT(--(LARGE(AAX2:ABC2,ADG!$K$1)=AAX2:ABC2),--(AAX2:ABC2>ADG!$K$2),{1,2,3,4,5,6}),"")

In your original formula red highlighted is wrong, it should be ABA2 insted of AAA2

,ADG!$K$1)=AAA2,MAX(AAX2:ABC2)>LARGE(AAX2:ABC2,2)+ADG!$K$3,ABA2>ADG!$K$2),4,

 

[SQUIDD]

Thanks for that, yes indeed I did have a mistake in my formula.
Your Formula is in-fact much shorter. I like It.
The only strange thing is I get the occasional 0 in my results.
I always get my answer when required, but where i should not get a result, sometimes i get a blank cell and sometimes i get a 0.
Its no big problem, but i would prefer just blank, i will have a play with it.

Cheers

Dave

 

[markmzz]

Another way:

Use Ctrl+Shift+Enter to enter the formula

=IFERROR(MATCH(1,(LARGE(ADG!AAX2:ABC2,$K$1)=ADG!AAX2:ABC2)*(MAX(ADG!AAX2:ABC2)>LARGE(ADG!AAX2:ABC2,2)+$K$3)*(ADG!AAX2:ABC2>$K$2),0),"")

Markmzz