Average multiple columns and return column header with highest average

JamesonMH

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

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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
 
Upvote 0
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?
 
Upvote 0
Sorry mate, I don't use formula or vba
maybe someone else will give formula solution
have a nice day
 
Upvote 0
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!
 
Upvote 0
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’)
 
Upvote 0
  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.
 
Upvote 0
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))
 
Upvote 0
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) )
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top