×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Building Standard Deviation using Array [Excel]2

## 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!

-Kevin

### RE: Building Standard Deviation using Array [Excel]

2
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/

### RE: Building Standard Deviation using Array [Excel]

Great explanation...

Dik

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!