Solved

Excel 2003

Posted on 2011-03-21
5
172 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:snhandle
  • 3
5 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 500 total points
ID: 35185717
Try this formula in cell C2, copy down

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

Thomas
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35185725
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
 
LVL 41

Expert Comment

by:dlmille
ID: 35185729
you beat me, Thomas...

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35185739
and I ranked backwards.

My formula should be

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

lol

Dave
0
 

Author Closing Comment

by:snhandle
ID: 35195532
Good job
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now