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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,087
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UOC = Table.UnpivotOtherColumns(Source, {}, "Header", "Value"),
    Group = Table.Group(UOC, {"Header"}, {{"Avg", each List.Average([Value]), type number}}),
    SortDsc = Table.Sort(Group,{{"Avg", Order.Descending}}),
    FirstRow = Table.FirstN(SortDsc,1)
in
    FirstRow
MorningAfternoonEveningHeaderAvg
942Morning8.75
813
1021
811
 

JamesonMH

Board Regular
Joined
Apr 17, 2018
Messages
111
Office Version
  1. 365
Thanks for a solution that clearly works. I was hoping for a non-PQ and non-VBA solution though as quite a few different users (with different Excel versions) will be using my sheet.

Any ideas?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,087
Sorry mate, I don't use formula or vba
maybe someone else will give formula solution
have a nice day
 

JamesonMH

Board Regular
Joined
Apr 17, 2018
Messages
111
Office Version
  1. 365

ADVERTISEMENT

Sorry mate, I don't use formula or vba
maybe someone else will give formula solution
have a nice day
No worries. Again, thank you for your help I'm sure I'll reference your approach in the future!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,881
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

that isn't an option here.
  1. Can you explain why not?
  2. If not, could you insert a new row at the top with the average & then hide that new row?
  3. What result do you want if more than one column has equal highest average?
  4. I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 

JamesonMH

Board Regular
Joined
Apr 17, 2018
Messages
111
Office Version
  1. 365
  1. Can you explain why not?
  2. If not, could you insert a new row at the top with the average & then hide that new row?
  3. What result do you want if more than one column has equal highest average?
  4. I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
1. Because that's not the way I want my report to look ... many solution posts here on mrexcel wouldn't exist if people simply added helper rows/columns or subtotals; but I find that isn't always practical, nor the cleanest solution when a single cell formula may exist. I noted in my original question I already know how to get the solution with just 3 functions if I had a row for the averages.
2. Again, I could do that but I was hoping for a formula solution. Any ideas?
3. Fair question. But my actual dataset is thousands of rows, so 2 identical averages wouldn't occur, when decimals are factored in.
4. I didn't know about that, I'll review those settings and adjust as needed. Thanks for the tip.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,881
Office Version
  1. 365
Platform
  1. Windows
1. Because that's not the way I want my report to look ... many solution posts here on mrexcel wouldn't exist if people simply added helper rows/columns or subtotals; but I find that isn't always practical, nor the cleanest solution when a single cell formula may exist. I noted in my original question I already know how to get the solution with just 3 functions if I had a row for the averages.
Fair enough - just asking. You had said it wasn't an option which I interpreted to be not the same as "it's not my preference".

2. Again, I could do that but I was hoping for a formula solution.
It would be a formula solution if you stored the averages somewhere (new row 1, unused hidden columns AA:AC etc) & used your index/match and it would look exactly as you want.

2... a formula solution. Any ideas?
If you really want a single formula solution and you do only have 3 columns, then you could try something like ..

Excel Formula:
=IF(AVERAGE(A2:A100)=MAX(AVERAGE(A2:A100),AVERAGE(B2:B100),AVERAGE(C2:C100)),A1,IF(AVERAGE(B2:B100)>AVERAGE(C2:C100),B1,C1))
 

JamesonMH

Board Regular
Joined
Apr 17, 2018
Messages
111
Office Version
  1. 365
Excel Formula:
=IF(AVERAGE(A2:A100)=MAX(AVERAGE(A2:A100),AVERAGE(B2:B100),AVERAGE(C2:C100)),A1,IF(AVERAGE(B2:B100)>AVERAGE(C2:C100),B1,C1))
That seems to do the trick, thanks Peter!
Yes, I'll only have 3 columns for now but I can see why a formula like this wouldn't be ideal in this case if there were dozens of columns :S
I was wasting too much time trying (unsuccessfully) to work with INDEX for a possible solution, but you got right to it. It's not a complex problem by any means - I'm surprised I haven't run into this challenge before ... I wonder if there are other formula approaches that could handle many columns (if my spreadsheet changed one day) ... anyway, not important right now since you answered my question.
Thanks again for your help (and I just updated my profile Office version (y) )
 

Watch MrExcel Video

Forum statistics

Threads
1,114,108
Messages
5,545,987
Members
410,718
Latest member
ALM1GHTY
Top