Me dijeron una forma más sencilla de hacerlo, básicamente:
=JERARQUIA.EQV(Q124;$Q$124:$Q$138;0)+CONTAR.SI($Q$124:Q124;Q124)-1
JERARQUIA.EQV genera una lista ordenada de mayor a menor (si pones el argumento 0) y te da la posición (en la lista) del valor que le digas:
=JERARQUIA.EQV(valor a buscar;rango en donde buscar;modo)
JERARQUIA.EQV es de las "nuevas" fórmulas que ponen para intentar ser más legibles, como desventaja es que solo funciona con valores numéricos, la fórmula madre CONTAR.SI hace exactamente lo mismo con más lógica, estas dos formulas son completamente equivalentes, aparte de que CONTAR.SI funciona con números y texto:
A B C
+-----------------------------
1| JERARQUIA.EQV CONTAR.SI
2| 9 1 1
3| 9 1 1
4| 9 1 1
5| 8 4 4=JERARQUIA.EQV(A2;$A$2:$A$5;0)
=CONTAR.SI($A$2:$A$5;">"&A2)+1
Luego se le suma las repeticiones que se encuentren hasta la fila en cuestión, restando 1 para que su propio valor no se cuenta. Eso hace que los valores repetidos más abajo tengan más repeticiones que los mismos de arriba, haciendo que el resultado sea mayor.
Pero acabo de notar un posible error... si un valor tiene muchos repetidos le suma mucho y entonces puede que quede con una posición mayor a la que debe, pero por lo que veo eso no ocurre, no sé por qué

No hay error porque está usando rangos dinámicos CONTAR.SI($Q$124:Q124;Q124), ves que $Q$124 inmoviliza esa coordenada, pero Q124 no está inmovilizado, por eso al arrastrar la fórmula $Q$124 se mantendrá mientras que Q124 se irá incrementando por cada celda que se arrastre aumentando el rango dinámicamente, el último Q124 es la condición, es decir, cuenta cuantas apariciones del valor último (Q124) hay en el rango dinámico $Q$124:Q124
Teniendo todo lo anterior claro, puedes utilizar:
=CONTAR.SI($A$2:$A$5;">"&A2)+CONTAR.SI($A$2:A2;A2)
---
Otra opción por la que podrías optar es usar Macros, siempre hay gente purista que lo quiere hacer todo con fórmulas madre, otros con fórmulas matriciales, otros todo con tablas dinámicas, otros todo con macros, etc, es una locura XD, pero es bueno conocer un poco de todo.

Con darle click derecho al nombre de la hoja, Ver código abres el editor de macros, arriba tienes el ámbito (General y Worksheet), se elige Worksheet que corresponde a la Hoja actual, luego se elige el evento, se utilizará Change porque este se ejecuta cada vez que hay cambios en alguna celda de la hoja
El código es:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
promedios = Range("L131:L137").Value
ataqueTipos = Range("B131:B137").Value
For i = 2 To UBound(promedios)
For j = 1 To UBound(promedios) + 1 - i
If promedios(j, 1) < promedios(j + 1, 1) Then
tmp = promedios(j, 1)
promedios(j, 1) = promedios(j + 1, 1)
promedios(j + 1, 1) = tmp
tmp = ataqueTipos(j, 1)
ataqueTipos(j, 1) = ataqueTipos(j + 1, 1)
ataqueTipos(j + 1, 1) = tmp
End If
Next
Next
Range("B127") = ataqueTipos(1, 1)
Range("B128") = ataqueTipos(2, 1)
Application.EnableEvents = True
End Sub
1. Indica el tipo de estructura (Private Sub) en este caso es una Procedimiento (función que no devuelve valor), el nombre es Worksheet_Change (puede ser cualquiera, pero el nombre por defecto ya es bastante claro al dar a entender que corresponde al evento Change de la Hoja), luego están los argumentos del Procedimiento, por defecto está el argumento de nombre Target de tipo (As) Range (rango de celdas) y se pasa por valor (ByVal)
2. Esta línea desactiva los eventos, claro, sino cualquier cambio en el Excel volvería a llamar a este Procedimiento, incluso este Procedimiento puede hacer cambios y no queremos que se re-llame, al menos no hasta terminar
3 y 4. Declaro dos variables Array, el primero para guardar los "promedios", y el otro para guardar los "ataques tipos", los Range siempre devuelven Arrays bidimensionales (Fila, Columna), además estos Arrays empezarán con índice 1 en lugar de 0
5 al 16. Es el famoso algoritmo de ordenación Burbuja, los bucles en las Macros de Excel (VBA) tienen diferente sintaxis a las que ya conoces, For inicializar_variable To valor_maximo_de_variable, por ejemplo el clásico for (i = 0; i < 10; i++) en VBA es: For i = 0 To 9, no requiere el i++ porque se sobreentiende que la variable debe aumentar de uno en uno con cada iteración
Ubound devuelve el último índice de un Array, si el Array es: vocales = ("a", "e", "i", "o", "u"), UBound(vocales) devolverá 4 porque se sobreentiende que el array vocales empieza con índice 0:
vocales(0) = "a"
vocales(1) = "e"
vocales(2) = "i"
vocales(3) = "o"
vocales(4) = "u"
Ah, y los Arrays en VBA usan paréntesis en lugar de corchetes como se puede ver. Considerando que Range devuelve un Array bidimensional y que además el primer índice empieza en 1, sería algo así suponiendo que las vocales están en el rango "A1:A5" :
vocales = Range("A1:A5")
Es lo mismo que:
vocales(1,1) = "a"
vocales(2,1) = "e"
vocales(3,1) = "i"
vocales(4,1) = "o"
vocales(5,1) = "u"
Recuerda: vocales(fila, columna), si usas UBound(vocales) devuelve el último índice del array, es decir 5
El Algoritmo de Ordenación es muy popular así que no lo explicaré, salvo las líneas 11, 12 y 13 porque lo que hacen es replicar esa misma ordenación con el Array "ataqueTipos" para replicar esa ordenación, también decir que en todos los casos se está índicando el 1 en por ejemplo promedio(j,1) porque el Array es bidimensional pero como solo tiene una Columna y no necesitamos más, ponemos 1, solo varía la Fila
17 y 18 Escriben el primer y segundo valor del Array ordenado en la celda de resultados
19 Vuelve a activar los eventos
---
Adjunto el archivo de excel para que lo revises, y otra cosa importante de las Macros es que para que se conserven debes guardar el archivo como .xlsm en lugar de .xlsx, el propio excel te lo recordará, aparte de los permisos y confianza en las macros claro, esa es la desventaja de las macros, muy escandalosas
https://mega.nz/file/0wYUwajC#ulsigtqFbg-HURicEbyU8zLyTYgq8dpBN3CWIhE3vYk