Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Kristajea
 
Posts: n/a
Default 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   Report Post  
Duke Carey
 
Posts: n/a
Default

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   Report Post  
SongBear
 
Posts: n/a
Default

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   Report Post  
SongBear
 
Posts: n/a
Default

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   Report Post  
PC
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Trouble with Syntax - IF formula Bert via OfficeKB.com New Users to Excel 1 April 8th 05 01:00 AM
Howdo U copy a formula down a column, that uses data in another w. Need Help pasting a formula Excel Worksheet Functions 1 February 25th 05 07:04 PM
Howdo U copy a formula down a column, that uses data in another w. brantty Excel Worksheet Functions 0 February 25th 05 11:11 AM
Problem with VBA returning the contents of a long formula. [email protected] Excel Discussion (Misc queries) 2 February 23rd 05 01:14 AM
How do I use Range Names listed in a VLookup table in a formula? Essbasedvlpr32 Excel Worksheet Functions 3 December 15th 04 11:11 PM


All times are GMT +1. The time now is 06:19 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"