software

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(A1gt;80.01,A1lt;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 :-)
------------------------------*------------------------------*----------------quot;simmerdownquot; gt; wrote in message
...
gt; How can I write an IF statement that evaluates whether a cell's value is
gt; BETWEEN two numbers?
gt;
gt; Example:
gt;
gt; A1 = 89.99
gt;
gt; I need a statement that evaluates whether A1's contents are between 80.01
gt; and 90.00.
gt;
gt; Thank you.
=AND(A1gt;=80.01,A1lt;=90.00)

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

=IF(AND(A1gt;=80.01,A1lt;=90.00),quot;Yesquot;,quot;Noquot;)

--

Regards,

Peo Sjoblom

nwexcelsolutions.comquot;simmerdownquot; gt; wrote in message
...
gt; How can I write an IF statement that evaluates whether a cell's value is
gt; BETWEEN two numbers?
gt;
gt; Example:
gt;
gt; A1 = 89.99
gt;
gt; I need a statement that evaluates whether A1's contents are between 80.01
gt; and 90.00.
gt;
gt; Thank you.
=if(and(a1gt;=80.01,A1lt;=90),quot;Betweenquot;, quot;Not betweenquot;)quot;simmerdownquot; wrote:

gt; How can I write an IF statement that evaluates whether a cell's value is
gt; BETWEEN two numbers?
gt;
gt; Example:
gt;
gt; A1 = 89.99
gt;
gt; I need a statement that evaluates whether A1's contents are between 80.01
gt; and 90.00.
gt;
gt; 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 :-)
------------------------------*------------------------------*----------------quot;Duke Careyquot; gt; wrote in message
...
gt; =if(and(a1gt;=80.01,A1lt;=90),quot;Betweenquot;, quot;Not betweenquot;)
gt;
gt;
gt; quot;simmerdownquot; wrote:
gt;
gt;gt; How can I write an IF statement that evaluates whether a cell's value is
gt;gt; BETWEEN two numbers?
gt;gt;
gt;gt; Example:
gt;gt;
gt;gt; A1 = 89.99
gt;gt;
gt;gt; I need a statement that evaluates whether A1's contents are between 80.01
gt;gt; and 90.00.
gt;gt;
gt;gt; Thank you.
This is true!

quot;Ken Wrightquot; wrote:

gt; Need to watch those = signs if BETWEEN is to be taken literally. Depends on
gt; what the OP really meant though :-)
gt;
gt; --
gt; Regards
gt; Ken....................... Microsoft MVP - Excel
gt; Sys Spec - Win XP Pro / XL 97/00/02/03
gt;
gt; ------------------------------Â*------------------------------Â*----------------
gt; It's easier to beg forgiveness than ask permission :-)
gt; ------------------------------Â*------------------------------Â*----------------
gt;
gt;
gt; quot;Duke Careyquot; gt; wrote in message
gt; ...
gt; gt; =if(and(a1gt;=80.01,A1lt;=90),quot;Betweenquot;, quot;Not betweenquot;)
gt; gt;
gt; gt;
gt; gt; quot;simmerdownquot; wrote:
gt; gt;
gt; gt;gt; How can I write an IF statement that evaluates whether a cell's value is
gt; gt;gt; BETWEEN two numbers?
gt; gt;gt;
gt; gt;gt; Example:
gt; gt;gt;
gt; gt;gt; A1 = 89.99
gt; gt;gt;
gt; gt;gt; I need a statement that evaluates whether A1's contents are between 80.01
gt; gt;gt; and 90.00.
gt; gt;gt;
gt; gt;gt; Thank you.
gt;
gt;
gt;

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

quot;Peo Sjoblomquot; wrote:

gt; =AND(A1gt;=80.01,A1lt;=90.00)
gt;
gt; will return TRUE if it is, if you need to get another answer
gt;
gt; =IF(AND(A1gt;=80.01,A1lt;=90.00),quot;Yesquot;,quot;Noquot;)
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt; quot;simmerdownquot; gt; wrote in message
gt; ...
gt; gt; How can I write an IF statement that evaluates whether a cell's value is
gt; gt; BETWEEN two numbers?
gt; gt;
gt; gt; Example:
gt; gt;
gt; gt; A1 = 89.99
gt; gt;
gt; gt; I need a statement that evaluates whether A1's contents are between 80.01
gt; gt; and 90.00.
gt; gt;
gt; gt; Thank you.
gt;
gt;
gt;

Duke, how can I combine several of these together?

quot;Duke Careyquot; wrote:

gt; =if(and(a1gt;=80.01,A1lt;=90),quot;Betweenquot;, quot;Not betweenquot;)
gt;
gt;
gt; quot;simmerdownquot; wrote:
gt;
gt; gt; How can I write an IF statement that evaluates whether a cell's value is
gt; gt; BETWEEN two numbers?
gt; gt;
gt; gt; Example:
gt; gt;
gt; gt; A1 = 89.99
gt; gt;
gt; gt; I need a statement that evaluates whether A1's contents are between 80.01
gt; gt; and 90.00.
gt; gt;
gt; gt; 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
quot;simmerdownquot; wrote:

gt; Duke, how can I combine several of these together?
gt;
gt; quot;Duke Careyquot; wrote:
gt;
gt; gt; =if(and(a1gt;=80.01,A1lt;=90),quot;Betweenquot;, quot;Not betweenquot;)
gt; gt;
gt; gt;
gt; gt; quot;simmerdownquot; wrote:
gt; gt;
gt; gt; gt; How can I write an IF statement that evaluates whether a cell's value is
gt; gt; gt; BETWEEN two numbers?
gt; gt; gt;
gt; gt; gt; Example:
gt; gt; gt;
gt; gt; gt; A1 = 89.99
gt; gt; gt;
gt; gt; gt; I need a statement that evaluates whether A1's contents are between 80.01
gt; gt; gt; and 90.00.
gt; gt; gt;
gt; gt; gt; 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 quot;Aquot;, if it is between 60 and 81
the letter is quot;Bquot;, if between 40 and 61 the letter is quot;Cquot;, and if below
40 the letter is quot;Dquot;. Although you are using quot;betweenquot; in this
statement, you wouldn't have to use the AND construct shown above
because you can test for gt;80 first (allocate quot;Aquot; if true), then test
for gt;60 (allocate quot;Bquot; if true, because the value must be less than or
equal to 80), then test for gt;40 (quot;Cquot;) and if none of these are true
then quot;Dquot; must be the result.

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

Pete