Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Students Click Here

Building Standard Deviation using Array [Excel]

Building Standard Deviation using Array [Excel]

Building Standard Deviation using Array [Excel]

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

Raw Data

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.



RE: Building Standard Deviation using Array [Excel]

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:
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

RE: Building Standard Deviation using Array [Excel]

Great explanation...


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.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close