Rank if

Dan71

New Member
Joined
Jul 31, 2006
Messages
48
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
don't manually type the brackets...

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

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
=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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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

New Member
Joined
Jul 31, 2006
Messages
48
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
 

Forum statistics

Threads
1,181,361
Messages
5,929,531
Members
436,676
Latest member
Mavri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top