Average row in array based on selected hour

[LambChoptheKid]

Hey everyone,

I am trying to average a row of data in my table here based on the number that I input in cell B2.

For example, as I have entered 17:00 in cell B2, I would like the formula to average the 15 highest values within the row of data corresponding to hour 17:00.

I would appreciate any help or guidance with solving this problem.

Thank you!

 

[Peter_SSs]

For the future, you will generally get faster/better responses if you can provide data that can be used for testing without heaps of manual typing. Have a look at XL2BB

I would like the formula to average the 15 highest values within the row of data corresponding to hour 17:00.

See how this goes.

23 12 15.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1		6:00
2
3		705.0666667
4
5
6
7	1:00	93	484	769	854	66	620	372	606	861	529	844	368	497	966	414	381	907	31	294	330
8	2:00	317	819	800	886	521	766	914	512	348	672	352	470	912	575	113	351	749	467	928	140
9	3:00	963	163	611	459	799	867	966	228	925	965	158	133	193	493	207	637	618	412	355	454
10	4:00	409	700	620	641	598	180	353	182	766	900	824	903	201	417	136	205	635	966	370	178
11	5:00	450	689	168	142	916	523	522	12	127	68	445	10	899	873	165	486	926	259	322	800
12	6:00	692	956	830	92	389	685	304	243	723	182	947	336	764	340	996	784	790	824	520	335
13	7:00	595	724	839	470	508	188	91	993	331	746	126	882	191	299	333	152	44	572	347	556
14	8:00	559	266	623	906	778	519	642	650	695	416	27	503	241	511	95	728	496	392	874	440
15	9:00	25	650	838	427	953	195	274	540	798	569	487	511	782	38	608	220	667	478	798	675
Average

Cell Formulas
Range	Formula
B3	B3	=AVERAGE(INDEX(SORT(FILTER(B7:U15,A7:A15=B1),,-1,1),SEQUENCE(,15)))

 

[LambChoptheKid]

For the future, you will generally get faster/better responses if you can provide data that can be used for testing without heaps of manual typing. Have a look at XL2BB


See how this goes.

23 12 15.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1		6:00
2
3		705.0666667
4
5
6
7	1:00	93	484	769	854	66	620	372	606	861	529	844	368	497	966	414	381	907	31	294	330
8	2:00	317	819	800	886	521	766	914	512	348	672	352	470	912	575	113	351	749	467	928	140
9	3:00	963	163	611	459	799	867	966	228	925	965	158	133	193	493	207	637	618	412	355	454
10	4:00	409	700	620	641	598	180	353	182	766	900	824	903	201	417	136	205	635	966	370	178
11	5:00	450	689	168	142	916	523	522	12	127	68	445	10	899	873	165	486	926	259	322	800
12	6:00	692	956	830	92	389	685	304	243	723	182	947	336	764	340	996	784	790	824	520	335
13	7:00	595	724	839	470	508	188	91	993	331	746	126	882	191	299	333	152	44	572	347	556
14	8:00	559	266	623	906	778	519	642	650	695	416	27	503	241	511	95	728	496	392	874	440
15	9:00	25	650	838	427	953	195	274	540	798	569	487	511	782	38	608	220	667	478	798	675
Average

Cell Formulas
Range	Formula
B3	B3	=AVERAGE(INDEX(SORT(FILTER(B7:U15,A7:A15=B1),,-1,1),SEQUENCE(,15)))

This worked, thank you! Unfortunately I'm not in a position to use non-native addons in my workspace so pictures is the best that I'm currently able to do.

Appreciate the help!

 

[Peter_SSs]

I'm not in a position to use non-native addons in my workspace

So a different 'for the future';

• State that in your opening post so we don't keep asking, and
• For smallish samples you can simply copy/paste from Excel into your post and tell us what range it is and explain any formulas involved. At least that way we can copy for testing, which we cannot do easily from an image.

This worked, thank you!

You're welcome. Glad to help.