How to generate an array of sorted results by omitting each element of operation (in this case AVERAGE)?

[Rnkhch]

Hello,

Part1:
I have 3 sets of 4 numbers as follows, such that I take the first number of each set and generate an average, then another average from the second numbers, an average from the third numbers, and finally an average from the fourth numbers. Then I take those "four" averages, and calculate a final average from those, and this is the final calculation that I need, which is so far, of course, very easy to do.

initial:
Q3=AVERAGE(B3,G3,L3)
R3=AVERAGE(C3,H3,M3)
S3=AVERAGE(D3,I3,N3)
T3=AVERAGE(E3,J3,O3)

Final:
V3=AVERAGE(Q3,R3,S3,T3)

A/1​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

2​

1​

2​

3​

4​

1​

2​

3​

4​

1​

2​

3​

4​

3​

43​

65​

98​

70​

32​

56​

71​

52​

78​

92​

23​

18​

4​

Part2:
Now, I want to exclude each of these 12 numbers, and see what the final result is, and this is the hard part (which I think would need some kind of array formula). I want to write a formula that I can spread in 12 cells, i.e. X3:AI3, such that each one of these 12 cells calculates the entire operation by excluding each number (in order). For example in cell X3, number B3 (i.e. 43) which is the first of the 12 numbers will be excluded from the operation, and the operation will become like this:

Final:
X3=AVERAGE(AVERAGE(--,G3,L3),AVERAGE(C3,H3,M3),AVERAGE(D3,I3,N3),AVERAGE(E3,J3,O3))
Y3=AVERAGE(AVERAGE(B3,--,L3),AVERAGE(C3,H3,M3),AVERAGE(D3,I3,N3),AVERAGE(E3,J3,O3))
.
.
.
AI3=AVERAGE(AVERAGE(B3,G3,L3),AVERAGE(C3,H3,M3),AVERAGE(D3,I3,N3),AVERAGE(E3,J3,--))

Part3:
Finally, in another set of 12 cells, i.e. AK3:AV3, I want the results of the previous step to be displayed in the order of larger to smaller starting at AK3. I want this step to be also done with a formula that I can spread from AK3 to AV3. (I hope to be able to handle this part with an INDEX formula which includes AGGREGATE which I saw at this link ():

)

I would highly appreciate any suggestions! Of course, if it is possible to combine parts 2 and 3 in a single range of 12 cells at X3:AI3, that's gonna be even enormously more fabulous ?

Thanks much!!!

 

[StephenCrump]

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2										43	65	98	70		32	56	71	52		78	92	23	18
3
4				CHECK	Av1	Av2	Av3	Av4
5		59.17		59.17	55.00	71.00	64.00	46.67			65	98	70		32	56	71	52		78	92	23	18
6		58.92		58.92	51.00	74.00	64.00	46.67		43		98	70		32	56	71	52		78	92	23	18
7		53.92		53.92	51.00	71.00	47.00	46.67		43	65		70		32	56	71	52		78	92	23	18
8		55.25		55.25	51.00	71.00	64.00	35.00		43	65	98			32	56	71	52		78	92	23	18
9		60.54		60.54	60.50	71.00	64.00	46.67		43	65	98	70			56	71	52		78	92	23	18
10		60.04		60.04	51.00	78.50	64.00	46.67		43	65	98	70		32		71	52		78	92	23	18
11		57.29		57.29	51.00	71.00	60.50	46.67		43	65	98	70		32	56		52		78	92	23	18
12		57.50		57.50	51.00	71.00	64.00	44.00		43	65	98	70		32	56	71			78	92	23	18
13		54.79		54.79	37.50	71.00	64.00	46.67		43	65	98	70		32	56	71	52			92	23	18
14		55.54		55.54	51.00	60.50	64.00	46.67		43	65	98	70		32	56	71	52		78		23	18
15		63.29		63.29	51.00	71.00	84.50	46.67		43	65	98	70		32	56	71	52		78	92		18
16		61.75		61.75	51.00	71.00	64.00	61.00		43	65	98	70		32	56	71	52		78	92	23
17
Sheet7

Cell Formulas
Range	Formula
B5:B16	B5	=LET(nos,FILTER(J2:W2,J2:W2<>""),wts,{0,1,1,1,1.5,1,1,1,1.5,1,1,1},MMULT(wts*INDEX(nos,MOD(SEQUENCE(,12)+SEQUENCE(12)-2,12)+1),SEQUENCE(12,,,0)))/12
D5:D16	D5	=AVERAGE(E5:H5)
E5:H16	E5	=AVERAGE(J5,O5,T5)
Dynamic array formulas.

