Forum Discussion
Lambda Without Paramaters N/A Error
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.
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.
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.
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).
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).
