Holy cow. This is lifechanging for me. I'm working with four workbooks where I was using Find & Replace to change cell references for MTD totals every month. Now I change the cell references in four cells and the entire workbook updates itself.
I use this!!!! Here's my use case: I have a table of assets, that includes their size attributes. I have a table where we have asset classes, and in this table we have a replacement cost formula. We have a field here with a sample size. So say we have a pipe, that is $10 per linear foot. The replacement cost formula in the classes table is =10*[@size] where we keep a sample linear footage. So we pull the formula into the assets table, using formulatext and I substitute the [@size] to the size field in the assets table, (something like [@size1]) so it displays "=10*1000" if that asset is 1000 feet, then I use this to force that new formula to calculate. I use the UDF format. I have a helper column that actually displays the formula as text before its calculated so people can see it and it's easy to catch if it's working properly.
Looking great Mark. That makes playing with conditions super easy, even the adjustments. Also the video looks fantastic. You’re getting even better at editing 🙏
Outstanding. In the past I've used the advanced filter function and VBA worksheet change event calls to accomplish this sort of filtering; this approach will be much smoother and more nearly transparent for my clients who are unaware of the advanced filter or who struggle to understand it.
Brilliant! By using the "old" macro I had to save the file as macro enabled. Before your awesome trick I was creating text like '=1+1 and then doing search =, replace by =.
This is a crazy good idea. I have been using VBA to do numerical analysis (say numerically integrating an arbitrary function over time). For the advanced methods the algorithm takes a bunch of values at different points in time over the domain (not at just one point). The algorithm is complicated but the same in all cases. Having to also code in the function as well makes it too specific. If I could somehow just put a text formula for the function into the UDF that would generalize the method it would be very much like how Matlab does it. I would love to have something like =Let(a,A1,b,A2,Start,A3,End,A4,integrate('a*t^2+b',a,b,Start,End,{"t"}). This is a simple case where you do not need numerical methods, but there lots of real world cases that have no general solutions. Now you have me thinking!
Before Lambdas, we could define a Name, let's call it Eval, with a Refers To formula of =EVALUATE(G20), and in another cell simply use =Eval to display the result. But this means we can only use it to evaluate the formula in G20 (which is hard-coded in the Refers To formula), so it's not as flexible as the Lambda. Maybe we should call this Name EvalG20, to distinguish it from other Evals we may need to define.
I find it crazy that LAMBDA now gives a new life to Excel 4 Macros. They should have been removed a long time ago. But they are still here and they still work.
Can this help get around the formula length limit? I ask in reference to setting up possible longer emails that can be sent out from Excel using formulas…
Because Excel 4 Macros, which is where EVALUATE came from, could be implemented in the name manager. So, it’s new functionality and very old functionality which both used the name manager both being combined together.
For the same reason INDIRECT needs to be volatile. The name of a of a cell reference is not in the calculation chain. So, needs to be volatile to ensure the calculation occurs when the inputs change.
Mine is giving me a value error because it is the product of a textjoin. I had to change several formulas but couldn't get head cell absolute references to work. Even when I special paste these to values only it adds a leading space so it doesn't recognize it as a formula. IDK the best way to fix it so i can just use my dang functions.
Wow... this is a whole new level... impressive! The closest thing I had ever seen about this was the use of Expression.Evaluate("1 + 1") in Power Query M language. Thank you for sharing!
It's a similar concept to Expression.Evaluate, but the VBA version at least has exists for the last 30 years. Have you ever had need to use Expression.Evaluate in Power Query?
@@ExcelOffTheGrid I have not. In fact, so far, I've come across with just one single video on UA-cam with that particular function. "Access Analytic" channel: ua-cam.com/video/SiT1tRkQL3U/v-deo.htmlfeature=shared Check at 06:16. Thank you for your reply.
Well actually this was a solution I created for an actual problem - a different scenario to the video, but also relied on the user entering the comparison operator as text. So I know there are use cases. Probably not many, but definitely some.
I don’t understand… we could create that filter condition by entering it directly, couldn’t we? Or is the idea that a user could input their own parameters into the table without knowing how to write formulas? This is quite cool and creative, though.
Yes - that is the example in the video. You don't want users to re-write a formula each time you want to add/remove a criteria. If it's for quick analysis, it ceases to be quick. Therefore, in the example you can add/remove criteria by typing the text into a box.
It's just an example of the technique, I'm sure there are loads of other ways. But is there an there an easy way to create a FILTER function with an unknown number of conditions?
Holy cow. This is lifechanging for me. I'm working with four workbooks where I was using Find & Replace to change cell references for MTD totals every month. Now I change the cell references in four cells and the entire workbook updates itself.
A big applause for being so daring with the content.
Thank you very much for telling in a tutorial: LAMBDA, EVALUATE and formulas.
Thanks Ivan - it’s an interesting technique that might help in a few scenarios.
I use this!!!!
Here's my use case:
I have a table of assets, that includes their size attributes.
I have a table where we have asset classes, and in this table we have a replacement cost formula. We have a field here with a sample size. So say we have a pipe, that is $10 per linear foot. The replacement cost formula in the classes table is =10*[@size] where we keep a sample linear footage.
So we pull the formula into the assets table, using formulatext and I substitute the [@size] to the size field in the assets table, (something like [@size1]) so it displays "=10*1000" if that asset is 1000 feet, then I use this to force that new formula to calculate. I use the UDF format. I have a helper column that actually displays the formula as text before its calculated so people can see it and it's easy to catch if it's working properly.
Mark you're killing it! Awesome Production
What can I say… I’ve learned from the best (i.e. You 😁)
you fixed my problem bro
Looking great Mark. That makes playing with conditions super easy, even the adjustments.
Also the video looks fantastic. You’re getting even better at editing 🙏
Thanks - I’ve been trying to up my editing game 😁
Outstanding. In the past I've used the advanced filter function and VBA worksheet change event calls to accomplish this sort of filtering; this approach will be much smoother and more nearly transparent for my clients who are unaware of the advanced filter or who struggle to understand it.
Great news - that is a use case I hand not considered , but it should wok. 💡
Wow, it's very cool that you can build a function dynamically and execute it. Thanks!
Glad you like it!
Brilliant! By using the "old" macro I had to save the file as macro enabled. Before your awesome trick I was creating text like '=1+1 and then doing search =, replace by =.
This is a crazy good idea. I have been using VBA to do numerical analysis (say numerically integrating an arbitrary function over time). For the advanced methods the algorithm takes a bunch of values at different points in time over the domain (not at just one point). The algorithm is complicated but the same in all cases. Having to also code in the function as well makes it too specific. If I could somehow just put a text formula for the function into the UDF that would generalize the method it would be very much like how Matlab does it. I would love to have something like =Let(a,A1,b,A2,Start,A3,End,A4,integrate('a*t^2+b',a,b,Start,End,{"t"}). This is a simple case where you do not need numerical methods, but there lots of real world cases that have no general solutions. Now you have me thinking!
Great stuff - glad I could get you thinking 🤔
Madness, beautiful madness❤
Thanks 😁
Crazy indeed 😅 But very good to know when the day comes. Thank's Marc!
Hopefully the day will never come, but when it does, you're ready.
Brilliant !
Thanks 😁
Great! But would it not work without converting it into volatile function by using now() within it?
It doesn’t calculate consistently without it.
I’m a sucker for a good Lambda formula! Cool trick for making it volatile. Loved the editing too, pro quality! What software do you use?
Thanks Erik 😁 I use Camtasia for recording and editing.
Before Lambdas, we could define a Name, let's call it Eval, with a Refers To formula of =EVALUATE(G20), and in another cell simply use =Eval to display the result. But this means we can only use it to evaluate the formula in G20 (which is hard-coded in the Refers To formula), so it's not as flexible as the Lambda. Maybe we should call this Name EvalG20, to distinguish it from other Evals we may need to define.
I find it crazy that LAMBDA now gives a new life to Excel 4 Macros. They should have been removed a long time ago. But they are still here and they still work.
Hi, very big thank you for the tip. Do you know if there is a limit to the amount of characters of the formula; the LEN() of my formula is 4942?
4,942!!! Woah 😱
I don’t know about limits. I’ve not been close to that level.
Nice, I'm surprised there's not already a built in function like this and now I'm wondering what other VBA can be run inside of a LAMBDA Function
Can this help get around the formula length limit? I ask in reference to setting up possible longer emails that can be sent out from Excel using formulas…
It doesn't change the formula length limit - it merely executes the formula. So any formula issues which exist before will continue to exist.
This is so very cool. Do you know why EVALUATE works when it's in LAMBDA?
Because Excel 4 Macros, which is where EVALUATE came from, could be implemented in the name manager.
So, it’s new functionality and very old functionality which both used the name manager both being combined together.
Could you please explain why you have to make that LAMBDA volatile?
For the same reason INDIRECT needs to be volatile.
The name of a of a cell reference is not in the calculation chain. So, needs to be volatile to ensure the calculation occurs when the inputs change.
Mine is giving me a value error because it is the product of a textjoin. I had to change several formulas but couldn't get head cell absolute references to work. Even when I special paste these to values only it adds a leading space so it doesn't recognize it as a formula. IDK the best way to fix it so i can just use my dang functions.
Very nice
Thanks 😁
Wow... this is a whole new level... impressive! The closest thing I had ever seen about this was the use of Expression.Evaluate("1 + 1") in Power Query M language. Thank you for sharing!
It's a similar concept to Expression.Evaluate, but the VBA version at least has exists for the last 30 years.
Have you ever had need to use Expression.Evaluate in Power Query?
@@ExcelOffTheGrid I have not. In fact, so far, I've come across with just one single video on UA-cam with that particular function.
"Access Analytic" channel: ua-cam.com/video/SiT1tRkQL3U/v-deo.htmlfeature=shared
Check at 06:16.
Thank you for your reply.
Does it work with Excel 2019?
The last VBA UDF example does, but the rest uses LAMBDA which is only available in Excel 2024 and Excel 365.
Very creative thinking but IMHO a solution looking for a problem.
Well actually this was a solution I created for an actual problem - a different scenario to the video, but also relied on the user entering the comparison operator as text.
So I know there are use cases. Probably not many, but definitely some.
Ifs there any situation when the fxTextToFormulaUDF would return #VALUE! with your "1+1" example
If you've not got VBA enabled in the workbook... I think that might be the error. Otherwise I can't re-create the issue.
@@ExcelOffTheGrid Doesn't help when I can't spell volatile. thanks for looking
I don’t understand… we could create that filter condition by entering it directly, couldn’t we? Or is the idea that a user could input their own parameters into the table without knowing how to write formulas? This is quite cool and creative, though.
Yes - that is the example in the video.
You don't want users to re-write a formula each time you want to add/remove a criteria. If it's for quick analysis, it ceases to be quick. Therefore, in the example you can add/remove criteria by typing the text into a box.
@@ExcelOffTheGrid great, thanks for your response!
Din work for me..
But i think the same answer we can have with simple filter function, not required either lamda or vba😊
It's just an example of the technique, I'm sure there are loads of other ways.
But is there an there an easy way to create a FILTER function with an unknown number of conditions?
@@ExcelOffTheGrid hmm..I can use this formula for the same answer =FILTER(G4:I12,(H4:H12=I17)*(I4:I12>I15)*(I4:I12