Solved

Excel 2003

Posted on 2011-03-21
5
176 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
[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
  • Learn & ask questions
  • 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 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

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,…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
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 demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

742 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