If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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.... |
#2
|
|||
|
|||
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.... |
#3
|
|||
|
|||
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.... |
#4
|
|||
|
|||
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.... |
#5
|
|||
|
|||
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.... |
#6
|
|||
|
|||
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.... |
#7
|
|||
|
|||
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.... |
#8
|
|||
|
|||
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.... |
#9
|
|||
|
|||
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.... |
#10
|
|||
|
|||
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.... |
|
Thread Tools | |
Display Modes | |
|
|