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)
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!!!
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!!!
