| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Get part of field name for highest value
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Get part of field name for highest value
On Thu, 3 Dec 2009 11:31:01 -0800, Lorna_Jane
wrote: 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 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:AH 2,0)), SEARCH("region",INDEX($AF$1:$AH$1,1,MATCH(MAX(AF2: AH2),AF2:AH2,0)))+6,99) --ron |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Get part of field name for highest value
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) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Lorna_Jane" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Format part of text in concatenated field | Excel Worksheet Functions | |||
| Take part data from two fields and auto fill into 3rd field | Excel Worksheet Functions | |||
| How to make a field created a part of the Pivot Table Field List? | Excel Discussion (Misc queries) | |||
| how do I 'shadow rank' a number into a field it isn't a part of | Excel Worksheet Functions | |||
| Sum a $ amount in part of a field | Excel Discussion (Misc queries) | |||
Linear Mode
