MAX function with IF Statement

I am trying to get the MAX value form a list using an IF statement with
March 1st 2006 as the condition. Based on the following abbreviated
table, I wrote the following formula:

={MAX(IF((A2:A7<C1),B2:B7,0)}

Date Rate March 1, 2006
January 1, 2006 1.1511
January 31, 2006 1.1560
February 1, 2006 1.1573
February 28, 2006 1.1614
March 1, 2006 1.1593
March 31, 2006 1.1450


I want to be able to rewrite the condition to specify a date range such
as greater than Jan 31st but less than March 1st. Any help would be
appreciated

Nest the if statements
=Max(if((A2:A7<C1),If((A2:A7>C2),B2:B7,0),0))

Charles
xl Geek
dbuc283 wrote:
> I am trying to get the MAX value form a list using an IF statement with
> March 1st 2006 as the condition. Based on the following abbreviated
> table, I wrote the following formula:
>
> ={MAX(IF((A2:A7<C1),B2:B7,0)}
>
> Date Rate March 1, 2006
> January 1, 2006 1.1511
> January 31, 2006 1.1560
> February 1, 2006 1.1573
> February 28, 2006 1.1614
> March 1, 2006 1.1593
> March 31, 2006 1.1450
>
>
> I want to be able to rewrite the condition to specify a date range such
> as greater than Jan 31st but less than March 1st. Any help would be
> appreciated

thanks for he help

Die_Another_Day wrote:
> Nest the if statements
> =Max(if((A2:A7<C1),If((A2:A7>C2),B2:B7,0),0))
>
> Charles
> xl Geek
> dbuc283 wrote:
> > I am trying to get the MAX value form a list using an IF statement with
> > March 1st 2006 as the condition. Based on the following abbreviated
> > table, I wrote the following formula:
> >
> > ={MAX(IF((A2:A7<C1),B2:B7,0)}
> >
> > Date Rate March 1, 2006
> > January 1, 2006 1.1511
> > January 31, 2006 1.1560
> > February 1, 2006 1.1573
> > February 28, 2006 1.1614
> > March 1, 2006 1.1593
> > March 31, 2006 1.1450
> >
> >
> > I want to be able to rewrite the condition to specify a date range such
> > as greater than Jan 31st but less than March 1st. Any help would be
> > appreciated

=MAX(IF((A2:A7>A3)*(A2:A7<C1),B2:B7))

array entered


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"dbuc283" <[email protected]> wrote in message
news:[email protected]...
> I am trying to get the MAX value form a list using an IF statement with
> March 1st 2006 as the condition. Based on the following abbreviated
> table, I wrote the following formula:
>
> ={MAX(IF((A2:A7<C1),B2:B7,0)}
>
> Date Rate March 1, 2006
> January 1, 2006 1.1511
> January 31, 2006 1.1560
> February 1, 2006 1.1573
> February 28, 2006 1.1614
> March 1, 2006 1.1593
> March 31, 2006 1.1450
>
>
> I want to be able to rewrite the condition to specify a date range such
> as greater than Jan 31st but less than March 1st. Any help would be
> appreciated
>