Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 8th 05, 11:41 AM
ALex
 
Posts: n/a
Default 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   Report Post  
Old April 8th 05, 12:31 PM
Jerry W. Lewis
 
Posts: n/a
Default

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   Report Post  
Old April 8th 05, 12:54 PM
JulieD
 
Posts: n/a
Default

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   Report Post  
Old April 8th 05, 01:11 PM
Duke Carey
 
Posts: n/a
Default

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   Report Post  
Old April 8th 05, 01:47 PM
bj
 
Posts: n/a
Default

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   Report Post  
Old April 8th 05, 01:50 PM
Domenic
 
Posts: n/a
Default

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   Report Post  
Old April 8th 05, 01:55 PM
bj
 
Posts: n/a
Default

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   Report Post  
Old April 11th 05, 01:04 PM
Jerry W. Lewis
 
Posts: n/a
Default

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   Report Post  
Old April 11th 05, 07:41 PM
Harlan Grove
 
Posts: n/a
Default

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   Report Post  
Old April 12th 05, 02:10 PM
Jerry W. Lewis
 
Posts: n/a
Default

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?





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
3 questions about automated c++ com add-in worksheet functions gert Excel Worksheet Functions 0 March 10th 05 09:57 AM
# of Functions per cell SUB-ZERO Excel Worksheet Functions 3 January 23rd 05 10:35 PM
PivotTable canned functions doco Excel Discussion (Misc queries) 0 January 14th 05 03:52 PM
How to load Engineering Functions into the Fx function wizard? jsaval Excel Worksheet Functions 1 November 11th 04 09:47 PM
Where can I see VBA code for financial functions? eios Excel Worksheet Functions 1 November 2nd 04 01:00 PM


All times are GMT +1. The time now is 11:53 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017