Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
"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 -- |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Better Way to Code IF Statements? | Excel Discussion (Misc queries) | |||
Logical ELSE statements | Excel Discussion (Misc queries) | |||
Nesting IF Statements Based on Same Cell Value | Excel Worksheet Functions | |||
Nested IF statements | Excel Worksheet Functions | |||
If statements | Excel Worksheet Functions |