# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  IF statement including BETWEEN

## simmerdown

How can I write an IF statement that evaluates whether a cell's value is
BETWEEN two numbers?

Example:

A1 = 89.99

I need a statement that evaluates whether A1's contents are between 80.01
and 90.00.

Thank you.

----------


## Ken Wright

=AND(A1>80.01,A1<90)

--
Regards
Ken.......................    Microsoft MVP - Excel
Sys Spec - Win XP Pro /  XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"simmerdown" <simmerdown@discussions.microsoft.com> wrote in message
news:3DE91615-C576-4BD6-B171-156EBF83B057@microsoft.com...
> How can I write an IF statement that evaluates whether a cell's value is
> BETWEEN two numbers?
>
> Example:
>
> A1 = 89.99
>
> I need a statement that evaluates whether A1's contents are between 80.01
> and 90.00.
>
> Thank you.

----------


## Peo Sjoblom

=AND(A1>=80.01,A1<=90.00)

will return TRUE if it is, if you need to get another answer

=IF(AND(A1>=80.01,A1<=90.00),"Yes","No")

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"simmerdown" <simmerdown@discussions.microsoft.com> wrote in message
news:3DE91615-C576-4BD6-B171-156EBF83B057@microsoft.com...
> How can I write an IF statement that evaluates whether a cell's value is
> BETWEEN two numbers?
>
> Example:
>
> A1 = 89.99
>
> I need a statement that evaluates whether A1's contents are between 80.01
> and 90.00.
>
> Thank you.

----------


## Duke Carey

=if(and(a1>=80.01,A1<=90),"Between", "Not between")


"simmerdown" wrote:

> How can I write an IF statement that evaluates whether a cell's value is
> BETWEEN two numbers?
>
> Example:
>
> A1 = 89.99
>
> I need a statement that evaluates whether A1's contents are between 80.01
> and 90.00.
>
> Thank you.

----------


## Ken Wright

Need to watch those = signs if BETWEEN is to be taken literally.  Depends on
what the OP really meant though :-)

--
Regards
Ken.......................    Microsoft MVP - Excel
Sys Spec - Win XP Pro /  XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"Duke Carey" <DukeCarey@discussions.microsoft.com> wrote in message
news:F237B6AB-0111-403B-848E-A02364677002@microsoft.com...
> =if(and(a1>=80.01,A1<=90),"Between", "Not between")
>
>
> "simmerdown" wrote:
>
>> How can I write an IF statement that evaluates whether a cell's value is
>> BETWEEN two numbers?
>>
>> Example:
>>
>> A1 = 89.99
>>
>> I need a statement that evaluates whether A1's contents are between 80.01
>> and 90.00.
>>
>> Thank you.

----------


## Duke Carey

This is true!

"Ken Wright" wrote:

> Need to watch those = signs if BETWEEN is to be taken literally.  Depends on
> what the OP really meant though :-)
>
> --
> Regards
>            Ken.......................    Microsoft MVP - Excel
>               Sys Spec - Win XP Pro /  XL 97/00/02/03
>
> ------------------------------Â*------------------------------Â*----------------
> It's easier to beg forgiveness than ask permission :-)
> ------------------------------Â*------------------------------Â*----------------
>
>
> "Duke Carey" <DukeCarey@discussions.microsoft.com> wrote in message
> news:F237B6AB-0111-403B-848E-A02364677002@microsoft.com...
> > =if(and(a1>=80.01,A1<=90),"Between", "Not between")
> >
> >
> > "simmerdown" wrote:
> >
> >> How can I write an IF statement that evaluates whether a cell's value is
> >> BETWEEN two numbers?
> >>
> >> Example:
> >>
> >> A1 = 89.99
> >>
> >> I need a statement that evaluates whether A1's contents are between 80.01
> >> and 90.00.
> >>
> >> Thank you.
>
>
>

----------


## simmerdown

How can I combine more than one of these "IF(AND)" statements?

"Peo Sjoblom" wrote:

> =AND(A1>=80.01,A1<=90.00)
>
> will return TRUE if it is, if you need to get another answer
>
> =IF(AND(A1>=80.01,A1<=90.00),"Yes","No")
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> http://nwexcelsolutions.com
>
>
> "simmerdown" <simmerdown@discussions.microsoft.com> wrote in message
> news:3DE91615-C576-4BD6-B171-156EBF83B057@microsoft.com...
> > How can I write an IF statement that evaluates whether a cell's value is
> > BETWEEN two numbers?
> >
> > Example:
> >
> > A1 = 89.99
> >
> > I need a statement that evaluates whether A1's contents are between 80.01
> > and 90.00.
> >
> > Thank you.
>
>
>

