Excel 2003

I would like to have formula in column C where it should show "1" for the largest amount for each ID and then 2 for the second largest amount for each ID and then 3 for the 3rd largest amount for each ID. For exameple for ID 110 it should 1 for 25 amount and 2 for 23 amount. Please look at the attach sample. thanks
Sample.xls
snhandleAsked:
Who is Participating?
 
nutschCommented:
Try this formula in cell C2, copy down

=SUMPRODUCT(--(A$2:A$10=A2),--(B2< B$2:B$10))+1

Thomas
0
 
dlmilleCommented:
Put this in C2 and copy down:

=SUMPRODUCT(--(B2>$B$2:$B$1000)*($A$2:$A$1000=A2))+1

Increase the 1000 to the max your range will be

see attached:

Dave
ranking-sample-r1.xls
0
 
dlmilleCommented:
you beat me, Thomas...

Dave
0
 
dlmilleCommented:
and I ranked backwards.

My formula should be

=SUMPRODUCT(--(B2<$B$2:$B$1000)*($A$2:$A$1000=A2))+1

lol

Dave
0
 
snhandleAuthor Commented:
Good job
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.