If you like, you can wrap the formula in a SORT to do everything in one formula.

 

[Rnkhch]

Wow, thank you so much! This is a marvelous formula, and I learned a lot of new Excel things from it!! There are a couple of things:

1) I noticed that either with enter or control/shift/enter, the formula automatically spreads itself down vertically. By any chance is there a trick to make it spread across horizontally?

2) As you suggested, could you please show me the SORT as well (preferably horizontally ?)

(The reason for horizontal is that I have many rows of these 12 numbers under each other, so the only room available is to do the spreading/sorting horizontally across)

 

[bebo021999]

This is formula based Excel 2016 or earlier.
Not sure with later version can eliminate Part 2 or not, but VBA can. Post back if you refer to VBA.
First row is your initial data
from second row, I use random data.

Book1
	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	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV
1																PART 1							PART 2													PART 3
2
3	43	65	98	70		32	56	71	52		78	92	23	18		51	71	64	46.7		58.17		59.17	58.92	53.92	55.25	60.54	60.04	57.29	57.50	54.79	55.54	63.29	61.75		63.29	61.75	60.54	60.04	59.17	58.92	57.50	57.29	55.54	55.25	54.79	53.92
4	68	70	43	45		33	76	51	76		50	57	62	40		50	68	52	53.7		55.92		53.71	55.63	57.04	57.00	58.08	54.88	56.04	53.13	55.96	57.25	54.67	57.63		58.08	57.63	57.25	57.04	57.00	56.04	55.96	55.63	54.88	54.67	53.71	53.13
5	38	73	40	68		60	24	29	59		60	50	60	39		53	49	43	55.3		50.00		51.83	47.00	50.38	48.42	49.08	53.13	51.75	49.54	49.08	49.88	47.88	52.04		53.13	52.04	51.83	51.75	50.38	49.88	49.54	49.08	49.08	48.42	47.88	47.00
6	75	68	67	67		65	38	59	38		23	75	77	77		54	60	68	60.7		60.75		58.17	59.79	60.83	59.96	59.42	63.54	61.83	63.58	64.67	58.92	59.58	58.71		64.67	63.58	63.54	61.83	60.83	59.96	59.79	59.58	59.42	58.92	58.71	58.17
7	53	57	33	46		35	68	59	38		22	58	52	52		37	61	48	45.3		47.75		45.71	48.25	49.63	47.67	47.96	46.88	46.38	48.67	49.58	48.13	47.25	46.92		49.63	49.58	48.67	48.25	48.13	47.96	47.67	47.25	46.92	46.88	46.38	45.71
8	48	74	58	56		25	28	70	22		42	78	48	79		38	60	59	52.3		52.33		51.13	50.58	52.42	51.88	54.00	56.33	50.92	56.13	51.88	50.08	53.67	49.00		56.33	56.13	54.00	53.67	52.42	51.88	51.88	51.13	50.92	50.58	50.08	49.00
9	70	57	59	58		66	72	26	34		61	26	35	52		66	52	40	48.0		51.33		50.79	50.67	48.96	50.08	51.29	48.79	53.08	53.08	51.92	54.54	51.96	50.83		54.54	53.08	53.08	51.96	51.92	51.29	50.83	50.79	50.67	50.08	48.96	48.79
Sheet2

Cell Formulas
Range	Formula
Q3:T9	Q3	=AVERAGE(B3,G3,L3)
X3:AI9	X3	=(SUM($Q3:$T3)-INDEX($Q3:$T3,MOD(COLUMNS($A:A)-1,4)+1)+(INDEX($Q3:$T3,MOD(COLUMNS($A:A)-1,4)+1)*3-INDEX($B3:$O3,COLUMNS($A:A)+INT((COLUMNS($A:A)-1)/4)))/2)/4
AK3:AV9	AK3	=LARGE($X3:$AI3,COLUMNS($A:A))
L4:O9,G4:J9,B4:E9	B4	=RANDBETWEEN(20,80)
V3:V9	V3	=AVERAGE(Q3:T3)

 

[Rnkhch]

Awesome, thanks much! I'll apply this to my work data tomorrow at the office and will let you know if I have any other questions.

 

