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

Use:

=MAX(A1:A4*(B1:B4="a"))
confirm with control - shift - enter
And similar for min

Mangesh




"CHRIS K" <CHRISK@discussions.microsoft.com> wrote in message
news:C3965EAD-FC3C-4A8C-9B4F-B3B01A99FDE5@microsoft.com...
> 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

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

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" <CHRISK@discussions.microsoft.com> wrote in message
news:C3965EAD-FC3C-4A8C-9B4F-B3B01A99FDE5@microsoft.com...
> 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

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" <CHRISK@discussions.microsoft.com> wrote in message
> news:C3965EAD-FC3C-4A8C-9B4F-B3B01A99FDE5@microsoft.com...
> > 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

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
>

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" <CHRISK@discussions.microsoft.com> wrote in message
> news:C3965EAD-FC3C-4A8C-9B4F-B3B01A99FDE5@microsoft.com...
> > 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
>
>
>

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" <CHRISK@discussions.microsoft.com> wrote in message
> news:C3965EAD-FC3C-4A8C-9B4F-B3B01A99FDE5@microsoft.com...
> > 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
>
>
>

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
> >

Don't forget Ctrl-Shift-Enter.

--
HTH

Bob Phillips

"CHRIS K" <CHRISK@discussions.microsoft.com> wrote in message
news:416BD4E6-3394-4872-A684-68373CEB4B42@microsoft.com...
> 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" <CHRISK@discussions.microsoft.com> wrote in message
> > news:C3965EAD-FC3C-4A8C-9B4F-B3B01A99FDE5@microsoft.com...
> > > 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
> >
> >
> >

makes it an array formula, otherwise it doesn't work on the whole range.

--
HTH

Bob Phillips

"CHRIS K" <CHRISK@discussions.microsoft.com> wrote in message
news:EA7ECC87-19A0-48A5-8B9D-923B9781B264@microsoft.com...
> 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" <CHRISK@discussions.microsoft.com> wrote in message
> > news:C3965EAD-FC3C-4A8C-9B4F-B3B01A99FDE5@microsoft.com...
> > > 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
> >
> >
> >

=MIN(IF(A2:A100="a",B2:B100))

array entered, Ctrl-Shift-Enter

keep pushing it :-)

--
HTH

Bob Phillips

"CHRIS K" <CHRISK@discussions.microsoft.com> wrote in message
news:321A8856-9B91-4A50-81B9-6D1D77A744E0@microsoft.com...
> 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
> >

I’ve been trying to create a ‘Max-If’ formula which would find would find the maximum of a range which is less-than 'n' & return the value of a cell in a separate range.

Num. Let.
12 A
17 B
12 C
22 D
33 E
18 F
16 G

Find the Max of A1:A7 [Num.] which is less than 30, and return the cell in matching row B1:B7 [Let.]

e.g. {=MAX(IF(A1:A7<30,B1:B7,”Not Found”))} or {=IF(MAX(A1:A7<30),B1:B7,”Not Found”))}

but they do not work.

The less-than or greater-than seems to present some issue vs. the equal.

Any help is greatly appreciated.