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.

=AND(A180.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" wrote in message
...
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.

=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" wrote in message
...
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.

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

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" wrote in message
...
=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.

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" wrote in message
...
=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.

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" wrote in message
...
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, 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.

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.

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

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.

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" wrote in message
...
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.

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.

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

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

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

Pete