Dynamic array constants

[michalrosa]

Greeting all!
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Is it possible to create a dynamic array constant? For example lets say we have the following array formula =if(type={A,B,C},if(category={X,Y,Z},if(mode=”No”,value)))
<o> </o>
It works fine with pre-defined, hard-coded array constants, is it possible to create them as dynamic entities, for example defined as a string in some cell and to point to that cell, something like =if(type=A1,if(category=A2,if(mode=”No”,value))) where A1 and A2 would be array constants.

Cheers,
Michal

 

[mikerickson]

What happened when you tried it?

 

[michalrosa]

What happened when you tried it?

It didn't work.. otherwise I would not ask the question..

But I found the answer already, kinda obvious actually, guess I didn't think hard enough about it - http://www.ozgrid.com/forum/showthread.php?t=46593

Thanks for your time everyone
Michal

 

[mikerickson]

What are type, category, mode, A, B, C, X, Y, Z and value? Are they named ranges or labels.

You might try substituting the nested IF with an AND, perhaps arithemticaly expresses.

 

[mikerickson]

OK, after fiddling with this for a bit, I think I understand your question.
The value of A1 can be only a number, text, logical or an error value.
A single cell cannot contain an array.

 

[michalrosa]

OK, after fiddling with this for a bit, I think I understand your question.
The value of A1 can be only a number, text, logical or an error value.
A single cell cannot contain an array.

****, the solution I "found" does not actually work in my case - http://www.ozgrid.com/forum/showthread.php?t=46593, pity.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Let's simplify:
<o> </o>
Cells A1:A3 contain values 5000,5001,5002 and are named “codes”, cells B1:B3 contain values 1000,2000,4000 and are named “values”.
<o> </o>
Formula =SUM(IF(codes={5000,5001},values)) returns 3000 as expected, what I would like to do is to use a dynamic name instead of an array constant, so far =SUM(IF(codes=A1:A2,values)) failed, so did =SUM(IF(codes=cbd,values)) – cbd being defined as a named range A1:A2.

Still no luck.

Cheers,
Michal

 

[mikerickson]

I'm assuming that codes and values are named ranges. And that they are column-wise ranges (e.g. like B1:B10)

The explicit array {5000, 5001} is a row-wise explicit array

if you put 5000 in A1 and 5001 in A2

{=SUM(IF(codes=TRANSPOSE(A1:A2),values))} should work.

 

[michalrosa]

I'm assuming that codes and values are named ranges. And that they are column-wise ranges (e.g. like B1:B10)

The explicit array {5000, 5001} is a row-wise explicit array

if you put 5000 in A1 and 5001 in A2

{=SUM(IF(codes=TRANSPOSE(A1:A2),values))} should work.

Doh, good one. I haven’t done any serious work with matrices for a while, of course the IF function would baulk when trying to compare two one-dimensional arrays of different sizes so transposing the second array does the trick.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Well done, thank you.
<o> </o>
Cheers,
<?xml:namespace prefix = st1 ns = "urn:schemas:contacts" /><st1:GivenName w:st="on">Michal</st1:GivenName>