IF statement including BETWEEN

S

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

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 :)
------------------------------­------------------------------­----------------
 
K

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 :)
------------------------------­------------------------------­----------------
 
D

Duke Carey

This is true!

Ken Wright said:
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 :)
------------------------------­------------------------------­----------------
Click to expand...
 
D

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
 
P

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
 
S

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

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
 
S

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

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
 
S

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

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
 
Top