IF Function based on a set of numbers?
 

I am trying to figure out how to do an IF function based on a set of numbers.
To be detailed, in column B I am inputing apartment unit numbers
(i.e-15472-101 or 15390-308) and in column G I would like it to tell me an
"R" or "L" based on the last digit. All units ending with 1,3,6, or 8 would
show a "L" and all units that end with 2,4,5,or 7 would show a "R". Does
anyone know how to write this formula? This is something that I currently
have to do manually 664 times and would love to have a formula do this for
me....

IF Function based on a set of numbers?
 

Here is one way. Use a formula like the following in column G:

=CHOOSE(RIGHT(B1,1),"L","R","L","R","R","L","R","L ")

This formula will not know what to do if the last digit is a zero or a nine.
We could check for these and other errors this way:

=IF(ISERROR(CHOOSE(RIGHT(B1,1),"L","R","L","R","R" ,"L","R","L")),"???",CHOOSE(RIGHT(B1,1),"L","R","L ","R","R","L","R","L"))

Hope this helps,

Hutch

"Scott A" wrote:

I am trying to figure out how to do an IF function based on a set of numbers.
To be detailed, in column B I am inputing apartment unit numbers
(i.e-15472-101 or 15390-308) and in column G I would like it to tell me an
"R" or "L" based on the last digit. All units ending with 1,3,6, or 8 would
show a "L" and all units that end with 2,4,5,or 7 would show a "R". Does
anyone know how to write this formula? This is something that I currently
have to do manually 664 times and would love to have a formula do this for
me....

IF Function based on a set of numbers?
 

Thank you Hutch.

I tried your formula and I just recieved and error so I tried to use the
other formula and just recieved "???". The unit number that I am using is
472-101. Am I doing something wrong?

Scott A

"Tom Hutchins" wrote:

Here is one way. Use a formula like the following in column G:

=CHOOSE(RIGHT(B1,1),"L","R","L","R","R","L","R","L ")

This formula will not know what to do if the last digit is a zero or a nine.
We could check for these and other errors this way:

=IF(ISERROR(CHOOSE(RIGHT(B1,1),"L","R","L","R","R" ,"L","R","L")),"???",CHOOSE(RIGHT(B1,1),"L","R","L ","R","R","L","R","L"))

Hope this helps,

Hutch

"Scott A" wrote:

I am trying to figure out how to do an IF function based on a set of numbers.
To be detailed, in column B I am inputing apartment unit numbers
(i.e-15472-101 or 15390-308) and in column G I would like it to tell me an
"R" or "L" based on the last digit. All units ending with 1,3,6, or 8 would
show a "L" and all units that end with 2,4,5,or 7 would show a "R". Does
anyone know how to write this formula? This is something that I currently
have to do manually 664 times and would love to have a formula do this for
me....

IF Function based on a set of numbers?
 

Also, there will not be any 0's or 9's for this property. I also need
another formula that will bo the same thing but using a unit number with a
Letter in it like 981A-IC where I would like it to pick up the "A" at the end
of the numbers. That number could be anything from A-H in that one slot. I
know that this one will probaly be a lot more complicated but thanks for any
help you can give me.

Scott A

"Tom Hutchins" wrote:

Here is one way. Use a formula like the following in column G:

=CHOOSE(RIGHT(B1,1),"L","R","L","R","R","L","R","L ")

This formula will not know what to do if the last digit is a zero or a nine.
We could check for these and other errors this way:

=IF(ISERROR(CHOOSE(RIGHT(B1,1),"L","R","L","R","R" ,"L","R","L")),"???",CHOOSE(RIGHT(B1,1),"L","R","L ","R","R","L","R","L"))

Hope this helps,

Hutch

"Scott A" wrote:

I am trying to figure out how to do an IF function based on a set of numbers.
To be detailed, in column B I am inputing apartment unit numbers
(i.e-15472-101 or 15390-308) and in column G I would like it to tell me an
"R" or "L" based on the last digit. All units ending with 1,3,6, or 8 would
show a "L" and all units that end with 2,4,5,or 7 would show a "R". Does
anyone know how to write this formula? This is something that I currently
have to do manually 664 times and would love to have a formula do this for
me....

IF Function based on a set of numbers?
 

Nevermind, I figured that one out, I forgot to change the B1 to the correct
Cell number. LOL I just need help on the last one that I mentioned...

"Tom Hutchins" wrote:

Here is one way. Use a formula like the following in column G:

=CHOOSE(RIGHT(B1,1),"L","R","L","R","R","L","R","L ")

This formula will not know what to do if the last digit is a zero or a nine.
We could check for these and other errors this way:

=IF(ISERROR(CHOOSE(RIGHT(B1,1),"L","R","L","R","R" ,"L","R","L")),"???",CHOOSE(RIGHT(B1,1),"L","R","L ","R","R","L","R","L"))

