IF Statements
 

I am trying to find a way to use an IF statement in a way in which I'm not
sure it was meant to be used in excel, but maybe someone can help me work it
out.

What I need to do is assign a cell a different number according to what
range another cell's number falls into. For example if the number that is
entered into cell D1 falls between 1.5 and 1.8, I need a 7 to be placed in
cell D2. BUT, if that number falls between 2.3 and 2.7, I need a 5 to be
placed in cell D2. In all I have 9 ranges on numbers that dictate what
number should be place into cell D2. Is there a way to do this in excel?

It appears your ranges are not contiguous, so...

Set up a table of three columns. Put the lower bound of each of your ranges
in the left most column, say A1:A9. The upper bound is in B1:B9, and the
number to be assigned is in C1:C9. If the number you are tsting is in D1,
then use this formula:

=SUMPRODUCT(--(A1:A9<=D1),--(B1:B9=D1),C1:C9)


"rfryan61" wrote:


I am trying to find a way to use an IF statement in a way in which I'm not
sure it was meant to be used in excel, but maybe someone can help me work it
out.

What I need to do is assign a cell a different number according to what
range another cell's number falls into. For example if the number that is
entered into cell D1 falls between 1.5 and 1.8, I need a 7 to be placed in
cell D2. BUT, if that number falls between 2.3 and 2.7, I need a 5 to be
placed in cell D2. In all I have 9 ranges on numbers that dictate what
number should be place into cell D2. Is there a way to do this in excel?

Simplest just to use nested if statements:

if(D11.5,if(D11.8,if(D12.3, 4,3),2),1)

That example returns:

1 is its less than 1.5
2 if 1.5<D1<1.8
3 if 1.8<D1<2.3
4 if D12.3

can't write yours because I don't know boundaries but it's trivial to
convert from that.


--
jacob.metcalfe
------------------------------------------------------------------------
jacob.metcalfe's Profile: http://www.excelforum.com/member.php...o&userid=27105
View this thread: http://www.excelforum.com/showthread...hreadid=466264

Duke,

The range actually is continuous. Let me see if I can explain things a
little better. There has already been a calculation done that tells us if a
product fall between a specific range, it gets a specific €œgrade.€� For
example, if it falls between 0.0-0.4, it gets a grade of 5. And if it falls
between 0.41-1.25, it gets a grade of 4.5. The ranges are continuous:
0.0-0.4, 0.41-1.25, 1.26-2.10 etc. (9 ranges in all, ending @13.60). Now
what I will be doing is entering a single number in a cell that will fall
into one of these ranges, and I want our grade number to automatically pop up
in the cell next to it, so thats where the formula should be€”right?

"Duke Carey" wrote:

It appears your ranges are not contiguous, so...

Set up a table of three columns. Put the lower bound of each of your ranges
in the left most column, say A1:A9. The upper bound is in B1:B9, and the
number to be assigned is in C1:C9. If the number you are tsting is in D1,
then use this formula:

=SUMPRODUCT(--(A1:A9<=D1),--(B1:B9=D1),C1:C9)


"rfryan61" wrote:


I am trying to find a way to use an IF statement in a way in which I'm not
sure it was meant to be used in excel, but maybe someone can help me work it
out.

What I need to do is assign a cell a different number according to what
range another cell's number falls into. For example if the number that is
entered into cell D1 falls between 1.5 and 1.8, I need a 7 to be placed in
cell D2. BUT, if that number falls between 2.3 and 2.7, I need a 5 to be
placed in cell D2. In all I have 9 ranges on numbers that dictate what
number should be place into cell D2. Is there a way to do this in excel?

"rfryan61" wrote
.. The range actually is continuous. Let me see if I can explain things a
little better. There has already been a calculation done that tells us if
a
product fall between a specific range, it gets a specific "grade." For
example, if it falls between 0.0-0.4, it gets a grade of 5. And if it
falls
between 0.41-1.25, it gets a grade of 4.5. The ranges are continuous:
0.0-0.4, 0.41-1.25, 1.26-2.10 etc. (9 ranges in all, ending @13.60). Now
what I will be doing is entering a single number in a cell that will fall
into one of these ranges, and I want our grade number to automatically
pop up in the cell next to it, ..

One way is to try VLOOKUP

In Sheet2, set up a lookup reference table in cols A & B,
e.g.:

0.00 5.0
0.41 4.5
1.26 4.0
etc

Then in say, Sheet1, if you have the values in col A, in A1 down

Put in B1: =IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2))
and copy down

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

I believe he mentioned 9 ranges. That exceeds the limit for nested IF
statements, no?

On Fri, 9 Sep 2005 10:33:56 -0500, jacob.metcalfe
<jacob.metcalfe.1v3n2f_1126281912.0165@excelforu m-nospam.com wrote:


Simplest just to use nested if statements:

if(D11.5,if(D11.8,if(D12.3, 4,3),2),1)

That example returns:

1 is its less than 1.5
2 if 1.5<D1<1.8
3 if 1.8<D1<2.3
4 if D12.3

can't write yours because I don't know boundaries but it's trivial to
convert from that.

The formula I gave you will work whether the ranges ("boundaries") are
contiguous or not and yes, you would put this formula in the cell where you
want the numeric value to appear.

DO NOT use nested IF() functions! They are OK for 2 or 3 conditions, but
have a max of 7 nested layers. If you get even close to the limit the
parentheses are terribly hard to keep straight.

"rfryan61" wrote:

Duke,

The range actually is continuous. Let me see if I can explain things a
little better. There has already been a calculation done that tells us if a
product fall between a specific range, it gets a specific €œgrade.€� For
example, if it falls between 0.0-0.4, it gets a grade of 5. And if it falls
between 0.41-1.25, it gets a grade of 4.5. The ranges are continuous:
0.0-0.4, 0.41-1.25, 1.26-2.10 etc. (9 ranges in all, ending @13.60). Now
what I will be doing is entering a single number in a cell that will fall
into one of these ranges, and I want our grade number to automatically pop up
in the cell next to it, so thats where the formula should be€”right?

"Duke Carey" wrote:

It appears your ranges are not contiguous, so...

Set up a table of three columns. Put the lower bound of each of your ranges
in the left most column, say A1:A9. The upper bound is in B1:B9, and the
number to be assigned is in C1:C9. If the number you are tsting is in D1,
then use this formula:

=SUMPRODUCT(--(A1:A9<=D1),--(B1:B9=D1),C1:C9)


"rfryan61" wrote:


I am trying to find a way to use an IF statement in a way in which I'm not
sure it was meant to be used in excel, but maybe someone can help me work it
out.

What I need to do is assign a cell a different number according to what
range another cell's number falls into. For example if the number that is
entered into cell D1 falls between 1.5 and 1.8, I need a 7 to be placed in
cell D2. BUT, if that number falls between 2.3 and 2.7, I need a 5 to be
placed in cell D2. In all I have 9 ranges on numbers that dictate what
number should be place into cell D2. Is there a way to do this in excel?