Building Standard Deviation using Array [Excel]
Building Standard Deviation using Array [Excel]
(OP)
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-...
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!
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-...
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





RE: Building Standard Deviation using Array [Excel]
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/
RE: Building Standard Deviation using Array [Excel]
Dik