Compute Normalized Score with Google Sheet Formula

Question

I have a spreadsheet that looks like this:

        Score1    Score2    Score3
Joe          8         5         3 
Jane         4         6         2
Abdul        5         5         3
Nadia        9         7         5

I want to compute a new column containing an "average normalized score" (ANS) for each person, where for each score I get a "normalized score" by subtracting the mean and dividing by the standard deviation over that score-column, and then average up those normalized scores to get the ANS per-person.

The result should be:

        Score1    Score2    Score3        ANS
Joe          8         5         3     -0.135
Jane         4         6         2     -0.686
Abdul        5         5         3     -0.621
Nadia        9         7         5      1.44

The python-esque pseudo-code for this operation would be:

for j in [1..N_cols]:
    mean_scores = mean(score[i, j] for i in [1..N_rows])
    std_scores = std(score[i, j] for i in [1..N_rows])
    for i in [1..N_rows]:
        norm_scores[i, j] = (scores[i, j] - mean_scores)/std_scores

for i in [1..N_rows]:
    avg_norm_scores[i] = mean(norm_scores[i, j] for j in [1..N_cols])

Or in numpy it would simply be:

avg_norm_scores = ((scores - scores.mean(axis=0))/scores.std(axis=0)).mean(axis=1)

What is the most succinct way I could calculate a new column containing the ANS in google sheets?

pnuts pnuts · Accepted Answer · 2019-06-28T03:46:37

Maybe not the most succinct, but the right answers:

=average((B2-average(B:B))/STDEVP(B:B),(C2-average(C:C))/STDEVP(C:C),(D2-average(D:D))/STDEVP(D:D))

copied down to suit, assuming 8 is in B2.

Compute Normalized Score with Google Sheet Formula

1 Answers