excel - Calculate the standard deviation from a binned list -

say have list of integer values, 1 through 10. instead of having actual dataset, have quantities of each value. example:

1  | 73 2  | 121 3  | 155 4  | 149 5  | 187 6  | 180 7  | 166 8  | 148 9  | 120 10 | 81 

as can see, it'd incredibly time consuming list out each value individually (73 1s, 121 2s et cetera) it's way know how use stdev().

how can calculate standard deviation of values?

a custom user defined function (aka udf) might expedient route.

      

your values column have been exploded multiples in column b d2:d1381 (see below).

the stdev, stdev.p , stdev.s formulas in e2:g2 are,

=stdev($d2:$d1381) =stdev.p($d2:$d1381) =stdev.s($d2:$d1381) 

the udf formulas in e3:g3 are,

=udf_stdev_exploded($a2:$a11, 1)   ' or =udf_stdev_exploded($a2:$a11) =udf_stdev_exploded($a2:$a11, 2) =udf_stdev_exploded($a2:$a11, 3) 

the udf formulas based upon following module code.

function udf_stdev_exploded(rng range, optional ityp long = 1)     dim r long, v long, vvals variant      redim vvals(0)     r = 1 rng.rows.count         v = 1 rng.cells(r, 2).value2             vvals(ubound(vvals)) = rng.cells(r, 1).value2             redim preserve vvals(0 ubound(vvals) + 1)         next v     next r     redim preserve vvals(0 ubound(vvals) - 1)      select case ityp         case 1             udf_stdev_exploded = worksheetfunction.stdev(vvals)         case 2             udf_stdev_exploded = worksheetfunction.stdev_p(vvals)         case 3             udf_stdev_exploded = worksheetfunction.stdev_s(vvals)         case else             'do nothing     end select end function 

i've added option process array using either stdev, stdev.p or stdev.s functions. stdev function default.

addendum

the code exploding values a2:b11 long column of values on worksheet is,

sub stdev_vals()     dim rw long, f long, n long      worksheets("sheet1")         rw = 2 .cells(rows.count, 1).end(xlup).row             .cells(rows.count, 4).end(xlup).offset(1, 0).resize(.cells(rw, 2).value2, 1) = .cells(rw, 1).value2         next rw     end end sub