Hope this helps,

Hutch

"Scott A" wrote:

I am trying to figure out how to do an IF function based on a set of numbers.
To be detailed, in column B I am inputing apartment unit numbers
(i.e-15472-101 or 15390-308) and in column G I would like it to tell me an
"R" or "L" based on the last digit. All units ending with 1,3,6, or 8 would
show a "L" and all units that end with 2,4,5,or 7 would show a "R". Does
anyone know how to write this formula? This is something that I currently
have to do manually 664 times and would love to have a formula do this for
me....

IF Function based on a set of numbers?
 

In the unit numbers with the letters, do they always follow the pattern of
your example (numbers, a letter, dash, more letters)? What do you want the
formula to return for the different letters (A-H)?

Hutch

"Scott A" wrote:

Also, there will not be any 0's or 9's for this property. I also need
another formula that will bo the same thing but using a unit number with a
Letter in it like 981A-IC where I would like it to pick up the "A" at the end
of the numbers. That number could be anything from A-H in that one slot. I
know that this one will probaly be a lot more complicated but thanks for any
help you can give me.

Scott A

"Tom Hutchins" wrote:

Here is one way. Use a formula like the following in column G:

=CHOOSE(RIGHT(B1,1),"L","R","L","R","R","L","R","L ")

This formula will not know what to do if the last digit is a zero or a nine.
We could check for these and other errors this way:

=IF(ISERROR(CHOOSE(RIGHT(B1,1),"L","R","L","R","R" ,"L","R","L")),"???",CHOOSE(RIGHT(B1,1),"L","R","L ","R","R","L","R","L"))

Hope this helps,

Hutch

"Scott A" wrote:

I am trying to figure out how to do an IF function based on a set of numbers.
To be detailed, in column B I am inputing apartment unit numbers
(i.e-15472-101 or 15390-308) and in column G I would like it to tell me an
"R" or "L" based on the last digit. All units ending with 1,3,6, or 8 would
show a "L" and all units that end with 2,4,5,or 7 would show a "R". Does
anyone know how to write this formula? This is something that I currently
have to do manually 664 times and would love to have a formula do this for
me....

IF Function based on a set of numbers?
 

If you are interested, here is another formula you can use...

=IF(OR(--RIGHT(A11)={1,3,6,8}),"L","R")

--
Rick (MVP - Excel)


"Scott A" wrote in message
...
Nevermind, I figured that one out, I forgot to change the B1 to the
correct
Cell number. LOL I just need help on the last one that I mentioned...

"Tom Hutchins" wrote:

Here is one way. Use a formula like the following in column G:

=CHOOSE(RIGHT(B1,1),"L","R","L","R","R","L","R","L ")

This formula will not know what to do if the last digit is a zero or a
nine.
We could check for these and other errors this way:

=IF(ISERROR(CHOOSE(RIGHT(B1,1),"L","R","L","R","R" ,"L","R","L")),"???",CHOOSE(RIGHT(B1,1),"L","R","L ","R","R","L","R","L"))

Hope this helps,

Hutch

"Scott A" wrote:

I am trying to figure out how to do an IF function based on a set of
numbers.
To be detailed, in column B I am inputing apartment unit numbers
(i.e-15472-101 or 15390-308) and in column G I would like it to tell me
an
"R" or "L" based on the last digit. All units ending with 1,3,6, or 8
would
show a "L" and all units that end with 2,4,5,or 7 would show a "R".
Does
anyone know how to write this formula? This is something that I
currently
have to do manually 664 times and would love to have a formula do this
for
me....

IF Function based on a set of numbers?
 

They do always follow this exact pattern and I would like A, C, E, and G to
return an "R" and B, D, F, and H to return an "L".

"Tom Hutchins" wrote:

In the unit numbers with the letters, do they always follow the pattern of
your example (numbers, a letter, dash, more letters)? What do you want the
formula to return for the different letters (A-H)?

Hutch

"Scott A" wrote:

Also, there will not be any 0's or 9's for this property. I also need
another formula that will bo the same thing but using a unit number with a
Letter in it like 981A-IC where I would like it to pick up the "A" at the end
of the numbers. That number could be anything from A-H in that one slot. I
know that this one will probaly be a lot more complicated but thanks for any
help you can give me.

Scott A

"Tom Hutchins" wrote:

Here is one way. Use a formula like the following in column G:

=CHOOSE(RIGHT(B1,1),"L","R","L","R","R","L","R","L ")

This formula will not know what to do if the last digit is a zero or a nine.
We could check for these and other errors this way:

=IF(ISERROR(CHOOSE(RIGHT(B1,1),"L","R","L","R","R" ,"L","R","L")),"???",CHOOSE(RIGHT(B1,1),"L","R","L ","R","R","L","R","L"))

