Is it possible to input an "array of expressions" as a reference in a formula parameter (or in a cell)?

[Rnkhch]

Hello,

I was playing with the general array syntax, i.e. ={}, and I realized that it only accepts elements of the main data types: numbers, texts, logicals, blanks, and errors, as follows:

={1,2,3}
={"a","b","c"}
={"","",""}
={TRUE,FALSE,TRUE}
={#DIV/0!,#N/A,#REF!}
={12,"book","",TRUE,#DIV/0!}

However, when I tried to input arrays of expressions as below, I couldn't ENTER them:
={A1,A2,A3}
={A1:B5,C3:C6}
={B2:B7*5,C6:D8/2}
etc.

Is it possible to write arrays like this?

The reason I thought about this was that I was thinking to create a LAMBDA such that it would do a particular operation based on whether the input reference is a number, text, blank, logical, or error based on this same exact index order, "number, text, blank, logical, error". But I don't want to include five extra parameters for my LAMBDA. I just want to include all five actions in one parameter like this:

=XXXX(reference,value_if_types)
where the user would write something like this:

=XXXX(A1:G1,{A1:G1*5,"",0,"",100})
which means if A1:G1 is number, multiply them by 5, if A1:G1 is text, return blank, if A1:G1 is blank, return 0, if A1:G1 is logical, return blank, and if A1:G1 is error, return 100.

So another (less specific) way of asking my question would be: Is it possible to condense a few parameters into one? (whether in the {} syntax or any other syntax)

Thanks for any input!

 

[jasonb75]

Not in a single function, you can use things like CHOOSE({1,2,3},A1,A2,A3) or VSTACK / HSTACK if you have them.

Failing that, the MC function that you created a few weeks back comes to mind as a LAMBDA that should work for this purpose.

 

[Rnkhch]

Thank you! Yes, I was thinking that MC is probably the only reasonable way to do this and also the most minimalistic in terms of user input.

I'm debating whether to make a new function or to include it in my ISXTYPE function. I might incorporate this as one extra optional parameter at the end of ISXTYPE with the label "[value_if_types]".

 

[Rnkhch]

Hi Jason,

I got stuck at the stage of extracting each of the five MC elements and using them in the main function of the LAMBDA (for now I'm working on a separate LAMBDA, but if I can get it to work, I'll incorporate it into ISXTYPE):

=XXXX(A1:G1,MC(A1:G1*5,"",0,"",100))

How can I extract each of these five elements and use them with (an indexed?) order? Here is the main function of the LAMBDA that I have so far (although not sure if this is correct yet or not because I can't proceed to test it ):

SWITCH(XTYPE(A1:G1),"dt:NUMBER",A1:G1*5,"dt:TEXT","","dt:BLANK",0,"dt:LOGICAL","","dt:ERROR",100)

In this function, XTYPE determines the type of each cell of the reference A1:G1, and I want this function to perform the correct action on the reference depending on the data type. (So in this example, if any of the cells in A1:G1 are number, they should be multiplied by 5; if they are text, they should appear as blank; if they are blank, they should appear as 0; if they are logical, they should appear as blank; and if they are error, they should appear as 100.)

Thanks for any input!

 

[jasonb75]

Are you still trying to figure this one out or have you been able to resolve it? I had forgotten all about this thread until just now when I noticed your XTYPE thread at the top of the LAMBDA subforum.

I did have a quick look at it last week but wasn't really able to make much sense of what was going on.

 

[Rnkhch]

It's great to hear from you! Yes, I'm still trying to figure this out and hopefully make my ISXTYPE function more powerful by adding one parameter at the end.

But no matter what I do, I cannot "intactly transfer" each of the parameters of MC to the inside of the ISXTYPE and work on them. Anything I tried so far causes the parameters to be "evaluated" thus losing their original syntax.

In fact, I was thinking to post a thread with a title like "Is it possible to transfer a parameter with its precise syntax to another part of a function and avoid it being evaluated?"

So if you can figure this out, it will be fabulous

Thank you!

I did have a quick look at it last week but wasn't really able to make much sense of what was going on.

Did you need me to explain anything? (wasn't sure if you were referring to this thread or perhaps XTYPE or ISXTYPE)

 

[jasonb75]

Did you need me to explain anything?

To be honest, I've looked at so many different things over the last week or so that I'm surprised I can remember who I am some days. I'm just about to pack up for the day but I'll have another look over it in the tomorrow and see if I can figure out what I'm looking at, then go from there.

 

[Rnkhch]

Sure, that's great! Please take your time and get a good rest.

 

[jasonb75]

I've looked over this a few times today in between other things and I'm sure that there is something simple that I'm overlooking but I'm just not getting my head around it.

Do you have a test workbook with this (and the other associated lambda functions) that you could upload to a fileshare site and post a link in the thread?

 

[Rnkhch]

Yes, I have all my functions in a template that I call "Blank power workbook" which I choose every time I start a new workbook (instead of the built-in blank). I have even put comments for each of my functions so they feel exactly like the native functions when you search a function name .

It's on my home computer. I'll see if I can access it during the day, but if not, I'll share it here tonight when I get home.