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 •


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

=A2>=LARGE($A$2:$A$10,5)

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 •

#2
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
'

'
Range("A1:A100").Select
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("B18").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-17]C:R[-13]C)"
Range("B17").Select
ActiveCell.FormulaR1C1 = "Average of TOP 5 is"
Range("B17,B18").Select
Range("B18").Activate
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Range("D13").Select
End Sub


Report •

#3
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

#4
March 26, 2009 at 06:18:38
or

=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