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.

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.

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.

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.

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.

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

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.

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.

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.

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.