Average ranking formula

[NJS1982]

Hi, I am wondering if anyone can help me with a formula to work out an average ranking in a series of results.
The data is below; I am trying to ascertain what the average ranking is somehow?
Thanks in advance!

Ranking	Number
1	20
2	11
3	22
4	9
5	34
6	14
7	16
8	47
9	7
10	0
Total:	180

 

[Joe4]

Assuming that your values are in cells A2:B11, does this give you what you expect?

=SUMPRODUCT(A2:A11*B2:B11)/SUM(B2:B11)

 

[Fluff]

What version of Excel are you using?

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’)

Also what exactly do you mean by the average rank?

 

[NJS1982]

Thanks both; @Joe4 has got it - result = 5.2722222, so that's perfect. I was trying all sorts of average formulas and couldn't get it right with using the rank column too.
I am using O365 (Desktop version). I'll update my account details (been a while since I have used this forum).

Thanks again!

 

[Joe4]

You are welcome.
Glad we were able to help!