Convert Text to a Formula in Excel | The CRAZY method for tough Excel problems.
Vložit
- čas přidán 7. 07. 2024
- ★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Blog post ★
exceloffthegrid.com/convert-s...
★ About this video ★
We can convert text to a range using the INDIRECT function, so there has got to be a function to convert text to a formula... right?
Well... no... yes, kind of!
So let's find out how to convert Text to a formula in Excel.
0:00 Introduction
0:24 EVALUATE function
1:24 EVALUATE/LAMBDA combination
3:09 How to automate Excel
3:24 Use case
4:45 VBA User Defined Function
5:58 Wrap-up
★ Download 30 most useful Excel VBA Macros ebook for FREE ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel
Mark you're killing it! Awesome Production
What can I say… I’ve learned from the best (i.e. You 😁)
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.
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 😁
Madness, beautiful madness❤
Thanks 😁
Wow, it's very cool that you can build a function dynamically and execute it. Thanks!
Glad you like it!
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... 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 CZcams with that particular function.
"Access Analytic" channel: czcams.com/video/SiT1tRkQL3U/video.htmlfeature=shared
Check at 06:16.
Thank you for your reply.
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 🤔
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.
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.
Brilliant !
Thanks 😁
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.
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
Very nice
Thanks 😁
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…
It doesn't change the formula length limit - it merely executes the formula. So any formula issues which exist before will continue to exist.
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!
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