need a formula to tell me the average bottom 50%

**padcfc** · 09-16-2011, 09:21 AM

How do i write a formula that will tell me the value of the average bottom 50% without having to individually typing out every number (so instead of typing 1,2,3?)

This is what i have so far?

£1 answer £441
£341
£349
£445
£504
£571
£622
£698
£758
£805
£827
£845
£855
£863
£929
£936

**Miraun** · 09-16-2011, 09:25 AM

=Quartile(Array,1)

Nevermind, that's wrong... one moment.

**Andrew-R** · 09-16-2011, 09:26 AM

How about =SUMPRODUCT(A1:A16,--(A1:A16<=SMALL(A1:A16,INT(COUNTA(A1:A16)/2))))/COUNTIF(A1:A16,"<=" & SMALL(A1:A16,INT(COUNTA(A1:A16)/2)))

That seems to return the answer you're looking for.

**padcfc** · 09-16-2011, 09:32 AM

Andrew-R - that works perfectly thank you so much

**Andrew-R** · 09-16-2011, 09:33 AM

No problems, glad we got there in the end

**daddylonglegs** · 09-16-2011, 09:40 AM

You could get the same result with an "array formula", i.e.

=AVERAGE(IF(A1:A16<=SMALL(A1:A16,INT(COUNT(A1:A16)/2)),A1:A16))

confirmed with CTRL+SHIFT+ENTER

but note that like Andrew's suggestion that might actually average more than 8 values (out of 16) if you have ties around the cut-off point. To always average exactly 8 values, ties or not, this version can be used

=AVERAGE(SMALL(A1:A16,ROW(INDIRECT("1:"&INT(COUNT(A1:A16)/2)))))

...also array entered