Replace a derived value with a label
 

I need to have the maximum value in a column replaced with it's ocncordant
label.

For example in the following table I need to see Alberta rather than the MAX
value underneath the Jan Column:

Sales by Region

Jan Feb Mar Apr May
BC 45600 50000 48000 52600 54700
Manitoba 45600 44000 48000 48000 46500
Ontario 45650 49000 56000 64500 58700
Saskatchewan 45900 45000 45600 44100 38900
Alberta 55700 48000 51000 54500 54600

Replace a derived value with a label
 

Ok... here is a possible solution.

Assuming your month headers start in B1, and your row headers start in A2
this formula will work.

=IF(MAX(B2:B6)=B2,$A$2,(IF(MAX(B2:B6)=B3,$A$3,(IF( MAX(B2:B6)=B4,$A$4,(IF(MAX(B2:B6)=B5,$A$5,(IF(MAX( B2:B6)=B6,$A$6,"")))))))))

It needs to go at the bottom of column B, and if you have more rows of data
then you need to add the IF component for each new row. You can then copy
the formula across to the other columns so that you potentially can have a
different MAX province each month. The other time an issue will arrive is if
there is a tie for the max value. I believe Excel will populate the cell
with the first one it comes to.

Hope this helps.


"Chris" wrote:

I need to have the maximum value in a column replaced with it's ocncordant
label.

For example in the following table I need to see Alberta rather than the MAX
value underneath the Jan Column:

Sales by Region

Jan Feb Mar Apr May
BC 45600 50000 48000 52600 54700
Manitoba 45600 44000 48000 48000 46500
Ontario 45650 49000 56000 64500 58700
Saskatchewan 45900 45000 45600 44100 38900
Alberta 55700 48000 51000 54500 54600

Replace a derived value with a label
 

Hi Chris

try
=INDEX($A:$A,MATCH(MAX(B:B),B:B,0))

If there are ties for Max value, then the first value's location will be the
one shown.
--

Regards
Roger Govier

"Chris" wrote in message
...
I need to have the maximum value in a column replaced with it's ocncordant
label.

For example in the following table I need to see Alberta rather than the
MAX
value underneath the Jan Column:

Sales by Region

Jan Feb Mar Apr May
BC 45600 50000 48000 52600 54700
Manitoba 45600 44000 48000 48000 46500
Ontario 45650 49000 56000 64500 58700
Saskatchewan 45900 45000 45600 44100 38900
Alberta 55700 48000 51000 54500 54600