Rank If

it won't work because your array's are trying to produce a single result. whilst the rank function needs an actual array to rank against, not just a single return.

have you checked out cpearson's ranking solutions yet?

edit: http://www.cpearson.com/excel/rank.htm

No sorting requirement implicates pretty expensive formulas...

The formula in C3 is:

=MATCH(B3,LARGE(IF($A$3:$A$11=A3,$B$3:$B$11),ROW(INDIRECT("1:"&COUNTIF($A$3:$A$11,A3)))),0)

which must be confirmed with control+shift+enter instead of just with enter.

Thanks for the solution.

Perhaps not as expensive--

The formula in C3 is:

=SUMPRODUCT(--(A$3:A$11=A3)*(B3< B$3:B$11))+1

I included a second set of data ("March") and copied this formula to column G. Aladin's formula is in column H.

--Tom

rrdonutz said:

Perhaps not as expensive--...

The formula in C3 is:

=SUMPRODUCT(--(A$3:A$11=A3)*(B3< B$3:B$11))+1

I included a second set of data ("March") and copied this formula to column G. Aladin's formula is in column H.

--Tom

Click to expand...

Watch out for -- and * together in the same formula...

=SUMPRODUCT(--(A$3:A$11=A3),--(B3< B$3:B$11))+1

It will be certainly less expensive than the MATCH formula with INDIRECT. With lots of data (huge ranges), sorting allows for performance formulas.

Originally posted by Aladin Akyurek:
Watch out for -- and * together in the same formula...

=SUMPRODUCT(--(A$3:A$11=A3),--(B3< B$3:B$11))+1
. . .

Click to expand...

Aladin, what is the potential problem/conflict? My apologies, as I'm sure you've probably answered this before . . .

Regards,

Tom

rrdonutz said:

Originally posted by Aladin Akyurek:
Watch out for -- and * together in the same formula...

=SUMPRODUCT(--(A$3:A$11=A3),--(B3< B$3:B$11))+1
. . .

Click to expand...

Aladin, what is the potential problem/conflict? My apologies, as I'm sure you've probably answered this before . . .

Regards,

Tom

Click to expand...

Doubles coercion maybe. More importantly, it would dispose the onlooker to believe that Tom fails to understand coercion. We would want to exclude such, don't we?

Well there's a lot I'd like to exclude, but probably the first would be my income from the avaricious bite of the tax man , having just given a large chunk (April 15) as well a couple of hunks of my backside to said predator.

I may be the person Seti originally posted this for. I already posted 2) in another thread, but here are both questions again.


At the moment, I have two issues:

1) The Rank IF: I need to rank people by production numbers within their position (e.g. processor, closer, underwriter, etc.), but some of the names of the employees (in another column) need to be excluded (Other and Unknown). What I did to the production numbers was to say =If(Name<>"Uknown",IF(Name<>"Other",Use Total,"")) and then use the complicated ranking forumula shown before (with Large, Indirect and Match or whatever it was). This seemed to work the first time (before my specs changed) - since I didn't have any errors in the two sample exhibits I made. It would be nice to not have to make the "Modified production" column and just include the conditions in the formula (to exlcude them from that ranking).

2) Count IF and IF. Basically, count the # of employees per position excluding the "Unknown" and "Others".

Thanks!