Average multiple columns and return column header with highest average

JamesonMH

Board Regular
Joined
Apr 17, 2018
Messages
111
Office Version
  1. 365
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

MorningAfternoonEvening
942
813
1021
811
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,918
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 

Some videos you may like

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
Joined
Oct 24, 2015
Messages
7,132
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}}),
    List = Table.AddColumn(Group, "Header", each [Count][Header]),
    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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,652
Messages
5,549,210
Members
410,905
Latest member
Extjel
Top