Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Trouble with formula
I am trying to get the following formula to work:
=IF(OR(P13=835,P13=384,P13=386,P13=364),(CONCATENA TE(P13,R13,0,0)),(IF(P13=585,(CONCATENATE(583,R13, 0,0))),(IF(P13=627,(CONCATENATE(0,30,R13,0,0))),(I F(P13=7,(CONCATENATE(0,17,R13,0,0))),(IF(P13=646,( CONCATENATE(147,R13,0,0)))))))) IF P13 = anything other than the numbers in the first section of the formula, I get a #VALUE error. I am using this to assist a data entry function. |
#2
|
|||
|
|||
OK, let's get rid of the CONCATENATE() functions and the surplus parens.
=IF(OR(P13=835,P13=384,P13=386,P13=364),P13&R13&"0 0",IF(P13=585,"583"&R13&"00",IF(P13=627,"030"&R13& "00",IF(P13=7,"017"&R13&"00",IF(P13=646,"147"&R13& "00",""))))) As a general rule, don't use parens when they aren't needed. They make it very easy to make typos and terribly difficult to troubleshoot a lengthy formula. "Kristajea" wrote: I am trying to get the following formula to work: =IF(OR(P13=835,P13=384,P13=386,P13=364),(CONCATENA TE(P13,R13,0,0)),(IF(P13=585,(CONCATENATE(583,R13, 0,0))),(IF(P13=627,(CONCATENATE(0,30,R13,0,0))),(I F(P13=7,(CONCATENATE(0,17,R13,0,0))),(IF(P13=646,( CONCATENATE(147,R13,0,0)))))))) IF P13 = anything other than the numbers in the first section of the formula, I get a #VALUE error. I am using this to assist a data entry function. |
#3
|
|||
|
|||
Kristajea...that's a nice name.
try this, you missplaced the next if-then-else inside of the wrong set of )))))). You were outside of three, and you only needed two. =IF(OR(P13=835,P13=384,P13=386,P13=364),(CONCATENA TE(P13,R13,0,0)),(IF(P13=585,(CONCATENATE(583,R13, 0,0)),(IF(P13=627,(CONCATENATE(0,30,R13,0,0)),(IF( P13=7,(CONCATENATE(0,17,R13,0,0)),(IF(P13=646,(CON CATENATE(147,R13,0,0)),CONCATENATE("xxx",R13,"0"," 0")))))))))) Let us know if this helped. SongBear "Kristajea" wrote: I am trying to get the following formula to work: =IF(OR(P13=835,P13=384,P13=386,P13=364),(CONCATENA TE(P13,R13,0,0)),(IF(P13=585,(CONCATENATE(583,R13, 0,0))),(IF(P13=627,(CONCATENATE(0,30,R13,0,0))),(I F(P13=7,(CONCATENATE(0,17,R13,0,0))),(IF(P13=646,( CONCATENATE(147,R13,0,0)))))))) IF P13 = anything other than the numbers in the first section of the formula, I get a #VALUE error. I am using this to assist a data entry function. |
#4
|
|||
|
|||
Also, you forgot the last else statement, for which i had substituted another
concatenation, so, even though the question has already been answered, just for completeness...this would also work... =IF(OR(P13=835,P13=384,P13=386,P13=364),(CONCATENA TE(P13,R13,0,0)),(IF(P13=585,(CONCATENATE(583,R13, 0,0)),(IF(P13=627,(CONCATENATE(0,30,R13,0,0)),(IF( P13=7,(CONCATENATE(0,17,R13,0,0)),(IF(P13=646,(CON CATENATE(147,R13,0,0)),"Error"))))))))) "SongBear" wrote: Kristajea...that's a nice name. try this, you missplaced the next if-then-else inside of the wrong set of )))))). You were outside of three, and you only needed two. =IF(OR(P13=835,P13=384,P13=386,P13=364),(CONCATENA TE(P13,R13,0,0)),(IF(P13=585,(CONCATENATE(583,R13, 0,0)),(IF(P13=627,(CONCATENATE(0,30,R13,0,0)),(IF( P13=7,(CONCATENATE(0,17,R13,0,0)),(IF(P13=646,(CON CATENATE(147,R13,0,0)),CONCATENATE("xxx",R13,"0"," 0")))))))))) Let us know if this helped. SongBear "Kristajea" wrote: I am trying to get the following formula to work: =IF(OR(P13=835,P13=384,P13=386,P13=364),(CONCATENA TE(P13,R13,0,0)),(IF(P13=585,(CONCATENATE(583,R13, 0,0))),(IF(P13=627,(CONCATENATE(0,30,R13,0,0))),(I F(P13=7,(CONCATENATE(0,17,R13,0,0))),(IF(P13=646,( CONCATENATE(147,R13,0,0)))))))) IF P13 = anything other than the numbers in the first section of the formula, I get a #VALUE error. I am using this to assist a data entry function. |
#5
|
|||
|
|||
Give this a try
=IF(OR(P13=835,P13=384,P13=386,P13=364),P13&R13&"0 0",IF(P13=585,583&R13&"00" ,IF(P13=627,0&30&R13&"00",IF(P13=7,0&17&R13&"00",I F(P13=646,147&R13&"00","NO MATCH"))))) HTH PC "Kristajea" wrote in message ... I am trying to get the following formula to work: =IF(OR(P13=835,P13=384,P13=386,P13=364),(CONCATENA TE(P13,R13,0,0)),(IF(P13=5 85,(CONCATENATE(583,R13,0,0))),(IF(P13=627,(CONCAT ENATE(0,30,R13,0,0))),(IF( P13=7,(CONCATENATE(0,17,R13,0,0))),(IF(P13=646,(CO NCATENATE(147,R13,0,0))))) ))) IF P13 = anything other than the numbers in the first section of the formula, I get a #VALUE error. I am using this to assist a data entry function. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble with Syntax - IF formula | New Users to Excel | |||
Howdo U copy a formula down a column, that uses data in another w. | Excel Worksheet Functions | |||
Howdo U copy a formula down a column, that uses data in another w. | Excel Worksheet Functions | |||
Problem with VBA returning the contents of a long formula. | Excel Discussion (Misc queries) | |||
How do I use Range Names listed in a VLookup table in a formula? | Excel Worksheet Functions |