Solved

Excel Ranking within a group

Posted on 2013-01-11
5
304 Views
Last Modified: 2013-01-11
Working on excel :

Segment     Gp               Result
2                 Asia                   2
5                 Asia                   2
3                 Asia                   2
6                Regional            1
1                Regional            1

Third result column is based on the result of first two columns:
- For same gp, detect the min value and show

How to do? thanks!
0
Comment
Question by:hkgal
  • 3
  • 2
5 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 38767476
Do you mean this?

Eg.  If your data is in A2:B6, try in C2:

=SUMPRODUCT(--($B$2:$B$6=B2),--(A2>=$A$2:$A$6))

or

=COUNTIFS($B$2:$B$6,B2,$A$2:$A$6,"<="&A2)

copied down.
0
 

Author Comment

by:hkgal
ID: 38767557
I am using Excel 2003 that without a countifs function?

the first sentense return:
1
3
2
2
1

seems not correct...
0
 

Author Comment

by:hkgal
ID: 38767585
e.g.Min for "Asia" is 2, so in column C should be all 2 for Asia
0
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 38767610
so you mean...

=MIN(IF($B$2:B$6=$B2,A$2:A$6))

confirmed with CTRL+SHIFT+ENTER, not just ENTER and copied down.
0
 

Author Closing Comment

by:hkgal
ID: 38767618
Thanks!
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

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…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

809 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