----------


## simmerdown

Duke, how can I combine several of these together?

"Duke Carey" wrote:

> =if(and(a1>=80.01,A1<=90),"Between", "Not between")
>
>
> "simmerdown" wrote:
>
> > How can I write an IF statement that evaluates whether a cell's value is
> > BETWEEN two numbers?
> >
> > Example:
> >
> > A1 = 89.99
> >
> > I need a statement that evaluates whether A1's contents are between 80.01
> > and 90.00.
> >
> > Thank you.

----------


## Duke Carey

You may be better off using a lookup table.  I'm guessing that you have a
series of ranges and you want your result to vary, depending on which range
the tested value falls into.  If that is so, create a 2-column table that
starts with the lowest # in your ranges in the left column, and the
corresponding result in the right column.  Something like grades

0        F
60      D
70      C
80      B
90      A

Let's say this table is in cells A1:B5

With the numeric grade 85 in D2, use a formula like

=VLOOKUP(D2,A1:B5,2)

which tells us that an 85 is a B



"simmerdown" wrote:

> Duke, how can I combine several of these together?
>
> "Duke Carey" wrote:
>
> > =if(and(a1>=80.01,A1<=90),"Between", "Not between")
> >
> >
> > "simmerdown" wrote:
> >
> > > How can I write an IF statement that evaluates whether a cell's value is
> > > BETWEEN two numbers?
> > >
> > > Example:
> > >
> > > A1 = 89.99
> > >
> > > I need a statement that evaluates whether A1's contents are between 80.01
> > > and 90.00.
> > >
> > > Thank you.

----------


## Pete_UK

It depends on what you want to do.

Imagine you want to allocate a letter depending on some value - if the
value is above 80 then the letter is "A", if it is between 60 and 81
the letter is "B", if between 40 and 61 the letter is "C", and if below
40 the letter is "D". Although you are using "between" in this
statement, you wouldn't have to use the AND construct shown above
because you can test for >80 first (allocate "A" if true), then test
for >60 (allocate "B" if true, because the value must be less than or
equal to 80), then test for >40 ("C") and if none of these are true
then "D" must be the result.

Would you like to describe what it is you want to do?

Pete

----------


## simmerdown

I'm still not clear.  In the VLOOKUP, how does it know that the value 85 is a
B, if the value of 85 isn't in the table?

I have a price list from $0 to over $200.  I'm trying to group the prices
into price categories, in $10 increments.  So.....<
$10....$10.01-$20.00...$20.01-$30.00...etc.

"Duke Carey" wrote:

> You may be better off using a lookup table.  I'm guessing that you have a
> series of ranges and you want your result to vary, depending on which range
> the tested value falls into.  If that is so, create a 2-column table that
> starts with the lowest # in your ranges in the left column, and the
> corresponding result in the right column.  Something like grades
>
> 0        F
> 60      D
> 70      C
> 80      B
> 90      A
>
> Let's say this table is in cells A1:B5
>
> With the numeric grade 85 in D2, use a formula like
>
> =VLOOKUP(D2,A1:B5,2)
>
> which tells us that an 85 is a B
>
>
>
> "simmerdown" wrote:
>
> > Duke, how can I combine several of these together?
> >
> > "Duke Carey" wrote:
> >
> > > =if(and(a1>=80.01,A1<=90),"Between", "Not between")
> > >
> > >
> > > "simmerdown" wrote:
> > >
> > > > How can I write an IF statement that evaluates whether a cell's value is
> > > > BETWEEN two numbers?
> > > >
> > > > Example:
> > > >
> > > > A1 = 89.99
> > > >
> > > > I need a statement that evaluates whether A1's contents are between 80.01
> > > > and 90.00.
> > > >
> > > > Thank you.

----------


## Peo Sjoblom

If you setup the lookup table starting with 0 going to lets say 90 thus
ascending and if there is not an exact match it will lookup the largest
smaller value so if  the lookup value is 85 it will lookup 80 and then B in
this case

You don't even have to use a table, you can hard code it like

=LOOKUP(A1,{0;60;70;80;90},{"F";"D";"C";"B";"A"})

