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.

 

[Fluff]

You could try it like

=SUM(IF(D4="",0,RANK(D4,$D$3:$D$20)),IF(E4="",0,RANK(E4,$E$3:$E$20)),IF(F4="",0,RANK(F4,$F$3:$F$20)))/COUNT(D4:K4)

 

[miinstrel]

You could try it like

=SUM(IF(D4="",0,RANK(D4,$D$3:$D$20)),IF(E4="",0,RANK(E4,$E$3:$E$20)),IF(F4="",0,RANK(F4,$F$3:$F$20)))/COUNT(D4:K4)

was hoping for something more automatic as more series will be added to this document over time, but copying that if/rank for each column isn't too tedious. didn't even know 'rank' existed as a function... thank you.

think this is effectively solved.

 

[Fluff]

You're welcome & thanks for the feedback.