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?

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.

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

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... :)

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.

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History