Get part of field name for highest value

  • Thread starter Thread starter Lorna_Jane
  • Start date Start date
L

Lorna_Jane

Hello, I have a spreadsheet which looks like the following:
AF AG AH
PercentRegion1 PercentRegion2 PercentRegion3 <-- Column header
100 0 0
25 75 0
1 0 99

I would like to add another field which would be called "dominant region"
and would be populated with the region number of the highest percentage i.e.
1
2
3

Any help is appreciated
 
Hello, I have a spreadsheet which looks like the following:
AF AG AH
PercentRegion1 PercentRegion2 PercentRegion3 <-- Column header
100 0 0
25 75 0
1 0 99

I would like to add another field which would be called "dominant region"
and would be populated with the region number of the highest percentage i.e.
1
2
3

Any help is appreciated
Click to expand...

Since your regions are listed in order:

=MATCH(MAX(AF2:AH2),AF2:AH2,0)

If, on the other hand, you want everything after "region" in the column header,
then:

=MID(INDEX($AF$1:$AH$1,1,MATCH(MAX(AF2:AH2),AF2:AH2,0)),
SEARCH("region",INDEX($AF$1:$AH$1,1,MATCH(MAX(AF2:AH2),AF2:AH2,0)))+6,99)

--ron
 
In case of a tie for highest value, this formula will favor the lower number
(if 1 and 3 are tied, formula returns 1).

=MATCH(AF2:AH2,MAX(AF2:AH2),0)
 
Back
Top