# 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

#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").SelectEnd 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 •