Average of top 5 highest values

March 25, 2009 at 04:39:15
Specs: Windows XP SP2
Hi all, I need an Excel formula that will look at a list of cells of different values, find the top 5, colour the cell yellow, and give me an average over those 5 values. Underschtooken?

See More: Average of top 5 highest values

Report •

March 25, 2009 at 07:15:02
ok so your formula for the conditional formatting is


To get the average of the top 5 values, you'd need to go

=LARGE($A$2:$A$10,1)+LARGE($A$2:$A$10,2)+LARGE etc...

then divide it by 5.

Report •

March 25, 2009 at 07:53:51
This isn't pretty but it may suit your purpose.

Sub AverageTop5ColumnA()
' Procedure : AverageTop5ColumnA
' Author : Orange
' Created : 3/25/2009
' Purpose : This proc will find the Average of the top 5 values in Column A,
' and put the answer in Col B Row 18. Then color the answer cell Yellow.
' Last Modified:
' Inputs: N/A
' Dependency: N/A
' Macro recorded 3/25/2009 by NewAdmin

Application.CutCopyMode = False
Selection.Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-17]C:R[-13]C)"
ActiveCell.FormulaR1C1 = "Average of TOP 5 is"
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Sub

Report •

March 25, 2009 at 15:28:15
Thanks Jon_k, the Large formula was exactly what I needed.
Thanks Orange for your work too, but the idea of turning the cells yellow was to let me know where amongst all the values the top 5 were, so it was intended that the cells that contained the top 5 scores were yellow, not the average score of all of them... :)

Report •

Related Solutions

March 26, 2009 at 06:18:38

=AVERAGE(LARGE($A$2:$A$10,1),LARGE($A$2:$A$10,2),LARGE etc...))

I only suggest this since it doesn't take too much energy to figure out what the formula is doing.

If you are looking at Jon's "divide by 5" formula a year from now or if someone else is looking at the formula, it might be difficult to quickly determine what you were trying to do.

Report •

Ask Question