Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / February 2008

Tip: Looking for answers? Try searching our database.

Indicating the first occurance of the max value in a range

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Aline Yiu - 06 Feb 2008 03:40 GMT
What to do to indicate the first highest score for each person?

Here's the example:

ID    score    score used
a    1
a    2    2
b    3
b    2
b    1
b    4    4
b              4
c    2    2
d    2    2
d    2
e    1    1
e    1

I have a formula (thanks to the Excel expert) that indicates all the max
values in a range: =IF(MAX(IF($A$2:$A13=A2, $B$2:$B13)), B2, " "),  but I'd
like to have it to show right away the first occurance of the max value.

Thank you for your help.
Signature

Aline Yiu

Max - 06 Feb 2008 04:00 GMT
One way

With data assumed in A2:B13

Put in C2 ("Score used" col), then array-enter the formula
by pressing CTRL+SHIFT+ENTER:
=IF(AND(MAX(IF($A$2:$A$13=A2,$B$2:$B$13))=B2,SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))<2),B2,"")
Copy C2 down to C13
Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> What to do to indicate the first highest score for each person?
>
[quoted text clipped - 20 lines]
>
> Thank you for your help.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.