Normalize data

[MrKickAss]

Hey everyone,

I am trying to create a formula to normalize my data sheets.
So basically, what I need is to calculate a ratio and repeat it as many times as the 100% amount. I could use the click+scroll method, but in some cases it would be rather very annoying.

As an example: I have 4 apple trees and I want the RED APPLES / ALL APPLES ratio for all trees and repeat it as many times as the ALL APPLES value, in order to get a standard deviation for all the trees. To do so, I need to normalize the apples.

what I have:

Tree-nr/ red apples/all apples
Tree 1/ 1/10
Tree 2/ 5/6
Tree 3/ 2/2
Tree 4/ 1/5

What I want:

Tree 1
0,1
0,1
0,1
0,1
0,1
0,1
0,1
0,1
0,1
0,1
Tree 2
0,833333333
0,833333333
0,833333333
0,833333333
0,833333333
0,833333333
Tree 3
1
1
Tree 4
0,2
0,2
0,2
0,2
0,2


Thanks in advance!!

PS: My excel version is 2007

[ben_hensel]

I don't get it. Wouldn't it be easier to just use a population-weighted standard deviation calculation instead?

Like, STDEV is defined (in excel) as SQRT( sum(x - mean)^2 / (n-1) ) for each x from 1 to n in a range.

So couldn't you just find a weighted average and then run:
=SQRT(sumproduct(total, x - weighted_average, x - weighted_average) / sum(all_apples, -1) )

Something like that? That's like a guess, not something I derived on a sheet of paper properly, so there's a good chance it has holes in it.

But my point is, I feel like you'd have a much easier time adjusting the formula to properly accept the dataset you actually have, rather than trying to message the dataset into some intermediate form to match the way the built-in functions work.