need to calculate the average of the first ten real results in a series of 30 numbers - some will be blank

[sfinns]

I need to calculate the average of the first ten real results in a series of 30 numbers - some will be blank.. the first ten in the series from left to right and ignore any numbers post teh first ten results taht appear - there will be several blanks in the series and multiple series with lots of blanks. If there are not ten signalled numbers in the series then the average of what is there ,ie 8 /9 etc.

item

result?

1​

2​

3​

4​

5​

6​

7​

8​

9​

10​

11​

12​

13​

14​

15​

16​

17​

18​

19​

20​

21​

22​

23​

24​

25​

26​

27​

28​

29​

30​

figures

1.8%​

16.0%​

8.3%​

1.4%​

-2.2%​

13.2%​

-11.7%​

3.9%​

10.4%​

19.5%​

-13.1%​

4.1%​

6.5%​

-8.2%​

23.0%​

9.8%​

4.2%​

14.9%​

5.9%​

7.8%​

1.2%​

1.3%​

6.9%​

figures

5.9%​

20.1%​

-3.8%​

-0.1%​

24.1%​

3.8%​

13.4%​

12.6%​

82.3%​

4.9%​

10.4%​

8.0%​

0.0%​

1.6%​

5.5%​

22.7%​

22.9%​

-3.6%​

9.5%​

average of first ten results that arent blank?

if less than ten then whatever the number

 

[Marcelo Branco]

Maybe...

Pasta1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1	item	Result		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30
2	figures	6,1%					1,8%	16,0%	8,3%	1,4%	-2,2%	13,2%	-11,7%	3,9%		10,4%	19,5%	-13,1%	4,1%	6,5%	-8,2%	23,0%	9,8%	4,2%			14,9%	5,9%	7,8%	1,2%		1,3%	6,9%
3	figures	17,7%						5,9%	20,1%			-0,1%	24,1%	3,8%		13,4%	12,6%	82,3%		4,9%	10,4%	8,0%	0,0%	1,6%			5,5%	22,7%		22,9%		-3,6%	9,5%
Plan1

Cell Formulas
Range	Formula
B2:B3	B2	=AVERAGE(D2:INDEX(D2:AG2,MATCH(10,INDEX(SUBTOTAL(2,OFFSET(D2,,,1,COLUMN(D2:AG2)-COLUMN(D2)+1)),),1)))

Hope this helps

M.

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

If you have Excel 365 with the new dynamic array functions then you can try the column C formula below.
Failing that I have suggested in column B a modification to Marcelo's formula to account for ..

If there are not ten signalled numbers in the series then the average of what is there ,ie 8 /9 etc.

20 03 06.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1	Result	Result	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30
2	6.1%	6.1%				1.8%	16.0%	8.3%	1.4%	-2.2%	13.2%	-11.7%	3.9%		10.4%	19.5%	-13.1%	4.1%	6.5%	-8.2%	23.0%	9.8%	4.2%			14.9%	5.9%	7.8%	1.2%		1.3%	6.9%
3	17.7%	17.7%					5.9%	20.1%			-0.1%	24.1%	3.8%		13.4%	12.6%	82.3%		4.9%	10.4%	8.0%	0.0%	1.6%			5.5%	22.7%		22.9%		-3.6%	9.5%
4	4.7%	4.7%			3.20%		6.20%
Average First 10

Cell Formulas
Range	Formula
B2:B4	B2	=AVERAGE(D2:INDEX(D2:AG2,MATCH(MIN(10,COUNT(D2:AG2)),INDEX(SUBTOTAL(2,OFFSET(D2,,,1,COLUMN(D2:AG2)-COLUMN(D2)+1)),),0)))
C2:C4	C2	=AVERAGE(INDEX(FILTER(D2:AG2,D2:AG2<>""),SEQUENCE(,MIN(10,COUNT(D2:AG2)))))

 

[Marcelo Branco]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

If you have Excel 365 with the new dynamic array functions then you can try the column C formula below.
Failing that I have suggested in column B a modification to Marcelo's formula to account for ..

If there are not ten signalled numbers in the series then the average of what is there ,ie 8 /9 etc.

Peter,

My formula works with less than 10 numbers...

Pasta1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1	item	Result		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30
2	figures	6,1%					1,8%	16,0%	8,3%	1,4%	-2,2%	13,2%	-11,7%	3,9%		10,4%	19,5%	-13,1%	4,1%	6,5%	-8,2%	23,0%	9,8%	4,2%			14,9%	5,9%	7,8%	1,2%		1,3%	6,9%
3	figures	17,7%						5,9%	20,1%			-0,1%	24,1%	3,8%		13,4%	12,6%	82,3%		4,9%	10,4%	8,0%	0,0%	1,6%			5,5%	22,7%		22,9%		-3,6%	9,5%
4	figures	4,7%				3,2%		6,2%
Plan2

