Solved

Excel Ranking within a group

Posted on 2013-01-11
5
275 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

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,…
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 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

7 Experts available now in Live!

Get 1:1 Help Now