# Average multiple columns and return column header with highest average

#### JamesonMH

##### Board Regular
Hi all,

I need some help. I have 3 columns containing random values. Each column has a header. I want a formula that'll take the average of each column and return the header name for whichever is highest.

E.g. Below, I'd want to to return "Morning" since it has the highest average. Note, I know if the average was shown at the bottom of each column I could simply use INDEX/MATCH/MAX, but that isn't an option here. Any help would be super appreciated. Thanks folks.
James

 Morning Afternoon Evening 9 4 2 8 1 3 10 2 1 8 1 1

#### Peter_SSs

##### MrExcel MVP, Moderator
You're welcome. Thanks for the follow-up.

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### sandy666

##### Well-known Member
just for fun for someone else
Power Query:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
UNP = Table.UnpivotOtherColumns(Source, {}, "Header", "Value"),
GroupAvg = Table.Group(UNP, {"Header"}, {{"Avg", each List.Average([Value]), type number}}),
SortDsc = Table.Sort(GroupAvg,{{"Avg", Order.Descending}}),
Group = Table.Group(SortDsc, {"Avg"}, {{"Count", each _, type table}}),
Extract = Table.TransformColumns(List, {"Header", each Text.Combine(List.Transform(_, Text.From), " | "), type text}),
Max = List.Max(Extract[Avg]),
FilterMax = Table.SelectRows(Extract, each [Avg] = Max)
in
FilterMax``````
tolerates duplicates, extra columns and rows

Replies
2
Views
93
Replies
0
Views
115
Replies
5
Views
72
Replies
14
Views
131
Replies
11
Views
165