[StephenCrump]

As you suggested, could you please show me the SORT as well (preferably horizontally ?)

Using the layout in Post #2, try:

=LET(nos,FILTER(J2:W2,J2:W2<>""),wts,{0;1;1;1;1.5;1;1;1;1.5;1;1;1},SORT(MMULT(SEQUENCE(,12,,0),wts*INDEX(nos,MOD(SEQUENCE(12)+SEQUENCE(,12)-2,12)+1))/12,,-1,1))

 

[StephenCrump]

BTW, you could simplify Part 1. As your groups are the same size, the result you're getting is just the simple average of all 12 numbers.

 

[Rnkhch]

Wow, thanks, your post #6 formula is incredible! It spreads across ?

You brought up a good point regarding same group size. I may have some data in which the groups might be a bit more irregular. I'll play around with them too tomorrow and see how thy come around, and I might need your help with them

 

[Rnkhch]

It worked really well on the data that was organized nicely in three sets ??? and the spilling was fabulous! Now I just need to take care of some data that are not as nicely organized:

A. Most of the data actually contains 12 sets of 4 numbers (so between 3 and 12 sets of 4 numbers)
B. In some sets, one or more of the four numbers are missing. (Sometimes all 4 numbers in a set are missing). I noticed that the missing cells cause the REF error in your function (from post #6)?
(C. In some data, the in between blank columns are not there, and all sets of (available) four numbers are stacked together continuously. I noticed that your formula already automatically takes care of this by the FILTER you have implemented, which is fantastic.)

So I just need to take care of A and B, and I would appreciate your help. If you could modify your post #6 such that it can take care of 12 sets of 4 numbers and any missing numbers in there (to avoid #REF), it will be fabulous! Thank you so much!!

 

[Rnkhch]

I tried to tackle this myself, but it is it a bit lengthy ?

1) First, I generated 12 sets of 4 numbers some of which are missing:

TestSort.xlsx
	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	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX
1
2	num. sets	3	23		35	27	3	71		41				67	13	19		47	61	78				8	35			71	3	7	48		33	11	71	24	71					36	46		62	22	44	13	14
3
Sheet1

2) Then I extracted all the first, second, third, and fourth numbers making sure I get 'blank' cells for missing values:

TestSort.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
3
4	1sts	3	27	41	67	47			7	11		36	22
5	2nds	23	3		13	61			48	71		46	44
6	3rds		71		19	78	8	71		24			13
7	4ths	35					35	3	33	71		62	14
8
Sheet1

Cell Formulas
Range	Formula
B4:M4	B4	=IF((((INDEX(B2:AW2,1,SEQUENCE(,12,1,4)))/1)=0),(""),((INDEX(B2:AW2,1,SEQUENCE(,12,1,4)))/1))
B5:M5	B5	=IF((((INDEX(B2:AW2,1,SEQUENCE(,12,2,4)))/1)=0),(""),((INDEX(B2:AW2,1,SEQUENCE(,12,2,4)))/1))
B6:M6	B6	=IF((((INDEX(B2:AW2,1,SEQUENCE(,12,3,4)))/1)=0),(""),((INDEX(B2:AW2,1,SEQUENCE(,12,3,4)))/1))
B7:M7	B7	=IF((((INDEX(B2:AW2,1,SEQUENCE(,12,4,4)))/1)=0),(""),((INDEX(B2:AW2,1,SEQUENCE(,12,4,4)))/1))
Dynamic array formulas.

3) Then, I wrote an array of 0s and 1s for excluding each number:

TestSort.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
8
9	excluder	0	1	1	1	1	1	1	1	1	1	1	1
10		1	0	1	1	1	1	1	1	1	1	1	1
11		1	1	0	1	1	1	1	1	1	1	1	1
12		1	1	1	0	1	1	1	1	1	1	1	1
13		1	1	1	1	0	1	1	1	1	1	1	1
14		1	1	1	1	1	0	1	1	1	1	1	1
15		1	1	1	1	1	1	0	1	1	1	1	1
16		1	1	1	1	1	1	1	0	1	1	1	1
17		1	1	1	1	1	1	1	1	0	1	1	1
18		1	1	1	1	1	1	1	1	1	0	1	1
19		1	1	1	1	1	1	1	1	1	1	0	1
20		1	1	1	1	1	1	1	1	1	1	1	0
21
Sheet1