Hope this helps,

Hutch

"Scott A" wrote:

I am trying to figure out how to do an IF function based on a set of numbers.
To be detailed, in column B I am inputing apartment unit numbers
(i.e-15472-101 or 15390-308) and in column G I would like it to tell me an
"R" or "L" based on the last digit. All units ending with 1,3,6, or 8 would
show a "L" and all units that end with 2,4,5,or 7 would show a "R". Does
anyone know how to write this formula? This is something that I currently
have to do manually 664 times and would love to have a formula do this for
me....

IF Function based on a set of numbers?
 

Here are two ways:

=IF(OR(--(MID(A1,FIND("-",A1)-1,1)={"A","C","E","G"})),"R",IF(OR(--(MID(A1,FIND("-",A1)-1,1)={"B","D","F","H"})),"L","???"))

Depending on your character set, this might not work for you (without
changing -64 to something else):
=CHOOSE(CODE(UPPER(MID(A1,FIND("-",A1)-1,1)))-64,"R","L","R","L","R","L","R","L")

Change the A1 references as needed in either formula.

Hutch

"Scott A" wrote:

They do always follow this exact pattern and I would like A, C, E, and G to
return an "R" and B, D, F, and H to return an "L".

"Tom Hutchins" wrote:

In the unit numbers with the letters, do they always follow the pattern of
your example (numbers, a letter, dash, more letters)? What do you want the
formula to return for the different letters (A-H)?

Hutch

"Scott A" wrote:

Also, there will not be any 0's or 9's for this property. I also need
another formula that will bo the same thing but using a unit number with a
Letter in it like 981A-IC where I would like it to pick up the "A" at the end
of the numbers. That number could be anything from A-H in that one slot. I
know that this one will probaly be a lot more complicated but thanks for any
help you can give me.

Scott A

"Tom Hutchins" wrote:

Here is one way. Use a formula like the following in column G:

=CHOOSE(RIGHT(B1,1),"L","R","L","R","R","L","R","L ")

This formula will not know what to do if the last digit is a zero or a nine.
We could check for these and other errors this way:

=IF(ISERROR(CHOOSE(RIGHT(B1,1),"L","R","L","R","R" ,"L","R","L")),"???",CHOOSE(RIGHT(B1,1),"L","R","L ","R","R","L","R","L"))

Hope this helps,

Hutch

"Scott A" wrote:

I am trying to figure out how to do an IF function based on a set of numbers.
To be detailed, in column B I am inputing apartment unit numbers
(i.e-15472-101 or 15390-308) and in column G I would like it to tell me an
"R" or "L" based on the last digit. All units ending with 1,3,6, or 8 would
show a "L" and all units that end with 2,4,5,or 7 would show a "R". Does
anyone know how to write this formula? This is something that I currently
have to do manually 664 times and would love to have a formula do this for
me....

IF Function based on a set of numbers?
 

Thank you Rick! I will keep that one for future use.

"Rick Rothstein" wrote:

If you are interested, here is another formula you can use...

=IF(OR(--RIGHT(A11)={1,3,6,8}),"L","R")

--
Rick (MVP - Excel)


"Scott A" wrote in message
...
Nevermind, I figured that one out, I forgot to change the B1 to the
correct
Cell number. LOL I just need help on the last one that I mentioned...

"Tom Hutchins" wrote:

Here is one way. Use a formula like the following in column G:

=CHOOSE(RIGHT(B1,1),"L","R","L","R","R","L","R","L ")

This formula will not know what to do if the last digit is a zero or a
nine.
We could check for these and other errors this way:

=IF(ISERROR(CHOOSE(RIGHT(B1,1),"L","R","L","R","R" ,"L","R","L")),"???",CHOOSE(RIGHT(B1,1),"L","R","L ","R","R","L","R","L"))

Hope this helps,

Hutch

"Scott A" wrote:

I am trying to figure out how to do an IF function based on a set of
numbers.
To be detailed, in column B I am inputing apartment unit numbers
(i.e-15472-101 or 15390-308) and in column G I would like it to tell me
an
"R" or "L" based on the last digit. All units ending with 1,3,6, or 8
would
show a "L" and all units that end with 2,4,5,or 7 would show a "R".
Does
anyone know how to write this formula? This is something that I
currently
have to do manually 664 times and would love to have a formula do this
for
me....

IF Function based on a set of numbers?
 

The secind on worked great Thank you Hutch!

"Tom Hutchins" wrote:

Here are two ways:

=IF(OR(--(MID(A1,FIND("-",A1)-1,1)={"A","C","E","G"})),"R",IF(OR(--(MID(A1,FIND("-",A1)-1,1)={"B","D","F","H"})),"L","???"))

