Rank if

[Dan71]

I was tring to remember how to do a "rank if". Finaly remembered how to do it using;

=sumproduct(--(crittera 1),--(critera 2),(sale<sales))+1

This works all well and good, but I have a couple of quetions.

Firstly this works as with highest as 1 and downwards, is it posible to make it do it the other way round.

Secondly, I tried firstly to acheve the same result using an array forula ie
{=rank(if(critera,sale,sales))} but could not get it to work. i did try a few diferent combinations of brackets etc. Is it posible to do it this way?

Thanks
Dan

 

[Jonmo1]

don't manually type the brackets...

after typing the formula, press CTRL + SHIFT + ENTER. That put's the brackets around the formula.

 

[barry houdini]

=sumproduct(--(crittera 1),--(critera 2),(sale<sales))+1

This works all well and good, but I have a couple of quetions.

Firstly this works as with highest as 1 and downwards, is it posible to make it do it the other way round.

just swap the < for a > i.e.

=sumproduct(--(criteria 1),--(criteria 2),--(sale>sales))+1

 

[Aladin Akyurek]

I was tring to remember how to do a "rank if". Finaly remembered how to do it using;

=sumproduct(--(crittera 1),--(critera 2),(sale < sales))+1

This works all well and good, but I have a couple of quetions.

Firstly this works as with highest as 1 and downwards, is it posible to make it do it the other way round.

Secondly, I tried firstly to acheve the same result using an array forula ie
{=rank(if(critera,sale,sales))} but could not get it to work. i did try a few diferent combinations of brackets etc. Is it posible to do it this way?

Thanks
Dan

=SUMPRODUCT(--(RangeX=X),--(RangeY=Y),--(RankRange > Z))+1

 

[Dan71]

Thanks for your help, Barry, that was spot on.

The part about the array formula was more for interest realy, I know how to use them ie use ctrl+shift+enter. I realy want to know if is posible to do a rank if in this way. I suppose the same would aplly to a "vlookup if" too. I see how "average ifs" etc work, it is formula with multiple parts to them like a vlookup, rank, etc ( in effect excell's built in array formulie).

Dan