Standard Deviation

  • Thread starter Thread starter Jethro[AGHL]
  • Start date Start date
J

Jethro[AGHL]

Is it possible to do a Standard Deviation equation on groups of numbers
without writing down all of them?
To do a STDEV on a list of numbers that you know contain groups of
similar numbers.
For example a list of 300 numbers, of which 50 are "72", 50 are "68", 35
are "43", etc... without having to create a list of all 300 numbers and
then do a STDEV on them.

Is it possible or do the numbers all have to be listed in a column?

Thanks,
Jeff
 
Yes

With the number of occurrences of each number in column A and the numbers in
column B

Use

=(SUM((B1:B3-SUMPRODUCT($B$1:$B$3,$A$1:$A$3)/SUM($A$1:$A$3))^2*A1:A3)/(COUNT
(C1:C135)-1))^0.5

To obtain the result for STDEV

and

=(SUM((B1:B3-SUMPRODUCT($B$1:$B$3,$A$1:$A$3)/SUM($A$1:$A$3))^2*A1:A3)/COUNT(
C1:C135))^0.5

To obtain the result for STDEVP

Note these are array formulas so use Ctrl+Shift+Enter to enter the formula
and it will appear in {}'s.

HTH

PC
 
Is it possible to do a Standard Deviation equation on groups of numbers
without writing down all of them?
To do a STDEV on a list of numbers that you know contain groups of
similar numbers.
For example a list of 300 numbers, of which 50 are "72", 50 are "68", 35
are "43", etc... without having to create a list of all 300 numbers and
then do a STDEV on them.

Is it possible or do the numbers all have to be listed in a column?
Click to expand...

It may be expedient just to enter them all, but you could use something like

=STDEV(72*ROW(1:50)^0,68*ROW(1:50)^0,43*ROW(1:35)^0,...)

as long as you remain within Excel's limit of 29 or fewer function arguments.
 
Back
Top