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
|
|||
|
|||
up to 7 functions?
What type of formula would respond to the following conditions 12 (excel
seems to only permit up to 7...is it possible to get around that?): IF Any Cell in Column AA= A1 and Any Cell in Column AB =B1 then C1=555 IF Any Cell in Column AA= A2 and Any Cell Column AB =B1 then C1=666 IF Any Cell in Column AA= A3 and Any Cell Column AB =B1 then C1=777 IF Any Cell in Column AA= A1 and Any Cell in Column AB =B2 then C1=888 IF Any Cell in Column AA= A2 and Any Cell in Column AB =B2 then C1=999 IF Any Cell in Column AA= A3 and Any Cell Column AB =B2 then C1=000 IF Any Cell in Column AA= A1 and Any Cell in Column AB =B3 then C1=111 IF Any Cell in Column AA= A2 and Any Cell in Column AB =B3 then C1=222 IF Any Cell in Column AA= A3 and Any Cell Column AB =B3 then C1=333 IF Any Cell in Column AA= A1 and Any Cell in Column AB =B4 then C1=1212 IF Any Cell in Column AA= A2 and Any Cell in Column AB =B4 then C1=2323 IF Any Cell in Column AA= A3 and Any Cell Column AB =B4 then C1=4545 help me please? Alex |
#2
|
|||
|
|||
You would have to either write a function in VBA or use helper cells to
do it. I am not aware of any Excel function or combination of functions that can test for "Any cell in Column AA=A1" If you are willing to restrict to a subset of a column, then you could use an array formula like IF(COUNT(FIND(A1,AA1:AA100)),"found","not found") but you would quickly run out of nesting levels. Array formulas must be array entered (Ctrl-Shift-Enter). Jerry ALex wrote: What type of formula would respond to the following conditions 12 (excel seems to only permit up to 7...is it possible to get around that?): IF Any Cell in Column AA= A1 and Any Cell in Column AB =B1 then C1=555 IF Any Cell in Column AA= A2 and Any Cell Column AB =B1 then C1=666 IF Any Cell in Column AA= A3 and Any Cell Column AB =B1 then C1=777 IF Any Cell in Column AA= A1 and Any Cell in Column AB =B2 then C1=888 IF Any Cell in Column AA= A2 and Any Cell in Column AB =B2 then C1=999 IF Any Cell in Column AA= A3 and Any Cell Column AB =B2 then C1=000 IF Any Cell in Column AA= A1 and Any Cell in Column AB =B3 then C1=111 IF Any Cell in Column AA= A2 and Any Cell in Column AB =B3 then C1=222 IF Any Cell in Column AA= A3 and Any Cell Column AB =B3 then C1=333 IF Any Cell in Column AA= A1 and Any Cell in Column AB =B4 then C1=1212 IF Any Cell in Column AA= A2 and Any Cell in Column AB =B4 then C1=2323 IF Any Cell in Column AA= A3 and Any Cell Column AB =B4 then C1=4545 help me please? Alex |
#3
|
|||
|
|||
haven't had time to think through a solution, but i just wanted to respond
to Jerry's post about I am not aware of any Excel function or combination of functions that can test for "Any cell in Column AA=A1" the countif function can be used for this e.g. =COUNTIF(AA:AA,A1) will give you the number of times it is found and =IF(COUNTIF(AA:AA,A1)=1,"found',"not found") will give you whether or not it is found hopefully will have time for a more detailed answer later on ... -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Jerry W. Lewis" wrote in message ... You would have to either write a function in VBA or use helper cells to do it. I am not aware of any Excel function or combination of functions that can test for "Any cell in Column AA=A1" If you are willing to restrict to a subset of a column, then you could use an array formula like IF(COUNT(FIND(A1,AA1:AA100)),"found","not found") but you would quickly run out of nesting levels. Array formulas must be array entered (Ctrl-Shift-Enter). Jerry ALex wrote: What type of formula would respond to the following conditions 12 (excel seems to only permit up to 7...is it possible to get around that?): IF Any Cell in Column AA= A1 and Any Cell in Column AB =B1 then C1=555 IF Any Cell in Column AA= A2 and Any Cell Column AB =B1 then C1=666 IF Any Cell in Column AA= A3 and Any Cell Column AB =B1 then C1=777 IF Any Cell in Column AA= A1 and Any Cell in Column AB =B2 then C1=888 IF Any Cell in Column AA= A2 and Any Cell in Column AB =B2 then C1=999 IF Any Cell in Column AA= A3 and Any Cell Column AB =B2 then C1=000 IF Any Cell in Column AA= A1 and Any Cell in Column AB =B3 then C1=111 IF Any Cell in Column AA= A2 and Any Cell in Column AB =B3 then C1=222 IF Any Cell in Column AA= A3 and Any Cell Column AB =B3 then C1=333 IF Any Cell in Column AA= A1 and Any Cell in Column AB =B4 then C1=1212 IF Any Cell in Column AA= A2 and Any Cell in Column AB =B4 then C1=2323 IF Any Cell in Column AA= A3 and Any Cell Column AB =B4 then C1=4545 help me please? Alex |
#4
|
|||
|
|||
What's the order of priority here Alex? Or are your test results mutually
exclusive? What if values in column AA match A1 and A2 and/or A3? What if values in AB = B1 and/or B2 and/or B3? If there is no chance of multiple tests being true, you can use something like this =(AND(COUNTIF(A:A,A1)0,COUNTIF(AB:AB,B1)0) *555) + (AND(COUNTIF(A:A,A2)0,COUNTIF(AB:AB,B1)0) *666) + ---finish your other 10 conditions with the same order "ALex" wrote: What type of formula would respond to the following conditions 12 (excel seems to only permit up to 7...is it possible to get around that?): IF Any Cell in Column AA= A1 and Any Cell in Column AB =B1 then C1=555 IF Any Cell in Column AA= A2 and Any Cell Column AB =B1 then C1=666 IF Any Cell in Column AA= A3 and Any Cell Column AB =B1 then C1=777 IF Any Cell in Column AA= A1 and Any Cell in Column AB =B2 then C1=888 IF Any Cell in Column AA= A2 and Any Cell in Column AB =B2 then C1=999 IF Any Cell in Column AA= A3 and Any Cell Column AB =B2 then C1=000 IF Any Cell in Column AA= A1 and Any Cell in Column AB =B3 then C1=111 IF Any Cell in Column AA= A2 and Any Cell in Column AB =B3 then C1=222 IF Any Cell in Column AA= A3 and Any Cell Column AB =B3 then C1=333 IF Any Cell in Column AA= A1 and Any Cell in Column AB =B4 then C1=1212 IF Any Cell in Column AA= A2 and Any Cell in Column AB =B4 then C1=2323 IF Any Cell in Column AA= A3 and Any Cell Column AB =B4 then C1=4545 help me please? Alex |
#5
|
|||
|
|||
This will give an error message if there is not a match in AA or AB but try
putting this into C! =CHOOSE(IF(COUNTIF(AB:AB,B1)0,0,IF(COUNTIF(AB:AB, B2)0,3,IF(COUNTIF(AB:AB,B3)0,6,IF(COUNTIF(AB:AB, B4)0,9,12))))+IF(COUNTIF(AA:AA,A1)0,1,IF(COUNTIF (AA:AA,A2)0,2,IF(COUNTIF(AA:AA,A3)0,3,12))),555, 666,777,888,999,0,111,222,333,444,1212,2323,4545) If you need something in C1 when there is no match the equation could be modified. If there is always to be a match in one or both coulumns the equation can be simplified. "ALex" wrote: What type of formula would respond to the following conditions 12 (excel seems to only permit up to 7...is it possible to get around that?): IF Any Cell in Column AA= A1 and Any Cell in Column AB =B1 then C1=555 IF Any Cell in Column AA= A2 and Any Cell Column AB =B1 then C1=666 IF Any Cell in Column AA= A3 and Any Cell Column AB =B1 then C1=777 IF Any Cell in Column AA= A1 and Any Cell in Column AB =B2 then C1=888 IF Any Cell in Column AA= A2 and Any Cell in Column AB =B2 then C1=999 IF Any Cell in Column AA= A3 and Any Cell Column AB =B2 then C1=000 IF Any Cell in Column AA= A1 and Any Cell in Column AB =B3 then C1=111 IF Any Cell in Column AA= A2 and Any Cell in Column AB =B3 then C1=222 IF Any Cell in Column AA= A3 and Any Cell Column AB =B3 then C1=333 IF Any Cell in Column AA= A1 and Any Cell in Column AB =B4 then C1=1212 IF Any Cell in Column AA= A2 and Any Cell in Column AB =B4 then C1=2323 IF Any Cell in Column AA= A3 and Any Cell Column AB =B4 then C1=4545 help me please? Alex |
#6
|
|||
|
|||
One way would be to use a helper column. Try the following (formulas
need to be confirmed with CONTROL+SHIFT+ENTER , not just ENTER)... Helper column: AD1: =INDEX({555,666,777},MATCH(1,(COUNTIF(AA:AA,A1:A3) 0)*(COUNTIF(AB:AB,B1) 0),0)) AD2: =INDEX({888,999,0},MATCH(1,(COUNTIF(AA:AA,A1:A3)0 )*(COUNTIF(AB:AB,B2)0) ,0)) AD3: =INDEX({111,222,333},MATCH(1,(COUNTIF(AA:AA,A1:A3) 0)*(COUNTIF(AB:AB,B3) 0),0)) AD4: =INDEX({1212,2323,4545},MATCH(1,(COUNTIF(AA:AA,A1: A3)0)*(COUNTIF(AB:AB,B 4)0),0)) This column can be hidden, if so desired. Alternatively, you can choose 'White' as your font color to hide these cells. Desired result: AE1: =INDEX(AD:AD,MATCH(TRUE,AD1:AD4=0,0)) Hope this helps! In article , "ALex" wrote: What type of formula would respond to the following conditions 12 (excel seems to only permit up to 7...is it possible to get around that?): IF Any Cell in Column AA= A1 and Any Cell in Column AB =B1 then C1=555 IF Any Cell in Column AA= A2 and Any Cell Column AB =B1 then C1=666 IF Any Cell in Column AA= A3 and Any Cell Column AB =B1 then C1=777 IF Any Cell in Column AA= A1 and Any Cell in Column AB =B2 then C1=888 IF Any Cell in Column AA= A2 and Any Cell in Column AB =B2 then C1=999 IF Any Cell in Column AA= A3 and Any Cell Column AB =B2 then C1=000 IF Any Cell in Column AA= A1 and Any Cell in Column AB =B3 then C1=111 IF Any Cell in Column AA= A2 and Any Cell in Column AB =B3 then C1=222 IF Any Cell in Column AA= A3 and Any Cell Column AB =B3 then C1=333 IF Any Cell in Column AA= A1 and Any Cell in Column AB =B4 then C1=1212 IF Any Cell in Column AA= A2 and Any Cell in Column AB =B4 then C1=2323 IF Any Cell in Column AA= A3 and Any Cell Column AB =B4 then C1=4545 help me please? Alex |
#7
|
|||
|
|||
after reading Duke's comments, I did assume (shudder) that the order you put
the matches was the priority. It would be easy to change the equation for a different priority. "bj" wrote: This will give an error message if there is not a match in AA or AB but try putting this into C! =CHOOSE(IF(COUNTIF(AB:AB,B1)0,0,IF(COUNTIF(AB:AB, B2)0,3,IF(COUNTIF(AB:AB,B3)0,6,IF(COUNTIF(AB:AB, B4)0,9,12))))+IF(COUNTIF(AA:AA,A1)0,1,IF(COUNTIF (AA:AA,A2)0,2,IF(COUNTIF(AA:AA,A3)0,3,12))),555, 666,777,888,999,0,111,222,333,444,1212,2323,4545) If you need something in C1 when there is no match the equation could be modified. If there is always to be a match in one or both coulumns the equation can be simplified. "ALex" wrote: What type of formula would respond to the following conditions 12 (excel seems to only permit up to 7...is it possible to get around that?): IF Any Cell in Column AA= A1 and Any Cell in Column AB =B1 then C1=555 IF Any Cell in Column AA= A2 and Any Cell Column AB =B1 then C1=666 IF Any Cell in Column AA= A3 and Any Cell Column AB =B1 then C1=777 IF Any Cell in Column AA= A1 and Any Cell in Column AB =B2 then C1=888 IF Any Cell in Column AA= A2 and Any Cell in Column AB =B2 then C1=999 IF Any Cell in Column AA= A3 and Any Cell Column AB =B2 then C1=000 IF Any Cell in Column AA= A1 and Any Cell in Column AB =B3 then C1=111 IF Any Cell in Column AA= A2 and Any Cell in Column AB =B3 then C1=222 IF Any Cell in Column AA= A3 and Any Cell Column AB =B3 then C1=333 IF Any Cell in Column AA= A1 and Any Cell in Column AB =B4 then C1=1212 IF Any Cell in Column AA= A2 and Any Cell in Column AB =B4 then C1=2323 IF Any Cell in Column AA= A3 and Any Cell Column AB =B4 then C1=4545 help me please? Alex |
#8
|
|||
|
|||
Not sure what I was thinking when I used COUNT(FIND()) instead of
COUNTIF, but COUNTIF(), does not work if you pass an entire column as the range. Jerry JulieD wrote: haven't had time to think through a solution, but i just wanted to respond to Jerry's post about I am not aware of any Excel function or combination of functions that can test for "Any cell in Column AA=A1" the countif function can be used for this e.g. =COUNTIF(AA:AA,A1) will give you the number of times it is found and =IF(COUNTIF(AA:AA,A1)=1,"found',"not found") will give you whether or not it is found hopefully will have time for a more detailed answer later on ... |
#9
|
|||
|
|||
Jerry W. Lewis wrote...
Not sure what I was thinking when I used COUNT(FIND()) instead of COUNTIF, but COUNTIF(), does not work if you pass an entire column as the range. .... Assuming the comma immediately after COUNTIF() was a typo, your statement is incorrect if you're referring to the first argument. COUNTIF's first argument must be a single area range, so the problem with entire column ranges being converted to arrays is finessed. On the other hand, you can't use an entire column range as COUNTIF's second argument, so the standard idiom for counting distinct entries in a range, e.g., =SUMPRODUCT(1/COUNTIF(A:A,A:A)) won't work. For example, enter =ROW() in A1:A65536, and enter =COUNTIF(A:A,"4") and =COUNTIF(A:A,"100") in other cells. I get 65532 and 99, respectively. Don't you? |
#10
|
|||
|
|||
The OP's application was an entire column as the second argument.
COUNTIF will not work for that. Jerry Harlan Grove wrote: Jerry W. Lewis wrote... Not sure what I was thinking when I used COUNT(FIND()) instead of COUNTIF, but COUNTIF(), does not work if you pass an entire column as the range. ... Assuming the comma immediately after COUNTIF() was a typo, your statement is incorrect if you're referring to the first argument. COUNTIF's first argument must be a single area range, so the problem with entire column ranges being converted to arrays is finessed. On the other hand, you can't use an entire column range as COUNTIF's second argument, so the standard idiom for counting distinct entries in a range, e.g., =SUMPRODUCT(1/COUNTIF(A:A,A:A)) won't work. For example, enter =ROW() in A1:A65536, and enter =COUNTIF(A:A,"4") and =COUNTIF(A:A,"100") in other cells. I get 65532 and 99, respectively. Don't you? |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
User functions not automatically invoked in Excel 2000, but are i. | Sprinks | General Discussion | 1 | July 19th, 2005 02:44 PM |
# of Functions per cell | SUB-ZERO | Worksheet Functions | 3 | January 23rd, 2005 10:35 PM |
Workbook with big array functions slow to open first time in 2003 | Terry | General Discussion | 1 | September 2nd, 2004 05:00 PM |
Pointless rant about order of terms in functions | Pete McCosh | Worksheet Functions | 8 | March 17th, 2004 04:37 PM |
Would like to use more than 7 IF functions in a formula | Mike F. | Worksheet Functions | 2 | September 23rd, 2003 03:15 AM |