View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Mzo Mzo is offline
external usenet poster
 
Posts: 1
Default Auto calc. the bottom 20% with a twist...

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(COU NT(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.
.....