need formula to get the highest rating for the specific patient.

[breddi76]

Hi,

I have a data like below. i have patient number n scar numbers for each patient. some patients may have 4 scars or few pts may have 10 scars. i need a formula to get output such as patient number with maximum no of scars. ex: if pt 02-001 has 8 scars, 02-002 has 5 scars n 02-003 has 3 scars in the below table. i need to get output where the pt# 02-001 has max no of scars 8. i don't want 1,2,3 etc scar no s in the output. i want only highest scar number.


any help would be highly appreciated.


PT# scar#

02-001 1

02-001 2

02-001 3

02-001 4

02-001 5

02-001 6

02-001 7

02-001 8

02-002 1

02-002 2

02-002 3

02-002 4

02-002 5

02-003 1

02-003 2

02-004 1

02-004 2

02-004 3

02-004 4

02-004 5

02-004 6

04-001 1

04-001 2

04-001 3

04-001 4

04-001 5

04-001 6


thanks in advance.

 

[Debraj]

Hi Breddi76,


Can you please try the below formula in Column C.. and drag the same..

=IF(COUNTIF($A$2:$A$28,A2)=COUNTIF($A$2:A2,A2),COUNTIF($A$2:$A$28,A2),"")

Regards,

Deb

 

[Hui]

Breddi76


I read the question differently


The maximum Scar# value is

=Max(B2:B28)

The PT# that corresponds is [code]=INDEX(A2:A28,MATCH(MAX(B2:B28),B2:B28,0))


If you want to know the specific max for a specific patient you cann use:

=MAX(IF(A2:A28="02-001",B2:B28,))

or

=MAX(IF(A2:A28=C1,B2:B28,))[/code]

where C1 has the patient ID 02-001 etc

 

[breddi76]

Thank you very much Debraj Roy. Your formula worked out for me. I really appreciate your reply.. thanks a ton.

 

[breddi76]

Dear Hui,

Thanks a lot for your effort to resolve my issue. this formula =MAX(IF(A2:A28="02-001",B2:B28,)) worked when i check for single patient. if i drag it does not work for other patients. i need to change pt number for every patient.


thanks again..


have a good weekend..