Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel 2003

Posted on 2011-03-21
5
Medium Priority
?
188 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 2000 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 42

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 42

Expert Comment

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

Dave
0
 
LVL 42

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

579 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