Sum/Product/Average of a group of data

[0-0 Wai Wai ^-^]

Hi.
Does anyone know if there is a function which can do the following:
- calculation (eg sum/product/average) of largest 6, for example, of the whole
data
- calculation (eg sum/product/average) of smallest 6, for example, of the whole
data
- calculation (eg sum/product/average) of a specific range (eg from 3th largest
to 8th largest, from 3th smallest to 8th smallest) of the whole data
???

 

[KL]

Hi,

Try these:

=SUMPRODUCT(LARGE(A1:A20,{1,2,3,4,5,6}))
=PRODUCT(LARGE(A1:A20,{1,2,3,4,5,6}))
=AVERAGE(LARGE(A1:A20,{1,2,3,4,5,6}))

=SUMPRODUCT(SMALL(A1:A20,{1,2,3,4,5,6}))
=PRODUCT(SMALL(A1:A20,{1,2,3,4,5,6}))
=AVERAGE(SMALL(A1:A20,{1,2,3,4,5,6}))

you can allso use ROW(INIRECT()) instead of the fixed array to return the
2nd argument for the functions LARGE and SMALL, e.g.:

=SUMPRODUCT(LARGE(A1:A20,ROW(INDIRECT("3:8"))))
=SUMPRODUCT(SMALL(A1:A20,ROW(INDIRECT("3:8"))))

Regards,
KL