Find Average, Return Column Header of Number Closest To Average

With a table like the one shown below, how can I find the average of each row, then determine which column header comes closest to the average?

In other words, assuming rows A:E contain the values shown below, what formula would I use in row F to get the column header above the number that comes closest to the average for that row?

1BR 2BR 3BR 4BR
Fruitridge 1 2 3 4
Grapeview 5 6 7 8
Orangevale 1 2 3 4
Appleton 5 6 7 8
Fruitridge 1 2 3 4
Grapeview 5 6 7 8
Orangevale 1 2 3 4
Appleton 5 6 7 8

Thanks!

Thanks for asking that, martindwilson!

I actually was going to mention that in my original post, but thought it might be complicating the question.

My preference is to "round up" so that the result, if stuck between columns, defaults to the column on the right.

Hi Tony,

I probably should have posted a more "real world" example where the averages don't fall "between columns" (so to speak) very frequently.

As I mentioned just above, I'd like to default to the column to the right when the average is at a mid-point between two columns, unless there's some kind of magical way to return a value that indicates the average fell exactly between two columns, and indicates which those two columns are.

Thanks!

Tony,

Your formula seemed to work for the example I posted, but I can seem to make it work on this more realistic table:

B Col Header C Col Header D Col Header E Col Header
Row 2 5.05 5.23 5.35 5.54
Row 3 6.78 7.33 8.42 8.81
Row 4 7.69 8.13 9.06 9.55
Row 5 5.32 5.44 5.84 7.48
Row 6 6.95 7.55 8.55 9.01
Row 7 7.94 8.16 9.20 9.82
Row 8 5.05 5.23 5.35 5.54
Row 9 5.49 6.39 7.36 7.95
Row 10 7.27 7.75 8.70 9.22
Row 11 5.32 5.44 5.84 7.48
Row 12 6.16 7.25 8.10 8.47
Row 13 7.54 7.94 8.93 9.39

Thanks, Tony. That formula certainly seems to do as I asked.

I've tried to modify it so that it will display the column header of the number closest to average. If that number is lower, the column header on the left would show. Can't seem to make that work.

Any suggestions?

Thanks Biff / Tony.

(Don't know whether to address you by your signature or user name.)

I'll be able to post a small realistic example on Monday. (Unfortunately, I left that worksheet at work.)

Kind regards,

Dave Myers

In the meantime, attached is a worksheet that shows the example table in columns A:E.

Column F shows the average for each row.

Column G shows the value found in A:E that comes closest to the average value for that row.

Column H displays the column header that corresponds to the column where the value closest to average was found.

What I'm trying to do is roll all this into one formula so that it becomes a one-step process.

Tony / Biff,

Works like a champ!!

Thanks so much!

Kind regards,

Dave Myers