Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Excel 2003

Posted on 2011-03-21
Medium Priority
187 Views
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
Question by:snhandle
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3

LVL 39

Accepted Solution

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

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

ID: 35185729
you beat me, Thomas...

Dave
0

LVL 42

Expert Comment

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

ID: 35195532
Good job
0

## Featured Post

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
###### Suggested Courses
Course of the Month8 days, 9 hours left to enroll