Depending on your character set, this might not work for you (without
changing -64 to something else):
=CHOOSE(CODE(UPPER(MID(A1,FIND("-",A1)-1,1)))-64,"R","L","R","L","R","L","R","L")

Change the A1 references as needed in either formula.

Hutch

"Scott A" wrote:

They do always follow this exact pattern and I would like A, C, E, and G to
return an "R" and B, D, F, and H to return an "L".

"Tom Hutchins" wrote:

In the unit numbers with the letters, do they always follow the pattern of
your example (numbers, a letter, dash, more letters)? What do you want the
formula to return for the different letters (A-H)?

Hutch

"Scott A" wrote:

Also, there will not be any 0's or 9's for this property. I also need
another formula that will bo the same thing but using a unit number with a
Letter in it like 981A-IC where I would like it to pick up the "A" at the end
of the numbers. That number could be anything from A-H in that one slot. I
know that this one will probaly be a lot more complicated but thanks for any
help you can give me.

Scott A

"Tom Hutchins" wrote:

Here is one way. Use a formula like the following in column G:

=CHOOSE(RIGHT(B1,1),"L","R","L","R","R","L","R","L ")

This formula will not know what to do if the last digit is a zero or a nine.
We could check for these and other errors this way:

=IF(ISERROR(CHOOSE(RIGHT(B1,1),"L","R","L","R","R" ,"L","R","L")),"???",CHOOSE(RIGHT(B1,1),"L","R","L ","R","R","L","R","L"))

Hope this helps,

Hutch

"Scott A" wrote:

I am trying to figure out how to do an IF function based on a set of numbers.
To be detailed, in column B I am inputing apartment unit numbers
(i.e-15472-101 or 15390-308) and in column G I would like it to tell me an
"R" or "L" based on the last digit. All units ending with 1,3,6, or 8 would
show a "L" and all units that end with 2,4,5,or 7 would show a "R". Does
anyone know how to write this formula? This is something that I currently
have to do manually 664 times and would love to have a formula do this for
me....

IF Function based on a set of numbers?
 

The first formula Tom posted should work also. Assuming the letters A thru H
are the only possible letters that can appear in front of the dash, here is
that formula reduced to simpler terms...

=IF(OR(--(MID(A1,FIND("-",A1)-1,1)={"A","C","E","G"})),"R","L")

--
Rick (MVP - Excel)


"Scott A" wrote in message
...
The secind on worked great Thank you Hutch!

"Tom Hutchins" wrote:

Here are two ways:

=IF(OR(--(MID(A1,FIND("-",A1)-1,1)={"A","C","E","G"})),"R",IF(OR(--(MID(A1,FIND("-",A1)-1,1)={"B","D","F","H"})),"L","???"))

Depending on your character set, this might not work for you (without
changing -64 to something else):
=CHOOSE(CODE(UPPER(MID(A1,FIND("-",A1)-1,1)))-64,"R","L","R","L","R","L","R","L")

Change the A1 references as needed in either formula.

Hutch

"Scott A" wrote:

They do always follow this exact pattern and I would like A, C, E, and
G to
return an "R" and B, D, F, and H to return an "L".

"Tom Hutchins" wrote:

In the unit numbers with the letters, do they always follow the
pattern of
your example (numbers, a letter, dash, more letters)? What do you
want the
formula to return for the different letters (A-H)?

Hutch

"Scott A" wrote:

Also, there will not be any 0's or 9's for this property. I also
need
another formula that will bo the same thing but using a unit number
with a
Letter in it like 981A-IC where I would like it to pick up the "A"
at the end
of the numbers. That number could be anything from A-H in that one
slot. I
know that this one will probaly be a lot more complicated but
thanks for any
help you can give me.

Scott A

"Tom Hutchins" wrote:

Here is one way. Use a formula like the following in column G:

=CHOOSE(RIGHT(B1,1),"L","R","L","R","R","L","R","L ")

This formula will not know what to do if the last digit is a zero
or a nine.
We could check for these and other errors this way:

=IF(ISERROR(CHOOSE(RIGHT(B1,1),"L","R","L","R","R" ,"L","R","L")),"???",CHOOSE(RIGHT(B1,1),"L","R","L ","R","R","L","R","L"))

Hope this helps,

Hutch

"Scott A" wrote:

I am trying to figure out how to do an IF function based on a
set of numbers.
To be detailed, in column B I am inputing apartment unit
numbers
(i.e-15472-101 or 15390-308) and in column G I would like it to
tell me an
"R" or "L" based on the last digit. All units ending with
1,3,6, or 8 would
show a "L" and all units that end with 2,4,5,or 7 would show a
"R". Does
anyone know how to write this formula? This is something that
I currently
have to do manually 664 times and would love to have a formula
do this for
me....