Urgent help with populating an array formula

**nld08** · 03-14-2014, 12:23 PM

Hi all,

I am trying to calculate the Standard Deviation of an array. However, to populate the array, I have the following:

Count Number 1 Count Number 2 Count Number 3
1 100 2 200 3 300

For example, this above means that there are 1 100's, 2 200's, 3 300's. I would ideally like to populate an array that is {100,200,200,300,300,300} based on those 6 columns that provide a count and a number. Then, I would like to calculate the standard deviation based on those (in this case it is 74.5).

Does anyone know how to do this without VBA? Thank you so so much! Any help / tips would be greatly appreciated...array.jpg

**Kaper** · 03-16-2014, 03:14 AM

Yes, it can be done without VBA.
One shall start with basic equations for the standard deviation calculation.

The data layout if more than inconvenient*, so I ended up with rather long formula. But it is working.
The only thing to observe when adopting to your real data layout is that nis shall be in odd columns and xis in following even columns. if it is opposite in all MODs use xis range, not nis.

My "magic formula" :-D is:

Please Login or Register  to view this content.

*) of course would be much easier and shorter if data is organized in rows/columns like (or transposed/reversed, but separate x and n):
1 100
2 200
3 300