Home |
Search |
Today's Posts |
#1
|
|||
|
|||
multiple if
I need to create a formula that outputs a 1, 2, or 3 based on the result of:
If the value of x-y <10 then cell B3 = 1, If the value of x-y<10 but 15 then cell B3 = 2, If x-y<15 then cell B3 = 3. Can I do this with formulae or do I need to go to VBA, if I need to go to VBA how is this calculated. |
#2
|
|||
|
|||
in cell B3 =if(x-y<10,1,if(x-y<15,2,3)).I have assumed your middle statement
is between 10 and 15 and your last statement is greater than 15.I cant make sense otherwise -- paul remove nospam for email addy! "Elvin" wrote: I need to create a formula that outputs a 1, 2, or 3 based on the result of: If the value of x-y <10 then cell B3 = 1, If the value of x-y<10 but 15 then cell B3 = 2, If x-y<15 then cell B3 = 3. Can I do this with formulae or do I need to go to VBA, if I need to go to VBA how is this calculated. |
#3
|
|||
|
|||
How can the value be <10 but 15 in your second option? Assuming there was a
typo and you mean 10 <15 that leaves 10 outside? Do you mean 1 <10, 2 =10 <15 and 3 =15 ? If so you can use =VLOOKUP(X-Y,{0,1;10,2;15,3},2) Regards Peo Sjoblom (No private emails please) "Elvin" wrote in message ... I need to create a formula that outputs a 1, 2, or 3 based on the result of: If the value of x-y <10 then cell B3 = 1, If the value of x-y<10 but 15 then cell B3 = 2, If x-y<15 then cell B3 = 3. Can I do this with formulae or do I need to go to VBA, if I need to go to VBA how is this calculated. |
#4
|
|||
|
|||
That was the trick. Thanks Paul!
"paul" wrote: in cell B3 =if(x-y<10,1,if(x-y<15,2,3)).I have assumed your middle statement is between 10 and 15 and your last statement is greater than 15.I cant make sense otherwise -- paul remove nospam for email addy! "Elvin" wrote: I need to create a formula that outputs a 1, 2, or 3 based on the result of: If the value of x-y <10 then cell B3 = 1, If the value of x-y<10 but 15 then cell B3 = 2, If x-y<15 then cell B3 = 3. Can I do this with formulae or do I need to go to VBA, if I need to go to VBA how is this calculated. |
#5
|
|||
|
|||
you might have to consider <= as well to include 10 and 15
-- paul remove nospam for email addy! "Elvin" wrote: That was the trick. Thanks Paul! "paul" wrote: in cell B3 =if(x-y<10,1,if(x-y<15,2,3)).I have assumed your middle statement is between 10 and 15 and your last statement is greater than 15.I cant make sense otherwise -- paul remove nospam for email addy! "Elvin" wrote: I need to create a formula that outputs a 1, 2, or 3 based on the result of: If the value of x-y <10 then cell B3 = 1, If the value of x-y<10 but 15 then cell B3 = 2, If x-y<15 then cell B3 = 3. Can I do this with formulae or do I need to go to VBA, if I need to go to VBA how is this calculated. |
#6
|
|||
|
|||
Hi Poe, Elvin and Paul,
That was a good one Poe. I think Elvin has not described the problem properly and everyone has the solution though. Condition 1 Elvin stated: If the value of x-y <10 then cell B3=1. FINE Condition 2 Elvin stated: If the value of x-y<10 but 15 then cell B3 = 2. Now this is contradictory to condition 1. In con 1 <10 B3=1. Now here in CON 2 he again want <10 B3 =2. Condition 3 Elvin stated: If value x-y<15 B3=3. Contadictory to condition 1 and part of condition 2. I am sorry if I have misunderstood anyone here. The alternate formula for Poe's formula=VLOOKUP(X-Y,{0,1;10,2;15,3},2) is =IF(x-y<10,1,IF(AND(x-y=10,x-y<15),2,3)). Poe thanks I learnt something from your formula. Elvin I request you to consider your condition. -- Message posted via http://www.officekb.com |
#7
|
|||
|
|||
Hi Poe <g
-- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Peo Sjoblom" wrote in message ... How can the value be <10 but 15 in your second option? Assuming there was a typo and you mean 10 <15 that leaves 10 outside? Do you mean 1 <10, 2 =10 <15 and 3 =15 ? If so you can use =VLOOKUP(X-Y,{0,1;10,2;15,3},2) Regards Peo Sjoblom (No private emails please) "Elvin" wrote in message ... I need to create a formula that outputs a 1, 2, or 3 based on the result of: If the value of x-y <10 then cell B3 = 1, If the value of x-y<10 but 15 then cell B3 = 2, If x-y<15 then cell B3 = 3. Can I do this with formulae or do I need to go to VBA, if I need to go to VBA how is this calculated. |
#8
|
|||
|
|||
Hi
Another solution =MATCH(B3,{0;10;15},1) (I myself can't use Peo's solution because my regional settings.) Arvi Laanemets "Elvin" wrote in message ... I need to create a formula that outputs a 1, 2, or 3 based on the result of: If the value of x-y <10 then cell B3 = 1, If the value of x-y<10 but 15 then cell B3 = 2, If x-y<15 then cell B3 = 3. Can I do this with formulae or do I need to go to VBA, if I need to go to VBA how is this calculated. |
#9
|
|||
|
|||
AKA "The Raven" <bg
-- Regards, Peo Sjoblom (No private emails please) "Ken Wright" wrote in message ... Hi Poe <g -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Peo Sjoblom" wrote in message ... How can the value be <10 but 15 in your second option? Assuming there was a typo and you mean 10 <15 that leaves 10 outside? Do you mean 1 <10, 2 =10 <15 and 3 =15 ? If so you can use =VLOOKUP(X-Y,{0,1;10,2;15,3},2) Regards Peo Sjoblom (No private emails please) "Elvin" wrote in message ... I need to create a formula that outputs a 1, 2, or 3 based on the result of: If the value of x-y <10 then cell B3 = 1, If the value of x-y<10 but 15 then cell B3 = 2, If x-y<15 then cell B3 = 3. Can I do this with formulae or do I need to go to VBA, if I need to go to VBA how is this calculated. |
#10
|
|||
|
|||
Never more ... Never more!
"Peo Sjoblom" wrote in message ... AKA "The Raven" <bg -- Regards, Peo Sjoblom (No private emails please) "Ken Wright" wrote in message ... Hi Poe <g -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "Peo Sjoblom" wrote in message ... How can the value be <10 but 15 in your second option? Assuming there was a typo and you mean 10 <15 that leaves 10 outside? Do you mean 1 <10, 2 =10 <15 and 3 =15 ? If so you can use =VLOOKUP(X-Y,{0,1;10,2;15,3},2) Regards Peo Sjoblom (No private emails please) "Elvin" wrote in message ... I need to create a formula that outputs a 1, 2, or 3 based on the result of: If the value of x-y <10 then cell B3 = 1, If the value of x-y<10 but 15 then cell B3 = 2, If x-y<15 then cell B3 = 3. Can I do this with formulae or do I need to go to VBA, if I need to go to VBA how is this calculated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I show multiple worksheets on multiple screens? | Excel Discussion (Misc queries) | |||
Can I get the mode, min, and max with multiple criteria? | Excel Discussion (Misc queries) | |||
adding certain cells in multiple worksheets in multiple workbooks | Excel Worksheet Functions | |||
XML / parent with multiple children and with multiple children | Excel Discussion (Misc queries) | |||
multiple entries | Excel Worksheet Functions |