We help IT Professionals succeed at work.

# AVERAGE(LARGE(D1:D7, {1,2,3})) - D1:D7 should not be hardcoded - but based on a validation

on
Hi Team,
I'm stuck again. Looking forward to your helping hand.
What it is, i have this Formulat AVERAGE(LARGE(D1:D7, {1,2,3})).
The Column a1:a7 has years from 2002 and 2003 (asc order, repeating).
This formula works fine if all the years are same. Since we have 2002 and 2003 and will be many more, I need a formula that could set the D1:D7 to D1:D3 or to whatever rows it is 2002.
In user english, It is to find out the average of top three marks in a particular subject since 2000 to 2010. Number of students vary every year or it could be same for few years. The formula should pick one year and find the top 3 marks and find the average. Right now i set the range for each year. Please help this out. I know i could do this via VBA but the requirement is a formula. Thanks a million!
Comment
Watch Question

## View Solution Only

Commented:
can you post an example sheet ?
Most Valuable Expert 2012
Top Expert 2012

Commented:
The formula you have finds the average of the top 3 numbers in D1:D7.

What I'm not understanding is what you mean by:   I need a formula that could set the D1:D7 to D1:D3 or to whatever rows it is 2002.

>> find out the average of top three marks in a particular subject since 2000 to 2010

Are you trying to find the average in the 2000 to 2010 timeframe?  where your data has other years?

Can you provide a couple examples that clarify your request?

Dave

Commented:
the formula takes the 3 largest values from cells D1 ~ D7 and displays the average value of this top3. It works if all the years are the same, but also if different years are used in cells from the D column.

this should be more or less what you are looking for but it is not very clear to me how you have arranged the dates / subjects etc.
When you would be able to post an example worksheet then we could figure out the best way of having the averaged top 3 years.
Most Valuable Expert 2012
Top Expert 2012

Commented:
If you're trying to set some criteria for the timeframe - e.g., you want the top three years where the years are in the 2000 to 2010 timeframe, then you can use this formula:

=AVERAGE(LARGE((D1:D7<=2010)*(D1:D7>=2000)*(D1:D7),{1,2,3}))

Hit CTRL-SHIFT-ENTER after putting the formula in - paste the formula then hit F2 to enter EDIT mode, then hit CTRL-SHIFT-ENTER to confirm.

See attached.

PS - if this is NOT what you're looking for - clarifying examples would help.

Dave
avgLarge-r1.xls

Commented:
Dave,

I currently assume that the author is looking for average top 3 number of students from column D, and would like this for all distinct years that are stored in column A.
author currently must manually update the range of the 'average top 3' formula to generate the results for a particular year.

When author posts an example this indeed should clarify a great deal and help a lot.
Most Valuable Expert 2012
Top Expert 2012

Commented:
Agreed

Commented:
Here is the sample sheet. Thanks very much.
Sorry about the confusion. Had the same feedback on clarity when explained it to my peers.
SampleWorksheet.xlsx
Most Valuable Expert 2012
Top Expert 2012

Commented:
Put this formula in G5:

=AVERAGEIFS(\$D\$4:\$D\$45,\$A\$4:\$A\$45,\$F5)

and copy down

See attached.

Dave
SampleWorksheet-1-.xlsx
Most Valuable Expert 2012
Top Expert 2012

Commented:
AVERAGEIFS function
Show AllHide All
Returns the average (arithmetic mean) of all cells that meet multiple criteria.

Syntax

AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2…)

Average_range     is one or more cells to average, including numbers or names, arrays, or references that contain numbers.

Criteria_range1, criteria_range2, …     are 1 to 127 ranges in which to evaluate the associated criteria.

Criteria1, criteria2, …     are 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be averaged. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.

=AVERAGEIFS(Give me the average of this range,Look at this range,and only give me items from this range based on this criteria)
BRONZE EXPERT
Most Valuable Expert 2013
Commented:
But isn't it the average of the top 3 in each year, Dave?

For that try this formula in G5 confirmed with CTRL+SHIFT+ENTER and copied down

=AVERAGE(LARGE(IF(A\$4:A\$45=F5,D\$4:D\$45),{1,2,3}))

see attached

regards, barry

barry-average.xlsx
Most Valuable Expert 2012
Top Expert 2012

Commented:
You are correct, sir!  I lost the concept of "top 3 years" this deep in the thread.

going for coffee!

great catch.

Dave

Commented:
i'm yet to apply the solutions provided. meanwhile attempting to clear this.

it is Average of Top 3 Marks each year and not the year itself. the column for data manipulation is marks (D column).

Thanks much. I'm overwhelmed with the instant responses. Thanks a lot.
Most Valuable Expert 2012
Top Expert 2012

Commented:
barry's solution should work for you.

Cheers,

Dave

Commented:
You 'ARE' a GENIUS. I never thought IF statement could work this different passed to a function. WOW!!! I was about to go through all the 400 formulae in Excel 2010 to try combinations. But never could have achieved this result.
Again, you are a GENIUS. I will download your knowledge to my Brain if StarGate-SG1 is real :)
Most Valuable Expert 2012
Top Expert 2012

Commented:
you ought to see barry when he uses both hands :)

Dave