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!
  • Students Click Here

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


Research Report: State of IoT Adoption in Product Development 2019
This research report, based on a survey of 234 product development professionals, examines the current state of Internet of Things (IoT) adoption by product design teams, its perceived importance, and what features and capabilities teams consider important when making decision about adding IoT functionality to their products. Download Now
Research Report: Augmented Reality for Maintenance, Repair and Overhaul (MRO)
The term Industry 4.0 denotes a cluster of technologies that’s poised to fundamentally reshape manufacturing and bring about a new industrial revolution. These include 3D printing (AM), the Industrial Internet of Things (IIoT), artificial intelligence (AI) and mixed reality technologies, more commonly known as virtual reality (VR) and augmented reality (AR). Download Now

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