Building Standard Deviation using Array [Excel] 2

[kgwhipp]

This is my current design. I input raw data, and it outputs STDEV.S() calculations on the right hand side.

Raw Data
1
1
1
1
1
2
2
2
3
3

Standard Deviation 1.700619082

# of 1's: 5
# of 2's: 3
# of 3's: 2

My thoughts so far is that I want to feed the STDEV.S() function an ARRAY of the numbers, 1,1,1,1,2,2,2,2,3,3 etc.

an array looks like {1,1,1,1,1,2,2,2,2,2,3,3,3,3}

and =STDEV({1,1,1,1,1,2,2,2,2,2,3,3,3,3,3}) gives an answser of .84 (good!)

So the tricky part now is building this ARRAY using the "# of 1's" inputs, it might look something like an amalgam of "indirect, rept, row, etc" this is where I got stuck.

I was able to have it dump 111112222233333 into a single cell using REPT, with the hopes I could break into individuals and form into an array, but this won't work for numbers above 9, and it's really too hacky.

I CAN make a column, calculate stdev off of that and hide it away, but the solutions for that have limitations as well as it requires a bunch of manual work (counting how many items there are, and changing the corresponding formulas).
Close:

https://stackoverflow.com/questions/11841213/copy-value-n-times-in-excel

Looking for non VBA solution as I'd like portability over to G-sheets. Array formulas are OK even though it takes a bit of alternative formatting.

Thanks!



-Kevin

 

[IDS]

A non-VBA solution is:

Set up a table of the number of each value (Col B) and the first row for each value (Col C), then in E1 enter:
=MATCH(ROW(D1),$C$2:$C$5,1)
and copy down as far as required.

Then generate the address for the list of values required: ="Sheet1!E1:E"&C5-1

You can then use Indirect to return the standard deviation on that range:=STDEV.S(INDIRECT(B6))

Then enter the number of 1,2 and 3 in column B and the Standard deviation will update:

An alternative would be to do it in JavaScript, which could presumably be converted to G-sheets pretty easily, or use Excel on One-drive, but I have yet to try that.

Or my preference would be to use a VBA UDF, and forget about G-sheets


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[dik]

Great explanation...

Dik