Calculating a number's rank across multiple series

[miinstrel]

Hello hive mind,

Appreciate any assistance you can provide with this formula that's stumping me. In column B I'm trying to calculate how often the row's entry in columns D:K is the HIGHEST number of that series.
E.g. B4 should show 50% since the 130 in D4 is (tied for) the highest but the 141 in H4 is not.

Additionally, I'm trying to calculate the average rank (1.00 - 4.00) of each item across all series in which it is present.
E.g. C4 should show 1.50 since D4 is the highest (1.00) in column D and H4 is 2nd highest (2.00) in column H. (1+2)/2 = 1.5

Thanks for your time.

 

[hagia_sofia]

Hello, I am not sure if this is going to be of any use since it is a 365 attempt (not sure if you have it):

=LET(
array,D3:K20,
total,BYROW(array,LAMBDA(a,COUNTA(a))),
max,BYCOL(array,MAX),
count,BYROW(--(array=max),LAMBDA(a,SUM(a))),
percent,count/total,
rank,IFNA(DROP(REDUCE("",SEQUENCE(COLUMNS(array)),LAMBDA(a,b,HSTACK(a,RANK(INDEX(array,0,b),INDEX(array,0,b))))),,1),""),
average,BYROW(rank,LAMBDA(a,AVERAGE(a))),
HSTACK(percent,average))

 

[miinstrel]

Hello, I am not sure if this is going to be of any use since it is a 365 attempt (not sure if you have it):

=LET(
array,D3:K20,
total,BYROW(array,LAMBDA(a,COUNTA(a))),
max,BYCOL(array,MAX),
count,BYROW(--(array=max),LAMBDA(a,SUM(a))),
percent,count/total,
rank,IFNA(DROP(REDUCE("",SEQUENCE(COLUMNS(array)),LAMBDA(a,b,HSTACK(a,RANK(INDEX(array,0,b),INDEX(array,0,b))))),,1),""),
average,BYROW(rank,LAMBDA(a,AVERAGE(a))),
HSTACK(percent,average))

hey, thx for the speedy reply. I'm running Office 2021 and do not have 365. so unfortunately no, this doesn't work :/
But these fancy shmancy 365 functions look awesome... i might need to consider an upgrade at some point lol

 

[Fluff]

I'm running Office 2021

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[miinstrel]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Great idea, this place has made some nice improvements since I was last here

 

[miinstrel]

Just a quick bump. Thanks y'all

 

[Fluff]

Simplest way is with a helper row, in D21 dragged across

=MAX(D3:D20)

and then in B3 dragged down

=SUM(COUNTIFS(D3:K3,$D$21:$K$21))/COUNT(D3:K3)

 

[miinstrel]

Simplest way is with a helper row, in D21 dragged across

=MAX(D3:D20)

and then in B3 dragged down

=SUM(COUNTIFS(D3:K3,$D$21:$K$21))/COUNT(D3:K3)

it's close, but not quite a winner. this checks each row against ANY highest value in row 21. Any match is counted, not just for the specific series/column in which that value appears.

 

[Fluff]

How about

=SUM((D3:K3=$D$21:$K$21)*(D3:K3<>""))/COUNT(D3:K3)

 

[miinstrel]

How about

=SUM((D3:K3=$D$21:$K$21)*(D3:K3<>""))/COUNT(D3:K3)

That did it! Awesome, thank you.

Any thoughts on the second part? Avg rank across all series