Auto calc. the bottom 20% with a twist...

M

Mzo

Here's what I'm working with.

Company C1 C2 C3 C4 C5 Ctotal
1 4.51 2.12 4.29 0.35 2.29 13.56
2 2.78 2.2 3.85 0.21 2.08 11.12
3 5.07 2.54 2.03 0.2 1.52 11.36
4 5.53 2.15 4.23 0.29 1.47 13.67
5 2.55 1.76 3 0.17 1.45 8.93
6 4.03 0.9 1.21 0.16 1.28 7.58
7 0.69 2.94 0.88 0.38 1.02 5.91
8 4.2 3.88 4.42 0.24 0.91 13.65
9 2.4 1.78 1.16 0.17 0.62 6.13
10 1.5 2.44 1.81 0.41 0.58 6.74
11 1.76 0.85 4.38 0.56 0.46 8.01
12 3.85 1.9 1.87 0.11 0.37 8.1
13 4.91 0.5 3.71 0.23 0.36 9.71
14 1.51 1.25 3.2 0.44 0.32 6.72
15 1.14 3.25 1.36 0.19 0.32 6.26
16 3.05 0.25 0.42 0.1 0.2 4.02
17 0.82 0.71 1.19 0.08 0.11 2.91
18 2.64 2.94 2.18 0.21 0.09 8.06
19 3 2.1 0.83 0.09 0.08 6.1


Target 1.890 1.500 0.830 0.163 0.353 4.735

Autocount/20% 1:4 1:4 1:4 1:4 1:4
AutoCalc 4.735

-------------------------
I'm trying to automatically calculate the average of the bottom 20% of
Ctotal. So as there are 19 companies I want the average of the 4
smallest costs, (5.91+4.02+2.91+6.1)/4 = 4.735 in this case company
7, 16, 17 and 19.

I've got "AutoCalc" working for Ctotal [col. G] with the formula:


{=AVERAGE(SMALL(G2:G20,ROW(INDIRECT("1:"&ROUND(COUNT(G2:G20)*0.2,0) ))))}

So my issue is, I want to now average the four values in C1 through
C5, using the same 4 companies that are in the total average.

So for C5, I'd like to automatically average 1.02, 0.2, 0.11 and 0.08
to obtain the target result of 0.353.
Any help is appreciated.
.....
 
K

Ken

Mzo

Try this formula in row 21 (with your data in A1:G20)

=SUMPRODUCT(--(MATCH($G$2:$G$20,SMALL($G$2:$G$20,{1;2;3;4;5}))<5),
(B2:B20))/4

This is what I got:

Company C1  C2  C3  C4  C5  Ctotal
1 4.51 2.12 4.29 0.35 2.29 13.56
2 2.78 2.2 3.85 0.21 2.08 11.12
3 5.07 2.54 2.03 0.2 1.52 11.36
4 5.53 2.15 4.23 0.29 1.47 13.67
5 2.55 1.76 3 0.17 1.45 8.93
6 4.03 0.9 1.21 0.16 1.28 7.58
7 0.69 2.94 0.88 0.38 1.02 5.91
8 4.2 3.88 4.42 0.24 0.91 13.65
9 2.4 1.78 1.16 0.17 0.62 6.13
10 1.5 2.44 1.81 0.41 0.58 6.74
11 1.76 0.85 4.38 0.56 0.46 8.01
12 3.85 1.9 1.87 0.11 0.37 8.1
13 4.91 0.5 3.71 0.23 0.36 9.71
14 1.51 1.25 3.2 0.44 0.32 6.72
15 1.14 3.25 1.36 0.19 0.32 6.26
16 3.05 0.25 0.42 0.1 0.2 4.02
17 0.82 0.71 1.19 0.08 0.11 2.91
18 2.64 2.94 2.18 0.21 0.09 8.06
19 3 2.1 0.83 0.09 0.08 6.1
1.89 1.5 0.83 0.1625 0.3525 4.735

You can build your indirect function back into the formula to
generalize you 20% calc if you need to.

Good luck

Ken
Norfolk, Va
 
Top