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!

 

[Rnkhch]

Here is the file: Blank power workbook1

It contains all my LAMBDAs. I have the syntax of ISXTYPE at B2.

At B7, I was trying to extract and transfer each of the MC parameters with INDEX, but of course it didn't work . I figured that would be my first step to solve, but so far I haven't succeeded. (I tried a few other failed ways for the parameter extraction, but I must have deleted them after being frustrated )

And I have the data types listed at the right.

Please let me know if you need any explanations or clarifications.

My goal is to add a parameter [value_if_types] as the last parameter of ISXTYPE such that each of the five "basic" data types can be entered (respectively) into it with MC (or any better way if possible). So for example, it would look like something like this:

=ISXTYPE(B4:M5,,,,,,,MC(B4:M5*5,"",0,"",100))

Thanks a lot!

 

[jasonb75]

I think that I'm following what you're trying to do now, using a psuedo formula to explain it, is this on the lines of what you're thinking.

CHOOSE(XTYPE(B4:M5,"basic"),B4:M5*5,"",0,"",100)

Effectively that is saying
if xtype = 1 then multiply by 100
if xtype = 2 then blank
if xtype = 3 then 0
if xtype = 4 then blank
if xtype = 5 then 100

My plans for the next few days have just changed again, I'll have a look at it when I can but with I think I need to try and look at this in more detail when I can get an hour or so without disturbances from people who seem to have more say in how I spend my free time than I do

 

[Rnkhch]

Yes, you got it right, except that fifth scenario is type 16 for errors:

if xtype = 1 then multiply by 100
if xtype = 2 then blank
if xtype = 3 then 0
if xtype = 4 then blank
if xtype = 16 then 100

Thanks for your help

(I have implicitly included a type 5 for "unknown" data types in XTYPE so that it can alert me if it encounters a type which is not number, text, blank, logical, or error.)

 

[jasonb75]

Hey, just a quick check in to see if you've been able to get anywhere with this one yet?

I've been looking over it between other things but I'm still not seeing any way of getting to where you want other than the pseudo syntax that I used a couple of posts up, although I don't think that will be of much use in the actual lambda that you're trying to create.

 

[Rnkhch]

Hi Jason, same here. Thanks! I wasn't able to make any progress either

Seems like there's no way of transferring parameters inside the function "intact". They get evaluated as soon as they are "reached"

Perhaps, I can make a post with a more direct title, asking if there's a way of transferring parameters inside a function.

 

[jasonb75]

I think I get it although I'm not 100% sure that I'm following what you mean by transferring parameters intact vs evaluating them, but in reality anything in a formula will be evaluated at the first encounter.

The only way that you might work around it is to pass the parameter as a string instead of anything meaningful, then you would have to evaluate it with EVAL(string) which is going to complicate things further and may not work anyway.

If the parameter is going to be passed between some of your functions then it will possibly mean some revisions to the other functions as well.

 

[Rnkhch]

what you mean by transferring parameters intact vs evaluating them... / If the parameter is going to be passed between some of your functions...

I meant to pass the parameter around, within the same function (not between different functions), as you also referred to in your second paragraph:

pass the parameter as a string instead of anything meaningful ... [and] evaluate it with EVAL(string)

Can you show me an example of how passing the parameter around as a string works and how EVAL(string) works? I think I may be able to figure something out if I see how it is done. Perhaps INDIRECT() can be useful to "rebuild" the "meaningful parameter" after the parameter is passed around as a string?

Thanks!

 

[jasonb75]

INDIRECT can only be used for a string that returns a range (also, it has to be a fixed range as I found out last week).

EVALUATE (I thought it was EVAL, but had it wrong) can be used for any string that returns something which is valid as a formula (or part of one), but you can't use it in cell, it has to be done as a named range.

Here's a simple example of it,

21-11-22.xlsx
	A	B
1		Value
2		1
3		2
4		3
5		4
6		5
7
8
9	Function	sum
10	Start cell	b2
11	End cell	b6
12
13	Result	15
Sheet3

Cell Formulas
Range	Formula
B13	B13	=STRFORM

For some reason the named range didn't come out as part of the mini sheet above, STRFORM is a named range referring to =EVALUATE(Sheet3!$B$9&"("&Sheet3!$B$10&":"&Sheet3!$B$11&")") Sheet3 refers to the mini sheet above. Note that Sheet name and absolute references are likely to be needed, without them it adjusts relative to the currently selected cell (or at least it appears to).

If using it as part of a formula, the rest of the formula must be valid. For example, with =INDEX(A:A,MATCH("Something",B:B,0)) you could evaluate the bold part as a string. =INDEX(A:A,SOMETHING) where SOMETHING is a named range that refers to =EVALUATE("match(""Something"",b:b,0)") note that if you need to use double quotes around text within the string then they must be doubled, so " becomes "" and "" for a blank becomes """"

Hopefully that makes enough sense to give you the idea of how it works.

 

[Rnkhch]

Thanks Jason. I played with it for some time last night and my initial didn't go anywhere, but I'll do more testing, and if I can make it work, I'll update.