where A1 holds the lookup value

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"simmerdown" <simmerdown@discussions.microsoft.com> wrote in message
news:6EBEFBD5-FA9D-4CB8-BA43-D144ED1FF46E@microsoft.com...
> I'm still not clear.  In the VLOOKUP, how does it know that the value 85
> is a
> B, if the value of 85 isn't in the table?
>
> I have a price list from $0 to over $200.  I'm trying to group the prices
> into price categories, in $10 increments.  So.....<
> $10....$10.01-$20.00...$20.01-$30.00...etc.
>
> "Duke Carey" wrote:
>
>> You may be better off using a lookup table.  I'm guessing that you have a
>> series of ranges and you want your result to vary, depending on which
>> range
>> the tested value falls into.  If that is so, create a 2-column table that
>> starts with the lowest # in your ranges in the left column, and the
>> corresponding result in the right column.  Something like grades
>>
>> 0        F
>> 60      D
>> 70      C
>> 80      B
>> 90      A
>>
>> Let's say this table is in cells A1:B5
>>
>> With the numeric grade 85 in D2, use a formula like
>>
>> =VLOOKUP(D2,A1:B5,2)
>>
>> which tells us that an 85 is a B
>>
>>
>>
>> "simmerdown" wrote:
>>
>> > Duke, how can I combine several of these together?
>> >
>> > "Duke Carey" wrote:
>> >
>> > > =if(and(a1>=80.01,A1<=90),"Between", "Not between")
>> > >
>> > >
>> > > "simmerdown" wrote:
>> > >
>> > > > How can I write an IF statement that evaluates whether a cell's
>> > > > value is
>> > > > BETWEEN two numbers?
>> > > >
>> > > > Example:
>> > > >
>> > > > A1 = 89.99
>> > > >
>> > > > I need a statement that evaluates whether A1's contents are between
>> > > > 80.01
>> > > > and 90.00.
>> > > >
>> > > > Thank you.

----------


## simmerdown

I'm currently using the following formula to evaluate a price list, however
this formula uses all of the available spaces within the cell.  I still need
to evaluate prices above $80 to over $200.

=IF(U4<10.01,"Under
$10.01",IF(U4<20.01,"$10.01-$20.00",IF(U4<30.01,"$20.01-$30.00",IF(U4<40.01,"$30.01-$40.00",IF(U4<50.01,"$40.01-$50.00",IF(U4<60.01,"$50.01-$60.00",IF(U4<70.01,"$60.01-$70.00",IF(U4<80.01,"$70.01-$80.00"))))))))

Hopefully, this makes things a little clearer on what I'm trying to do.

"simmerdown" wrote:

> How can I write an IF statement that evaluates whether a cell's value is
> BETWEEN two numbers?
>
> Example:
>
> A1 = 89.99
>
> I need a statement that evaluates whether A1's contents are between 80.01
> and 90.00.
>
> Thank you.

----------


## Pete_UK

This formula will do what you want - it will report in $10 increments,
with no upper limits.

=IF(U4<10.01,"Under
$10.01","$"&INT(U4/10)&"0.01-$"&(INT(U4/10)+1)&"0.00")

Hope this helps.

Pete

----------


## simmerdown

Pete, thank you very much.....this is VERY close.

At the $10 increments.....10, 20, 30 etc........it is grouping those values
in the higher category rather than the lower one.  Meaning, it puts $20 in
the $20.01-$30.00 group, rather than the $10.01-$20.00 group.

Other than that, this is what I need.

"Pete_UK" wrote:

> This formula will do what you want - it will report in $10 increments,
> with no upper limits.
>
> =IF(U4<10.01,"Under
> $10.01","$"&INT(U4/10)&"0.01-$"&(INT(U4/10)+1)&"0.00")
>
> Hope this helps.
>
> Pete
>
>

----------


## Pete_UK

Sorry, I only tested it with mid-range values. Here's an amended
version:

=IF(U4<10.01,"Under
$10.01","$"&INT((U4-0.01)/10)&"0.01-$"&(INT((U4-0.01)/10)+1)&"0.00")

This should solve it.

Pete

----------


## simmerdown

Pete, this works perfectly.  Thank you very much!!!!!

Dave.

"Pete_UK" wrote:

> Sorry, I only tested it with mid-range values. Here's an amended
> version:
>
> =IF(U4<10.01,"Under
> $10.01","$"&INT((U4-0.01)/10)&"0.01-$"&(INT((U4-0.01)/10)+1)&"0.00")
>
> This should solve it.
>
> Pete
>
>

----------


## Pete_UK

Thanks for feeding back - a bit simpler than all those IFs.

Pete

----------

