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
|
|||
|
|||
multiple arrays in single statement
I think you've overcomplicated the example, which makes it hard to
reproduce; can't you articulate your problem using simply, e.g., =MATCH(JP,iRange,0) and =MATCH(JP,iArray,0)? And I'm not sure what you mean by '"JP" is an array'. And I can't sort out the meaning of "if U2 contains JP using the above statement it returns 0 but if I use an array with the same value it returns #N/A". What do you mean "If U2 contains JP?" If you use an array instead of what? In any event, if JP is a defined name referring to {"x","z"} and iRange refers to A1:C1 and contains x,y,z, and iArray is a defined name referring to {"x","y","z"} both =MATCH(JP,iRange,0) and =MATCH(JP,iArray,0) return 1 and 3 if array entered into a two cell row. What are you trying to do? Keep it simple. Alan Beban rudekid wrote: does anyone know if you can refer to more than one array in a single Excel statement? I can't find any references in Excel help or textbooks saying I can't but at the same time, can't get a statement to work unless I convert one of the arrays to a text lookup. It's a problem because I need to look up more than one reference. If anyone knows the answer or a way round it this would help me going the wrong way with fixing the problem. For the record the statement I am trying to get to work is as follows: =SUM(IF(NOT(ISERROR(MATCH("JP",RawData!U2,0))),0,I F(ISERROR(MATCH("*"&IllegalChars&"*",RawData!E2:P2 ,0)),0,1))) Where "JP" was an array. At the moment, if U2 contains JP using the above statement it returns 0 but if I use an array with the same value it returns #N/A ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
multiple arrays in single statement
"rudekid" wrote...
does anyone know if you can refer to more than one array in a single Excel statement? Yes, but the arrays need to conform to each other. For example, {1,2}*{3,4} = {3,8} {1;2}*{3;4} = {3;8} {1,2}*{3;4} = {3,6;4,8} {1;2}*{3,4} = {3,4;6,8} but {1,2,0}*{3,4} = {3,8,#N/A} while {1,2,0}*{3;4} = {3,6,0;4,8,0} I can't find any references in Excel help or textbooks saying I can't but at the same time, can't get a statement to work unless I convert one of the arrays to a text lookup. ... Textbooks? For the record the statement I am trying to get to work is as follows: =SUM(IF(NOT(ISERROR(MATCH("JP",RawData!U2,0))), 0, IF(ISERROR(MATCH("*"&IllegalChars&"*",RawData!E2: P2,0)),0,1))) Where "JP" was an array. At the moment, if U2 contains JP using the above statement it returns 0 but if I use an array with the same value it returns #N/A Do you mean that the formula above works, but =SUM(IF(NOT(ISERROR(MATCH({"JP"},RawData!U2,0))),0 , IF(ISERROR(MATCH("*"&IllegalChars&"*",RawData!E2:P 2,0)),0,1))) doesn't? Or do you mean something like =SUM(IF(NOT(ISERROR(MATCH({"JP","XY"},RawData!U2,0 ))),0, IF(ISERROR(MATCH("*"&IllegalChars&"*",RawData!E2:P 2,0)),0,1))) doesn't work? If you mean the former, then please show the *EXACT* formula that *DOESN'T* work, not a roughly similar formula that does work. If you mean the latter, then the answer is that your array in the 1st MATCH call is likely doen't conform to the range in the second match, RawData!E2:P2. Even so, I have to wonder if you're making a mistake putting the entire two-part IF expression inside SUM. The way you've written it, if the 1st argument to the 1st MATCH call is a scalar (i.e., neither an array nor a range) this gives a single (scalar) result, which if true returns 0 to SUM, and if false returns the result of the second, inner IF call. If that's what you want, you'd be better off using =IF(COUNTIF(RawData!U2,"JP"),0, SUMPRODUCT(--(COUNTIF(RawData!E2:P2,"*"&IllegalChars&"*")0))) On the other hand, if the 1st argument to the 1st MATCH call is an array, either it needs to conform to the 2nd argument to the 2nd MATCH call, or you need to explain in much greater detail what you're trying to accomplish in your first IF call. I can think of several alternatives. 1. 1st arg to 1st MATCH call and 2nd arg to 2nd MATCH call should conform. =SUMPRODUCT(COUNTIF(RawData!U2,JP_array), --(COUNTIF(RawData!E2:P2,"*"&IllegalChars&"*")0)) 2. 1st IF call should be construed as if any of the entries in JP_array match RawData!U2, then return 0 (period) else sum of the results from the 2nd IF call. IF(SUMPRODUCT(COUNTIF(RawData!U2,JP_array)),0, SUMPRODUCT(--(COUNTIF(RawData!E2:P2,"*"&IllegalChars&"*")0))) 3. 1st IF call should be construed as if all of the entries in JP_array match RawData!U2, then return 0 (period) else sum of the results from the 2nd IF call. IF(SUMPRODUCT(1-COUNTIF(RawData!U2,JP_array))=0,0, SUMPRODUCT(--(COUNTIF(RawData!E2:P2,"*"&IllegalChars&"*")0))) -- Never attach files. Snip unnecessary quoted text. Never multipost (though crossposting is usually OK). Don't change subject lines because it corrupts Google newsgroup archives. |
#3
|
|||
|
|||
multiple arrays in single statement
thanks for the help, will check these out in case bamboozled by function this is what I was trying to do: This is one of many functions designed to test for errors in address data. Previously the function merely looked at an array of cells containing dodgy characters then compared it with an array of address fields and returned an error of 1 no matter how many illegal characters it found or 0 if it found none. What I was trying to do is build an exception if the countries for those entries were either in Singapore or Japan. So, in my formula: "JP" was an array of 2 codes, JP and SG representing the countries Japan and Singapore "Illegalchars" is the array of illegal characters. So: =SUM(IF(NOT(ISERROR(MATCH("JP",RawData!U2,0))),0,I F(ISERROR(MATCH("*"&IllegalChars&"*",RawData!E2:P2 ,0)),0,1))) (was supposed to) check if the address was Singapore or Japan. If it was, return a 0. If it wasn't, then check the rest of the address for the usual criteria, i.e. were there any illegal characters? Entering the above just returned #N/A regardless of the data entered in U2. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|