Cell Formulas
Range	Formula
B2:B4	B2	=AVERAGE(D2:INDEX(D2:AG2,MATCH(10,INDEX(SUBTOTAL(2,OFFSET(D2,,,1,COLUMN(D2:AG2)-COLUMN(D2)+1)),),1)))

Observe the 1

M.

 

[Marcelo Branco]

Complementing the previous post

In fact, with less than 10 numbers the formula calculates the Average of the entire row (30 cells), but as blank cells are not taken into account by the AVERAGE function everything works fine .

M.

 

[Peter_SSs]

Peter,

My formula works with less than 10 numbers...

Observe the 1

M.

Hi Marcelo
Looking again at your formula it should and does work. Sorry for suggesting otherwise.

The reason that I thought it didn't is that when I copied your XL2BB sheet to mine, that final "1" gets changed to a 0 & I hadn't noticed that happen
I have no idea why that happens. Can you check what happens at your end if you 'Copy to clipboard' from your post to a fresh sheet? Might have to report an "issue".
Doesn't look like it should be language translation related but I guess that could have something to do with it

 

[Marcelo Branco]

Hi Marcelo
Looking again at your formula it should and does work. Sorry for suggesting otherwise.

The reason that I thought it didn't is that when I copied your XL2BB sheet to mine, that final "1" gets changed to a 0 & I hadn't noticed that happen
I have no idea why that happens. Can you check what happens at your end if you 'Copy to clipboard' from your post to a fresh sheet? Might have to report an "issue".
Doesn't look like it should be language translation related but I guess that could have something to do with it

It's not your fault.
I edited the formula 1 minute after I've posted it.
Sorry for not mentioned it

M.

 

[Peter_SSs]

It's not your fault.
I edited the formula 1 minute after I've posted it.

Ah, but it looks like you only edited it in the formula box below the sheet. If you hover over cell B2 in post #2 you will see that it still has a 0 at the end. It is those actual formulas in the cells that get copied when using 'Copy to clipboard'. If you edit a formula you really need to do it in your worksheet and re-generate the XL2BB code.

 

[Marcelo Branco]

Ah, but it looks like you only edited it in the formula box below the sheet. If you hover over cell B2 in post #2 you will see that it still has a 0 at the end. It is those actual formulas in the cells that get copied when using 'Copy to clipboard'. If you edit a formula you really need to do it in your worksheet and re-generate the XL2BB code.

Peter,

Thank you for pointing out

Yes, i edited directly the BB code generated by XL2BB and forgot to edit the formulas in B2:B3

To all: please, disregard post #2

Correct version...

Pasta1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1	item	Result		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30
2	figures	6,1%					1,8%	16,0%	8,3%	1,4%	-2,2%	13,2%	-11,7%	3,9%		10,4%	19,5%	-13,1%	4,1%	6,5%	-8,2%	23,0%	9,8%	4,2%			14,9%	5,9%	7,8%	1,2%		1,3%	6,9%
3	figures	17,7%						5,9%	20,1%			-0,1%	24,1%	3,8%		13,4%	12,6%	82,3%		4,9%	10,4%	8,0%	0,0%	1,6%			5,5%	22,7%		22,9%		-3,6%	9,5%
4	figures	4,7%				3,2%		6,2%
Plan2

Cell Formulas
Range	Formula
B2:B4	B2	=AVERAGE(D2:INDEX(D2:AG2,MATCH(10,INDEX(SUBTOTAL(2,OFFSET(D2,,,1,COLUMN(D2:AG2)-COLUMN(D2)+1)),),1)))

M.

 

[Peter_SSs]

Peter,

Thank you for pointing out

Yes, i edited directly the BB code generated by XL2BB and forgot to edit the formulas in B2:B3

No problem - at least we got to the bottom of it (& no re-write for XL2BB needed)


If dynamic array functions are not available, a further option without the volatile function OFFSET would be

20 03 06.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1	Result		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30
2	6.1%					1.8%	16.0%	8.3%	1.4%	-2.2%	13.2%	-11.7%	3.9%		10.4%	19.5%	-13.1%	4.1%	6.5%	-8.2%	23.0%	9.8%	4.2%			14.9%	5.9%	7.8%	1.2%		1.3%	6.9%
3	17.7%						5.9%	20.1%			-0.1%	24.1%	3.8%		13.4%	12.6%	82.3%		4.9%	10.4%	8.0%	0.0%	1.6%			5.5%	22.7%		22.9%		-3.6%	9.5%
4	4.7%				3.20%		6.20%
Average First 10 (2)

Cell Formulas
Range	Formula
B2:B4	B2	=AVERAGE(D2:INDEX(D2:AG2,AGGREGATE(15,6,(COLUMN(D2:AG2)-COLUMN(D2)+1)/(D2:AG2<>""),MIN(10,COUNT(D2:AG2)))))