Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adapting MAX function
I have created a forumla in excel which selects the the highest value in a
range based on certain criteria contained in a neighbouring cell, it is shown below:- =MAX(IF(YOT!$B$3:$B$150="England",(YOT!C$3:C$149)) ) Can this formula be adapted so it does not pick the highest value, but would pick up the row heading of the row with the highest value, in this example it would be the value held in cell A Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adapting MAX function
Try:
=INDEX($A$3:$A$150,MATCH(MAX(IF($B$3:$B$150="Engla nd",$C$3:$C$150)),$C$3:$C$150,0)) HTH "nir020" wrote: I have created a forumla in excel which selects the the highest value in a range based on certain criteria contained in a neighbouring cell, it is shown below:- =MAX(IF(YOT!$B$3:$B$150="England",(YOT!C$3:C$149)) ) Can this formula be adapted so it does not pick the highest value, but would pick up the row heading of the row with the highest value, in this example it would be the value held in cell A Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adapting MAX function
Assuming that the conditional MAX is unique for England and that it is
not the same as another country's, the following formula should produce the header: =INDEX(A$3:A$150,MATCH(MAX(IF(YOT!$B$3:$B$150="Eng land",YOT!C$3:C$149)),C$3:C$150,0) HTH Kostis Vezerides |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adapting MAX function
..... Enter with Control+Shift+enter
"nir020" wrote: I have created a forumla in excel which selects the the highest value in a range based on certain criteria contained in a neighbouring cell, it is shown below:- =MAX(IF(YOT!$B$3:$B$150="England",(YOT!C$3:C$149)) ) Can this formula be adapted so it does not pick the highest value, but would pick up the row heading of the row with the highest value, in this example it would be the value held in cell A Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adapting MAX function
Still array entered...
=INDEX(YOT!$A$3:$A$150,MATCH(MAX(IF(YOT!$B$3:$B$15 0="England",(YOT!C$3:C$149))),IF(YOT!$B$3:$B$150=" England",(YOT!C$3:C$149)),0)) -- Regards, Dave "nir020" wrote: I have created a forumla in excel which selects the the highest value in a range based on certain criteria contained in a neighbouring cell, it is shown below:- =MAX(IF(YOT!$B$3:$B$150="England",(YOT!C$3:C$149)) ) Can this formula be adapted so it does not pick the highest value, but would pick up the row heading of the row with the highest value, in this example it would be the value held in cell A Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need some comments on my Utility_Move class module. | Excel Worksheet Functions | |||
Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |