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,871
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,086
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,069
Messages
5,545,803
Members
410,708
Latest member
SanTrapGamer
Top