Average of Highest values

D

Damo

I Have 12 months sales history on an item. rather than work out the average
over the 12 months data, I wont to enter a formula that will give me the
average of the 6 highest selling months. Is there a way I can do that? I
have my data set out in rows with calendar month headings across the top.
 
D

dleo

Use a nested formula.

=AVERAGE(MAX(A2:L2),LARGE(A2:L2,2),LARGE(A2:L2,3),LARGE(A2:L2,4),LARGE(A2:L2,5),LARGE(A2:L2,6))

where MAX finds the largest and LARGE finds the kth average like
LARGE(range,k)

Then drag your formula down and you're set!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top