Average of first 4 cells that have a value

[Mariyka1]

I have a worksheet that looks similar to the below - the data-set is very large so I just need to be able to create a single formula to take the average of the first 4 values in that row. some at the beginning are blank - I want the formula to ignore blank and take the average of the first four values. I know there is an average formula that ignores blanks - but I just don't know how I can constrict this formula to only take the first 4 values down to about 500k of rows.

January	February	March	April	May	June	July	August	September	November	December
20	10	40	70	70	30	60	70	30	20	10
	30	50	80	250	80	50	250	80	20	50
50	40	10	50	40	40	30	0	40	0	0
		30	120	10	120	40	50	20	40	0
				50	50	40	50	50	30	50
				50	40	120	50	40	40	20

<colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody>
</tbody>

you help is very much appreciated!

 

[Tom.Jones]

Array formula:

=AVERAGE(INDEX($A2:$K2, SMALL(IF($A2:$K2>0, COLUMN($A2:$K2), ""), 4)):INDEX($A2:$K2, SMALL(IF($A2:$K2>0, COLUMN($A2:$K2), ""), 1)))

Ctrl+Shift+Enter

 

[AhoyNC]

Another option:
Also an array formula that must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	January	February	March	April	May	June	July	August	September	November	December	Average
2	20	10	40	70	70	30	60	70	30	20	10	35
3		30	50	80	250	80	50	250	80	20	50	102.5
4	50	40	10	50	40	40	30	0	40	0	0	37.5
5			30	120	10	120	40	50	20	40	0	70
6					50	50	40	50	50	30	50	47.5
7					50	40	120	50	40	40	20	65
Sheet

 

[Mariyka1]

Thank you, although unfortunately it did not give me the average of the first 4 values in each row - it was closer to the average of the first 38 columns (out of a total of 117 columns)

 

[Mariyka1]

Thank you!! this worked perfectly

Another option:
Also an array formula that must be entered with CTRL-SHIFT-ENTER.

	A	B	C	D	E	F	G	H	I	J	K	L
1	January	February	March	April	May	June	July	August	September	November	December	Average
2	20	10	40	70	70	30	60	70	30	20	10	35
3		30	50	80	250	80	50	250	80	20	50	102.5
4	50	40	10	50	40	40	30	0	40	0	0	37.5
5			30	120	10	120	40	50	20	40	0	70
6					50	50	40	50	50	30	50	47.5
7					50	40	120	50	40	40	20	65

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:77px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:85px;"><col style="width:77px;"><col style="width:79px;"><col style="width:66px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula L2 {=AVERAGE(OFFSET(A2:K2,,MATCH(TRUE,ISNUMBER(A2:K2),0)-1,,4))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[AhoyNC]

You're welcome. Thanks for the feedback. I tried the formula by Tom_Jones and it worked for me.