Need URGENT help with populating an array based on Count

[nld08]

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

<tbody>
</tbody>

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

 

[thisoldman]

	A	B	C	D	E	F	G	H	I
1	Count_1	Num_1	Count_2	Num_2	Count_3	Num_3		Total_Count	6
2	1	100	2	200	3	300		Mean	233.3333
3								Variance	5555.556
4								Std Dev	74.5356

<tbody>
</tbody>

Cell Formula Total_Count =SUM(Count_1,Count_2,Count_3) Mean =((Count_1 * Num_1) + (Count_2 * Num_2) + (Count_3 * Num_3)) / Total_Count Variance =((Count_1*((Mean - Num_1)^2)) + (Count_2*((Mean - Num_2)^2)) + (Count_3*((Mean - Num_3)^2))) / Total_Count Std Dev =Variance^0.5 <tbody> </tbody>

<tbody>
</tbody>

 

[Domenic]

For a single cell formula solution, assuming that A1:F2 contains the data, maybe...

=STDEVP(LOOKUP(ROW(INDIRECT("1:"&SUMIF(A1:F1,"Count",A2:F2))),MMULT(N(OFFSET(A2:F2,,TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(A2:F2)/2)))*2-2,,1)),(ROW(INDIRECT("1:"&COLUMNS(A2:F2)/2))<=TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(A2:F2)/2))))+0)-N(OFFSET(A2:F2,,TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(A2:F2)/2)))*2-2,,1))+1,N(OFFSET(A2:F2,,TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(A2:F2)/2)))*2-2+1,,1))))

...which needs to be confirmed with CONTROL+SHIFT+ENTER. Adjust the ranges A1:F1 and A2:F2, accordingly. Note, however, this formula is resource-intensive. So it will slow down your spreadsheet if it's copied to many rows.

Hope this helps!

 

[XOR LX]

Very nice, Domenic.

Actually you can shorten that somewhat (and remove a large part of the volatility) by taking advantage of the fact that the lookup_vector does not need to be re-dimensioned (each element can simply be duplicated):

=STDEVP(LOOKUP(ROW(INDIRECT("1:"&SUMIF(A1:F1,"Count",A2:F2))),MMULT(0+(ROW(INDIRECT("1:"&COLUMNS(A1:F1)))>=TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(A1:F1))))),TRANSPOSE(IF(A1:F1="Count",A2:F2,0)))-TRANSPOSE(IF(A1:F1="Count",A2:F2,0))+1,IF(A1:F1<>"Count",A2:F2,B2:G2)))

Regards

 

[Domenic]

XOR LX, nicely done! In using your method, we can get rid of the volatile function INDIRECT...

=STDEVP(LOOKUP(ROW(INDEX(A:A,1):INDEX(A:A,SUMIF(A1:F1,"Count",A2:F2))),MMULT(0+(TRANSPOSE(COLUMN(A2:F2))>=COLUMN(A2:F2)),TRANSPOSE(IF(A1:F1="Count",A2:F2,0)))-TRANSPOSE(IF(A1:F1="Count",A2:F2,0))+1,IF(A1:F1<>"Count",A2:F2,B2:G2)))

Cheers!

 

[XOR LX]

Ah, yes, of course! Good stuff! And not a trace of volatility left!!

Like the idea of this LOOKUP construction to generate the required array, by the way.

Cheers