Trouble with formula

K

Kristajea

I am trying to get the following formula to work:

=IF(OR(P13=835,P13=384,P13=386,P13=364),(CONCATENATE(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,(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.
 
D

Duke Carey

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&"00",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.
 
S

SongBear

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),(CONCATENATE(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,(CONCATENATE(147,R13,0,0)),CONCATENATE("xxx",R13,"0","0"))))))))))

Let us know if this helped.
SongBear
 
S

SongBear

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),(CONCATENATE(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,(CONCATENATE(147,R13,0,0)),"Error")))))))))
 
P

PC

Give this a try

=IF(OR(P13=835,P13=384,P13=386,P13=364),P13&R13&"00",IF(P13=585,583&R13&"00"
,IF(P13=627,0&30&R13&"00",IF(P13=7,0&17&R13&"00",IF(P13=646,147&R13&"00","NO
MATCH")))))

HTH

PC
 
Top