Refercence the cells percentile compared to its column

There are 4 columns, D, E, F, G

Each column lists scores in every row. The scores mean nothing by themselves, but exceptionally high or low scores compared to the group are a cause for concern.

To make analyzing this easier, I created an H column. The H column analyzes all four scores in a row, and aggregate them into one main score.

I want the formula for this score to do the following:

Look at the value in each column for that row. For every value, compute what percentile it falls in compared to the rest of the scores in that column. Compute four percentiles scores (one for each column) and then produce an average of the percentiles.

So basically this is the plaintext version of the formula I need for cell H2:


(D2's percentile when compared to alll the numbers in column D)

+

(E2's percentile when compared to alll the numbers in column D)

+

(F2's percentile when compared to alll the numbers in column F)

+

(G2's percentile when compared to alll the numbers in column G)

************DIVIDED BY****************

4


How would I do this?

Also if someone is better at statistics and can think of a more mathmatically accurate and sound way to accomplish my goal, feel free to come up with a different algorithm.

I went with:

=AVERAGE(PERCENTRANK($D$2:$D$28,D2),PERCENTRANK($E$2:$E$28,E2),PERCENTRANK($F$2:$F$28,F2),PERCENTRANK($G$2:$G$28,G2))

average percentile.xlsx

Take a look at these statistical parameters.

http://en.wikipedia.org/wiki/Coefficient_of_variation

http://en.wikipedia.org/wiki/Standard_deviation