Lambda Without Paramaters N/A Error

[kheldar]

Hello,

I've got a pretty complex set of lambdas referencing child lambdas.

=LAMBDA(LET(calc,ModStart($S$3,A2#),
IFS(ModMatchStart($S$3,A2#)>0,calc,
ModMatchStart($S$3-1,A2#)>0,ModStart($S$3-1,A2#)/1440,
ModMatchStart($S$3-2,A2#)>0,ModStart(S3-2,A2#)/1440,
ModMatchStart($S$3-3,A2#)>0,ModStart(S3-3,A2#)/1440,
ModMatchStart($S$3-4,A2#)>0,ModStart($S$3-4,A2#)/1440,
ModMatchStart($S$3-5,A2#)>0,ModStart($S$3-5,A2#)/1440,
ModMatchStart($S$3-6,A2#)>0,ModStart(S3-6,A2#)/1440,
ModMatchStart($S$3-7,A2#)>0,ModStart($S$3-7,A2#)/1440,
ModMatchStart($S$3-8,A2#)>0,ModStart($S$3-8,A2#)/1440,
ModMatchStart($S$3-9,A2#)>0,ModStart($S$3-9,A2#)/1440)))()

This is the lambda to call other lambdas. It works on the grid. However, if I call it in another cell with

=LambdaModStart()

 I get N/A Error.

All others lambdas are working as intended. Including this one but as I said it works only on grid.

What could be going wrong?

Also, I know this could be done with a loop but I couldn't wrap my head around recursive lambdas.

I'd really appreciate your help.

Thanks.

[#2]

I can't wrap my head around what you are trying to do but what I do see is that you are using IFS(). As I started my experiments with LAMBDA I also used IFS() and found out that it does not check the condition(s) and then perform the corresponding action, it will calculate every parameter in the set and then perform the IFS action. I don't know if that is your problem but I had a recursive action so the LAMBDA kept calling itself even though it should have stopped when it reached my target value, the IFS() would try to calculate all the terms and therefor kept recursively calling itself until it reached its stack limit and failed.

[#3]

Hmm... But on the grid it works as intended. Could it be still the same issue?

[#4]

I don't know. I don't know what each of those Lambda functions (ModMatchStart, ModStart, LambdaModStart) are and I don't know what you mean by 'on the grid' mean vs. what?

[#5]

I mean if I call the lambda in a cell with its formulas using () at the end, it works. That famous Excel expert woman uses that term so I learnt it from her 🙂 I don't know what else it's called.

[#6]

ohhhh I get it. So when that huge Lambda formula is in a cell it works but if it is in the Name Manager / Advance Formula Editor (which one did you use?) and call it 'LambdaModStart' then call =LambdaModStart() it doesn't work, right?
So on the sheet those references will work but in the name manager they might need sheet reference or maybe that would depend on the scope of that name.

[#7]

@kheldar 

Sorry but I haven't got the remotest idea what the formula is meant to achieve.  I don't know what is returned by A2#, nor what value S3 may contain.  I would need to see what

ModMatchStart

ModStart

refer to.  At first sight, the initial Lambda and the null parameter string does nothing but return the LET and the LET itself does very little and could be eliminated.

[#8]

@kheldar 

If you have lambda function named abc defined as =LAMBDA(1+2) when both

=abc()

=LAMBDA(1+2)()

shall work. How exactly it is defined in Name Manger, with function call () or without?

[#9]

@SergeiBaklan 

This is how it's set up in name manager. It doesn't include () at the end.

=LAMBDA(LET(calc,ModStart('Real Time'!$S$3,'Real Time'!A2#),IFS(ModMatchStart('Real Time'!$S$3,'Real Time'!A2#)>0,calc,ModMatchStart('Real Time'!$S$3-1,'Real Time'!A2#)>0,ModStart('Real Time'!S3-1,'Real Time'!A2#)/1440,
ModMatchStart('Real Time'!$S$3-2,'Real Time'!A2#)>0,ModStart('Real Time'!S3-2,'Real Time'!A2#)/1440,ModMatchStart('Real Time'!$S$3-3,'Real Time'!A2#)>0,ModStart('Real Time'!S3-3,'Real Time'!A2#)/1440,
ModMatchStart('Real Time'!$S$3-4,'Real Time'!A2#)>0,ModStart('Real Time'!$S$3-4,'Real Time'!A2#)/1440,ModMatchStart('Real Time'!$S$3-5,'Real Time'!A2#)>0,ModStart('Real Time'!$S$3-5,'Real Time'!A2#)/1440,
ModMatchStart('Real Time'!$S$3-6,'Real Time'!A2#)>0,ModStart('Real Time'!S3-6,'Real Time'!A2#)/1440,ModMatchStart('Real Time'!$S$3-7,'Real Time'!A2#)>0,ModStart('Real Time'!$S$3-7,'Real Time'!A2#)/1440,
ModMatchStart('Real Time'!$S$3-8,'Real Time'!A2#)>0,ModStart('Real Time'!$S$3-8,'Real Time'!A2#)/1440,
ModMatchStart('Real Time'!$S$3-9,'Real Time'!A2#)>0,ModStart('Real Time'!$S$3-9,'Real Time'!A2#)/1440)))

 I use

=LambdaModStart()

 To call it and I get an N/A error.

I'll try to share a sample file.

[#10]

@PeterBartholomew1 

I'll try to prepare a sample file tonight. I know LET can be eliminated but it helps me visualize things better.

ModMatchStart = identifies if there is a matching value.
ModStart = retrieves the matching value.

This is ModMatchStart:

=LAMBDA(start,name,COUNTIFS(RawMMP[Name],name,RawMMP[Start],start,RawMMP[Non-Mod],"Mod"))

 This is ModStart:

=LAMBDA(start,name,SUMIFS(RawMMP[Duration1],RawMMP[Name],name,RawMMP[Start],start,RawMMP[Non-Mod],"Mod"))

[#11]

@mtarler 

This is how it's entered in name manager.

=LAMBDA(LET(calc,ModStart('Real Time'!$S$3,'Real Time'!A2#),IFS(ModMatchStart('Real Time'!$S$3,'Real Time'!A2#)>0,calc,ModMatchStart('Real Time'!$S$3-1,'Real Time'!A2#)>0,ModStart('Real Time'!S3-1,'Real Time'!A2#)/1440,
ModMatchStart('Real Time'!$S$3-2,'Real Time'!A2#)>0,ModStart('Real Time'!S3-2,'Real Time'!A2#)/1440,ModMatchStart('Real Time'!$S$3-3,'Real Time'!A2#)>0,ModStart('Real Time'!S3-3,'Real Time'!A2#)/1440,
ModMatchStart('Real Time'!$S$3-4,'Real Time'!A2#)>0,ModStart('Real Time'!$S$3-4,'Real Time'!A2#)/1440,ModMatchStart('Real Time'!$S$3-5,'Real Time'!A2#)>0,ModStart('Real Time'!$S$3-5,'Real Time'!A2#)/1440,
ModMatchStart('Real Time'!$S$3-6,'Real Time'!A2#)>0,ModStart('Real Time'!S3-6,'Real Time'!A2#)/1440,ModMatchStart('Real Time'!$S$3-7,'Real Time'!A2#)>0,ModStart('Real Time'!$S$3-7,'Real Time'!A2#)/1440,
ModMatchStart('Real Time'!$S$3-8,'Real Time'!A2#)>0,ModStart('Real Time'!$S$3-8,'Real Time'!A2#)/1440,
ModMatchStart('Real Time'!$S$3-9,'Real Time'!A2#)>0,ModStart('Real Time'!$S$3-9,'Real Time'!A2#)/1440)))

I enter the formula as :

=LAMBDA(LET(calc,ModStart($S$3,A2#),IFS(ModMatchStart($S$3,A2#)>0,calc,ModMatchStart($S$3-1,A2#)>0,ModStart(S3-1,A2#)/1440,ModMatchStart($S$3-2,A2#)>0,ModStart(S3-2,A2#)/1440,ModMatchStart($S$3-3,A2#)>0,ModStart(S3-3,A2#)/1440,ModMatchStart($S$3-4,A2#)>0,ModStart($S$3-4,A2#)/1440,ModMatchStart($S$3-5,A2#)>0,ModStart($S$3-5,A2#)/1440,ModMatchStart($S$3-6,A2#)>0,ModStart(S3-6,A2#)/1440,ModMatchStart($S$3-7,A2#)>0,ModStart($S$3-7,A2#)/1440,ModMatchStart($S$3-8,A2#)>0,ModStart($S$3-8,A2#)/1440,ModMatchStart($S$3-9,A2#)>0,ModStart($S$3-9,A2#)/1440)))()

But name manager automatically converts cell references to cell references including sheet references as displayed above.

[#12]

@kheldar 

We now have various versions of your formula but one thing I noticed is the mix of absolute and relative references ($S$3 and S3) which would give inconsistent results that depend upon the active cell.  

I can't help thinking that the list of 10 cases in the IFS formula couldn't be replaced by an array (maybe with an additional REDUCE).

[#13]

@kheldar So i still wonder if the IFS is giving an issue.  It would be so much easier if you provided a sample sheet with the formulas.  I know you don't want some fancy loop or reiteration, but that said, couldn't you at least make your formula much more simple and readable:

=LAMBDA(LET(calc,IFS(
ModMatchStart('Real Time'!$S$3,'Real Time'!A2#)>0,0,
ModMatchStart('Real Time'!$S$3-1,'Real Time'!A2#)>0,1,
ModMatchStart('Real Time'!$S$3-2,'Real Time'!A2#)>0,2,
ModMatchStart('Real Time'!$S$3-3,'Real Time'!A2#)>0,3,
ModMatchStart('Real Time'!$S$3-4,'Real Time'!A2#)>0,4,
ModMatchStart('Real Time'!$S$3-5,'Real Time'!A2#)>0,5,
ModMatchStart('Real Time'!$S$3-6,'Real Time'!A2#)>0,6,
ModMatchStart('Real Time'!$S$3-7,'Real Time'!A2#)>0,7,
ModMatchStart('Real Time'!$S$3-8,'Real Time'!A2#)>0,8,
ModMatchStart('Real Time'!$S$3-9,'Real Time'!A2#)>0,9),
ModStart('Real Time'!$S$3-calc,'Real Time'!A2#)/1440))

and if the ModStart() of a value that doesn't pass the ModMatchStart() is causing the problem this will also fix that.

It is also much more efficient (but not the most efficient option) because it doesn't try to calculate ModStart() for all 9 other/wrong values (which is why I hope this may fix your problem).