IF Function based on a set of numbers?

  • Thread starter Thread starter Scott A
  • Start date Start date
S

Scott A

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....
 
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
 
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
 
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
 
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...
 
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
 
If you are interested, here is another formula you can use...

=IF(OR(--RIGHT(A11)={1,3,6,8}),"L","R")
 
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".
 
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
 
The secind on worked great> Thank you Hutch!

Tom Hutchins said:
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
Click to expand...
 
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")
 
Back
Top