Home |
Search |
Today's Posts |
#1
|
|||
|
|||
HOW DO I GET THE EQUIVALENT OF MAXIF(RANGE,CRITERIA)?
HOW DO I GET THE EQUIVALENT OF MAXIF(RANGE,CRITERIA) &
HOW DO I GET THE EQUIVALENT OF MINIF(RANGE,CRITERIA)? i've tried loads of rubbish but cant get the answer i want. -- CHRISK |
#2
|
|||
|
|||
Here's one way:
In my example, I put letters down Col A and Values down Col B: =SUMPRODUCT(MAX((A2:A100="a")*(B2:B100))) You could also engage an autofilter and use the SUBTOTAL function to return the maximum visible value: =SUBTOTAL(4,range) Does that help? -- Regards, Ron |
#3
|
|||
|
|||
DONT SHOUT!
=MAX(IF(A1:A100<"value",B1:B100) which is an array formula, so commit with Ctrl-Shift-Enter. -- HTH Bob Phillips "CHRIS K" wrote in message ... HOW DO I GET THE EQUIVALENT OF MAXIF(RANGE,CRITERIA) & HOW DO I GET THE EQUIVALENT OF MINIF(RANGE,CRITERIA)? i've tried loads of rubbish but cant get the answer i want. -- CHRISK |
#4
|
|||
|
|||
Use:
=MAX(A1:A4*(B1:B4="a")) confirm with control - shift - enter And similar for min Mangesh "CHRIS K" wrote in message ... HOW DO I GET THE EQUIVALENT OF MAXIF(RANGE,CRITERIA) & HOW DO I GET THE EQUIVALENT OF MINIF(RANGE,CRITERIA)? i've tried loads of rubbish but cant get the answer i want. -- CHRISK |
#5
|
|||
|
|||
I put this in A1:B4
-1 a -2 b -3 a -4 a And got 0 back. So be careful with this one. Mangesh Yadav wrote: Use: =MAX(A1:A4*(B1:B4="a")) confirm with control - shift - enter And similar for min Mangesh "CHRIS K" wrote in message ... HOW DO I GET THE EQUIVALENT OF MAXIF(RANGE,CRITERIA) & HOW DO I GET THE EQUIVALENT OF MINIF(RANGE,CRITERIA)? i've tried loads of rubbish but cant get the answer i want. -- CHRISK -- Dave Peterson |
#6
|
|||
|
|||
The MAX works but the MIN returns 0
thanks -- CHRISK "Ron Coderre" wrote: Here's one way: In my example, I put letters down Col A and Values down Col B: =SUMPRODUCT(MAX((A2:A100="a")*(B2:B100))) You could also engage an autofilter and use the SUBTOTAL function to return the maximum visible value: =SUBTOTAL(4,range) Does that help? -- Regards, Ron |
#7
|
|||
|
|||
this works for half
what is the ctrl shift enter bit? -- CHRISK "Mangesh Yadav" wrote: Use: =MAX(A1:A4*(B1:B4="a")) confirm with control - shift - enter And similar for min Mangesh "CHRIS K" wrote in message ... HOW DO I GET THE EQUIVALENT OF MAXIF(RANGE,CRITERIA) & HOW DO I GET THE EQUIVALENT OF MINIF(RANGE,CRITERIA)? i've tried loads of rubbish but cant get the answer i want. -- CHRISK |
#8
|
|||
|
|||
sorry for the caps bob
cant get this one to work, was looking at the wrong one when i pressed the 'yes' button. this gives me 0 for everything Shall try harder to sort it thanks -- CHRISK "Bob Phillips" wrote: DONT SHOUT! =MAX(IF(A1:A100<"value",B1:B100) which is an array formula, so commit with Ctrl-Shift-Enter. -- HTH Bob Phillips "CHRIS K" wrote in message ... HOW DO I GET THE EQUIVALENT OF MAXIF(RANGE,CRITERIA) & HOW DO I GET THE EQUIVALENT OF MINIF(RANGE,CRITERIA)? i've tried loads of rubbish but cant get the answer i want. -- CHRISK |
#9
|
|||
|
|||
For MIN, try this:
=SUMPRODUCT(MIN(($A$2:$A$100="a")*($B$2:$B$100)+($ A$2:$A$100<"a")*10^10)) (The additions to the formula cause non-matches to equate to 100,000,000,000 instead of zero) -- Regards, Ron "CHRIS K" wrote: The MAX works but the MIN returns 0 thanks -- CHRISK "Ron Coderre" wrote: Here's one way: In my example, I put letters down Col A and Values down Col B: =SUMPRODUCT(MAX((A2:A100="a")*(B2:B100))) You could also engage an autofilter and use the SUBTOTAL function to return the maximum visible value: =SUBTOTAL(4,range) Does that help? -- Regards, Ron |
#10
|
|||
|
|||
Don't forget Ctrl-Shift-Enter.
-- HTH Bob Phillips "CHRIS K" wrote in message ... sorry for the caps bob cant get this one to work, was looking at the wrong one when i pressed the 'yes' button. this gives me 0 for everything Shall try harder to sort it thanks -- CHRISK "Bob Phillips" wrote: DONT SHOUT! =MAX(IF(A1:A100<"value",B1:B100) which is an array formula, so commit with Ctrl-Shift-Enter. -- HTH Bob Phillips "CHRIS K" wrote in message ... HOW DO I GET THE EQUIVALENT OF MAXIF(RANGE,CRITERIA) & HOW DO I GET THE EQUIVALENT OF MINIF(RANGE,CRITERIA)? i've tried loads of rubbish but cant get the answer i want. -- CHRISK |
#11
|
|||
|
|||
makes it an array formula, otherwise it doesn't work on the whole range.
-- HTH Bob Phillips "CHRIS K" wrote in message ... this works for half what is the ctrl shift enter bit? -- CHRISK "Mangesh Yadav" wrote: Use: =MAX(A1:A4*(B1:B4="a")) confirm with control - shift - enter And similar for min Mangesh "CHRIS K" wrote in message ... HOW DO I GET THE EQUIVALENT OF MAXIF(RANGE,CRITERIA) & HOW DO I GET THE EQUIVALENT OF MINIF(RANGE,CRITERIA)? i've tried loads of rubbish but cant get the answer i want. -- CHRISK |
#12
|
|||
|
|||
=MIN(IF(A2:A100="a",B2:B100))
array entered, Ctrl-Shift-Enter keep pushing it :-) -- HTH Bob Phillips "CHRIS K" wrote in message ... The MAX works but the MIN returns 0 thanks -- CHRISK "Ron Coderre" wrote: Here's one way: In my example, I put letters down Col A and Values down Col B: =SUMPRODUCT(MAX((A2:A100="a")*(B2:B100))) You could also engage an autofilter and use the SUBTOTAL function to return the maximum visible value: =SUBTOTAL(4,range) Does that help? -- Regards, Ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel equivalent of Matlab stem plot | Charts and Charting in Excel | |||
Can you convert a number to word equivalent ? | Excel Worksheet Functions | |||
convert duration (hr:mn:sec) in to equivalent sec | Excel Discussion (Misc queries) | |||
How do you make a backup file - .bak or MS equivalent? | New Users to Excel | |||
Lotus Equivalent | Excel Discussion (Misc queries) |