# 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

### 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
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``````
 Morning Afternoon Evening Header Avg 9 4 2 Morning 8.75 8 1 3 10 2 1 8 1 1

#### JamesonMH

##### Board Regular
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
Sorry mate, I don't use formula or vba
maybe someone else will give formula solution
have a nice day

#### JamesonMH

##### Board Regular

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!

#### sandy666

##### Well-known Member
You are welcome
Thanks for the feedback

#### Peter_SSs

##### MrExcel MVP, Moderator

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
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
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
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 )

Replies
2
Views
86
Replies
0
Views
105
Replies
5
Views
72
Replies
14
Views
124
Replies
11
Views
161