Weighted rank- if duplicates rank the average

Hello

I have some difficulties on what function to use..
I'm ranking some data entry but if duplicates get the average and that will be the weighted rank of the 2 data.
To further explain, I've attached a sample worksheet.

Thanks in advance.

weighted rank.xlsx

You can use this array* formula in B4:

=IF(COUNTIF(A$4:A$17,A4)=1,RANK(A4,A$4:A$17,1),AVERAGE(IF(A$4:A$17=A4,ROW(A$4:A$17)-3)))

and copy it down to B17.

* An array formula must be committed using the key combination of CTRL-SHIFT-ENTER (CSE) instead of the usual <Enter>.

Hope this helps.

Pete

EDIT: Sorry, slight amendment to the formula:

=IF(COUNTIF(A$4:A$17,A4)=1,ROW(A4)-3,AVERAGE(IF(A$4:A$17=A4,ROW(A$4:A$17)-3)))

still use CSE to commit it.

Pete

Hey, that was fast thanks.

A little adjustment in the ranges and i'm good to go.
And it works like a charm

Presumably you could just use the AVERAGE part alone, i.e.

=AVERAGE(IF(A$4:A$17=A4,ROW(A$4:A$17)-3))