Find STDEV Of First 3 Numbers To Appear in Column

[amonty78]

Starting from cell A1, I have the following values through to A10 (BLANK = an empty cell) :

48.5
BLANK
BLANK
49
BLANK
46
BLANK
37.5
51.5
50.5

If I wanted to manually calculate the STDEV of the first 3 values to appear in the column, I would type =STDEV(A1:A6)
However, I need to paste this formula to columns where the values and blank cells are randomly placed. So I am trying to figure out a formula that will automatically apply the STDEV function the the first 3 cells that contain values.

Any ideas ?

Many thanks.

[FlameRetired]

With the understanding that the posted values are in A1:A10 array enter this formula.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

SUBTOTAL/OFFSET should handle this, but I am unable so far to get it to work.

	A	B	C
1	48.5	1.607275	In B1: {=LOOKUP(1E+306,STDEV.S(OFFSET($A$1,SMALL(IF(ISNUMBER($A$1:$A$10),ROW($A$1:$A$10)-MIN(ROW($A$1:$A$10))),ROW($1:$3)),)))}
2	BLANK
3	BLANK
4	49
5	BLANK
6	46
7	BLANK
8	37.5
9	51.5
10	50.5

[FlameRetired]

Here's the SUBTOTAL/OFFSET. Also array entered.

Please Login or Register  to view this content.

If you have a newer version of Excel please let us know. There are other ways to do this. ex. AGGREGATE (2010) has some stat functions. STDEV is one of them.

[amonty78]

I've used the SUBTOTAL/OFFSET formula, it works really well thankyou.
Cheers !

[FlameRetired]

You are welcome. Glad to help. Thank you for the feedback, rep and marking your thread Solved.