Thousands of rows of data

Days Qty (pcs)
500 50
280 3
50 100
etc

Ho would I get the standrd deviation for this information without having to
type the 500 in 50 times, the 280 in 3 times, the 50 in 100 times, etc?
Again, I have thousands of rows of this data.

Using the Standard deviation equation given in XL help file:

=SQRT(SUMPRODUCT((A2:A2000-SUMPRODUCT(A2:A2000,B2:B2000)/SUM(B2:B2000))^2,B2:B2000)/(SUM(B2:B2000)-1))

Adjust range sizes to fit your data.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"LB" wrote:

Thousands of rows of data

Days Qty (pcs)
500 50
280 3
50 100
etc

Ho would I get the standrd deviation for this information without having to
type the 500 in 50 times, the 280 in 3 times, the 50 in 100 times, etc?
Again, I have thousands of rows of this data.

Your question is: how can you compute the std dev of grouped data?

I suggest that we take a simpler example to facilitate verification.
Consider the following in A1:A3 and B1:B3:

3 50
4 3
5 100

The std dev and avg can be computed easily by
STDEVP({50,50,50,3,3,3,3,100,100,100,100,100}) and
AVERAGE({50,50,50,3,3,3,3,100,100,100,100,100}). Note that I use STDEVP
instead of STDEV. I am assuming that A1:B3 represents all of the data, not
a sampling.

More generally, STDEVP of the grouped data can be computed by:

=SQRT(SUMPRODUCT(A1:A3,(B1:B3-X1)^2) / SUM(A1:A3))

where X1 is the average computed by:

=SUMPRODUCT(A1:A3,B1:B3) / SUM(A1:A3)

Compare these results with teh STDEVP and AVERAGE results.

Note: You could substitute the latter formula for X1 in the first formula,
but I think it would be less effiicient. You may or may not see a
difference when you have "thousands" of groups. But in any case, I think
the first formula is more readable as is.

If you want to compute STDEV instead of STDEVP, replace SUM(A1:A3) with
(SUM(A1:A3)-1) -- note the parentheses -- in the first formula only. The
average is still computed by dividing by SUM(A1:A3).


----- original message -----

"LB" wrote in message
...
Thousands of rows of data

Days Qty (pcs)
500 50
280 3
50 100
etc

Ho would I get the standrd deviation for this information without having
to
type the 500 in 50 times, the 280 in 3 times, the 50 in 100 times, etc?
Again, I have thousands of rows of this data.

Clarification....

"LB" wrote:
Days Qty (pcs)
500 50
280 3
50 100

Ho would I get the standrd deviation for this information without having
to type the 500 in 50 times, the 280 in 3 times, the 50 in 100 times,
etc?

I assumed you want to compute the std dev of qty, not std dev of days. So
you enter 50 500 times, not 500 50 times as you wrote. That would compute
the std dev of daily inventory or daily qty sold, for example.

If you truly want to compute the std dev of days (!) -- for example, the std
dev of days to produce or ship a piece -- you can just reverse the ranges
A1:A3 and B1:B3 in the formulas I provided.

It's a little more difficult to compare with the STDEVP and AVERAGE results.
I wish I had chosen smaller qtys ;-).


----- original message -----

"JoeU2004" wrote in message
...
Your question is: how can you compute the std dev of grouped data?

I suggest that we take a simpler example to facilitate verification.
Consider the following in A1:A3 and B1:B3:

3 50
4 3
5 100

The std dev and avg can be computed easily by
STDEVP({50,50,50,3,3,3,3,100,100,100,100,100}) and
AVERAGE({50,50,50,3,3,3,3,100,100,100,100,100}). Note that I use STDEVP
instead of STDEV. I am assuming that A1:B3 represents all of the data,
not a sampling.

More generally, STDEVP of the grouped data can be computed by:

=SQRT(SUMPRODUCT(A1:A3,(B1:B3-X1)^2) / SUM(A1:A3))

where X1 is the average computed by:

=SUMPRODUCT(A1:A3,B1:B3) / SUM(A1:A3)

Compare these results with teh STDEVP and AVERAGE results.

Note: You could substitute the latter formula for X1 in the first
formula, but I think it would be less effiicient. You may or may not see
a difference when you have "thousands" of groups. But in any case, I
think the first formula is more readable as is.

If you want to compute STDEV instead of STDEVP, replace SUM(A1:A3) with
(SUM(A1:A3)-1) -- note the parentheses -- in the first formula only. The
average is still computed by dividing by SUM(A1:A3).


----- original message -----

"LB" wrote in message
...
Thousands of rows of data

Days Qty (pcs)
500 50
280 3
50 100
etc

Ho would I get the standrd deviation for this information without having
to
type the 500 in 50 times, the 280 in 3 times, the 50 in 100 times, etc?
Again, I have thousands of rows of this data.