4) Then, I multiplied each of the sets of numbers from step 2 by the exclusion array again making sure to get blank cells for 0s and errors and manually calculated the average of each row in the rightmost column:

TestSort.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
21
22	1sts		27	41	67	47			7	11		36	22	32.25
23		3		41	67	47			7	11		36	22	29.25
24		3	27		67	47			7	11		36	22	27.5
25		3	27	41		47			7	11		36	22	24.25
26		3	27	41	67				7	11		36	22	26.75
27		3	27	41	67	47			7	11		36	22	29
28		3	27	41	67	47			7	11		36	22	29
29		3	27	41	67	47				11		36	22	31.75
30		3	27	41	67	47			7			36	22	31.25
31		3	27	41	67	47			7	11		36	22	29
32		3	27	41	67	47			7	11			22	28.125
33		3	27	41	67	47			7	11		36		29.875
34	2nds		3		13	61			48	71		46	44	40.85714
35		23			13	61			48	71		46	44	43.71429
36		23	3		13	61			48	71		46	44	38.625
37		23	3			61			48	71		46	44	42.28571
38		23	3		13				48	71		46	44	35.42857
39		23	3		13	61			48	71		46	44	38.625
40		23	3		13	61			48	71		46	44	38.625
41		23	3		13	61				71		46	44	37.28571
42		23	3		13	61			48			46	44	34
43		23	3		13	61			48	71		46	44	38.625
44		23	3		13	61			48	71			44	37.57143
45		23	3		13	61			48	71		46		37.85714
46	3rds		71		19	78	8	71		24			13	40.57143
47					19	78	8	71		24			13	35.5
48			71		19	78	8	71		24			13	40.57143
49			71			78	8	71		24			13	44.16667
50			71		19		8	71		24			13	34.33333
51			71		19	78		71		24			13	46
52			71		19	78	8			24			13	35.5
53			71		19	78	8	71		24			13	40.57143
54			71		19	78	8	71					13	43.33333
55			71		19	78	8	71		24			13	40.57143
56			71		19	78	8	71		24			13	40.57143
57			71		19	78	8	71		24				45.16667
58	4ths						35	3	33	71		62	14	36.33333
59		35					35	3	33	71		62	14	36.14286
60		35					35	3	33	71		62	14	36.14286
61		35					35	3	33	71		62	14	36.14286
62		35					35	3	33	71		62	14	36.14286
63		35						3	33	71		62	14	36.33333
64		35					35		33	71		62	14	41.66667
65		35					35	3		71		62	14	36.66667
66		35					35	3	33			62	14	30.33333
67		35					35	3	33	71		62	14	36.14286
68		35					35	3	33	71			14	31.83333
69		35					35	3	33	71		62		39.83333
70
Sheet1

Cell Formulas
Range	Formula
B22:M33	B22	=IFERROR((IF(((B$4*B9)=0),(""),(B$4*B9))),(""))
N22:N69	N22	=AVERAGE(B22:M22)
B34:M45	B34	=IFERROR((IF(((B$5*B9)=0),(""),(B$5*B9))),(""))
B46:M57	B46	=IFERROR((IF(((B$6*B9)=0),(""),(B$6*B9))),(""))
B58:M69	B58	=IFERROR((IF(((B$7*B9)=0),(""),(B$7*B9))),(""))

5) Finally I transposed the averages column and sorted it in one step:

TestSort.xlsx
	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	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX
70
71	sort. horiz. avg.	46	45.16667	44.16667	43.71429	43.33333	42.28571	41.66667	40.85714	40.57143	40.57143	40.57143	40.57143	40.57143	39.83333	38.625	38.625	38.625	38.625	37.85714	37.57143	37.28571	36.66667	36.33333	36.33333	36.14286	36.14286	36.14286	36.14286	36.14286	35.5	35.5	35.42857	34.33333	34	32.25	31.83333	31.75	31.25	30.33333	29.875	29.25	29	29	29	28.125	27.5	26.75	24.25
72
Sheet1

Cell Formulas
Range	Formula
B71:AW71	B71	=TRANSPOSE(SORT(N22:N69,,-1,))
Dynamic array formulas.

My experience with arrays and their operations is not that high, so I'm hoping someone can condense all these operations in one or as few formulas as possible. Otherwise it will be cumbrome to write this for each of my many rows of numbers.

Thanks much for any input!