Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Find Max and Min based on cell reference
I want to have a formula that finds the maximum value in a list. Easy enough
but I want to add a condition - the cell to the left has to equal a cell reference. So for example: If I enter the number 4 in a cell I want the formula to lookup the list (column B) and find all listings that have 4....then find the max value in column A. Its kind of like a maxif but I don't know if there is such a function? GK |
#2
|
|||
|
|||
=MAX(IF($B$2:$B$100=E4,$A$2:$A$100))
which must be confirmed with control+shift+enter instead of just with enter. E2 houses a criterion value like 4 that must hold for the range in B. gregork wrote: I want to have a formula that finds the maximum value in a list. Easy enough but I want to add a condition - the cell to the left has to equal a cell reference. So for example: If I enter the number 4 in a cell I want the formula to lookup the list (column B) and find all listings that have 4....then find the max value in column A. Its kind of like a maxif but I don't know if there is such a function? GK |
#3
|
|||
|
|||
And of course, we have the old "stand by" of *non-array* entered functions:
=SUMPRODUCT(MAX((B1:B100=D1)*A1:A100)) Where the number to lookup is entered in D1. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "gregork" wrote in message ... I want to have a formula that finds the maximum value in a list. Easy enough but I want to add a condition - the cell to the left has to equal a cell reference. So for example: If I enter the number 4 in a cell I want the formula to lookup the list (column B) and find all listings that have 4....then find the max value in column A. Its kind of like a maxif but I don't know if there is such a function? GK |
#4
|
|||
|
|||
Thanks Aladin.....exactly what I was after.
cheers GK "Aladin Akyurek" wrote in message ... =MAX(IF($B$2:$B$100=E4,$A$2:$A$100)) which must be confirmed with control+shift+enter instead of just with enter. E2 houses a criterion value like 4 that must hold for the range in B. gregork wrote: I want to have a formula that finds the maximum value in a list. Easy enough but I want to add a condition - the cell to the left has to equal a cell reference. So for example: If I enter the number 4 in a cell I want the formula to lookup the list (column B) and find all listings that have 4....then find the max value in column A. Its kind of like a maxif but I don't know if there is such a function? GK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|