I was working on a reasonably complex formula and thought about how much easier it would be if Excel supported variables in formulas. With a quick search, I found your video and the answer. Those of us who have worked in spreadsheets for decades are *so* used to the multi-column approach, and combining formulas as you show; but this can produce unreadable and hard-to-maintain results. LET is super cool new functionality; it's perfect for those who have a programming mentality and want their formulas to be readable and maintainable. Thanks for sharing!
Hi Bill - Be sure to check out the new video on the LAMBDA function that was just announced. It improves on the variable concept. th-cam.com/video/_iVgCZs2A2o/w-d-xo.html
Love the new LET Function -- makes big calculations much easier. calculating my time sheet for Double time its based on the day, and one day a week it starts a 15:00 , so depends on when you come in and leave before or after that time... there are about 7 variables used to make this calc... love it
A function containing stored variables is a game-changer. The power of this thing is immense and seems limited only by our creativity. I see this from MS: "Names of variables align with valid names that can be used in the name manager. E.g. "a" is valid but "c" is not because it conflicts with R1C1 style references." I notice that, surprisingly, you can have a named range like ABE and then a variable like ABE in LET and there won't be a problem.
I was surprised that the intellisense was offering me variable names when I typed the formula. I am surprised you can have range names that match variable names. Seems confusing.
For the solution before LET Function This function can solve the same problem (222) =MID(A6,LARGE(IFERROR((MID(A6,ROW($A$1:$A$50),1)=" ")*ROW(INDIRECT("1:"&LEN(A6))),0),2)+1,MAX(IFERROR(SEARCH(" ",A6,ROW($A$1:$A$50)),""))-LARGE(IFERROR((MID(A6,ROW($A$1:$A$50),1)=" ")*ROW(INDIRECT("1:"&LEN(A6))),0),2)-1)
Lol you hit on my second complaint from last week, we need to be able to add in line comments in the formula itself to explain what each step does. Naming the variables in Let does help but in line comments are still needed. If I could use the combo of Let, alt+enter and comments all in one cell that would go a long way to making formulas readable for the next person.
When I'm using DAX, I often prefix my variables with underscores. I think Excel's formula bar could really benefit from font colouring for names, cell references, table references and the new variables. It also could be doing with being a bit bigger, and as you mentioned, be more implicit about the formula escaping the row to encourage the use of multiple lines and even tabbed lines in a similar fashion to DAX. Either way, it's fantastic news, and I love the syntax. I just went into Excel to see if I had the updates yet and it appears that when re-installing recently I didn't notice that you have to "change level" on the Office Insider option under Account in Excel. I was on Monthly Targeted rather than Insider. I'm waiting patiently for it to download. I'm not suggesting that the LET function will cure Corona Virus, but it's a step in the right direction.
@@MrXL Yes, just got it last night. On first inspection, it appears that the LET() function has its limitations or works differently than other formulas. Indeed, your episode 2316 challenge is a really good example for testing the LET() function I'd say. I'll check it further in case I'm talking nonsense!!! As you're a big deal in the Excel world ;.), do you know what the best way is to report my findings, or who to?
Hi Mr. Excel.. thanks for this real-world example using LET. It seems like somewhat of a trade-off. I've always used ALT-ENTER in an expanded formula bar to make vertical sense out of really long horizontal formulas. So.. that method is always available. LET seems to allow for some simplification at the expense of another layer of interpretation to manage.. like keeping track of variable names in VBA. I guess it depends on the complexity of the formula required to solve the problem. Looking forward to playing with it and seeing more examples of its usefulness. Thanks for all the great insights and learning. Thumbs up for MrExcel.com!!
Very insightful, Wayne. Right now, I find that I can build the formula in steps and the consolidate into a single (impossible-to-understand) mega formula faster than I can build the LET function. My biggest problem: while you are in the middle of building a formula in the formula bar, you can't go look at other formulas. There were a few earlier versions of this long video. In the first, I was trying to build the LET from scratch, and I had to keep putting an apostrophe before the equals sign to go look at other formulas. In the second version, I typed everything into the cells and then re-typed it into the formula bar. The TEXTJOIN trick in the final video was my third iteration. It is one of those questions... "Do I take longer to build this formula so more people can understand it later?" Not always the same answer depending on the situation.
1 more unique formula to extract 2nd last word: FILTERXML(""&SUBSTITUTE(C3," ","")&"","a/a["&LEN(C3)-LEN(SUBSTITUTE(C3," ",""))&"]") where C3 contains the text to extract...the length of the formula is just 106 characters..I hope it is new to many of our excel lovers... I guess a tutorial will be required to explain this formula...
This is a good one. If you have five minutes, you should post that in response to www.mrexcel.com/board/threads/how-to-return-the-second-from-last-word-in-a-cell.457816/
Thanks, Mr Excel!!!!!
Awaiting Videos, hopefully soon. :) :)
I was working on a reasonably complex formula and thought about how much easier it would be if Excel supported variables in formulas. With a quick search, I found your video and the answer. Those of us who have worked in spreadsheets for decades are *so* used to the multi-column approach, and combining formulas as you show; but this can produce unreadable and hard-to-maintain results. LET is super cool new functionality; it's perfect for those who have a programming mentality and want their formulas to be readable and maintainable. Thanks for sharing!
Hi Bill - Be sure to check out the new video on the LAMBDA function that was just announced. It improves on the variable concept. th-cam.com/video/_iVgCZs2A2o/w-d-xo.html
Love the new LET Function -- makes big calculations much easier. calculating my time sheet for Double time its based on the day, and one day a week it starts a 15:00 , so depends on when you come in and leave before or after that time... there are about 7 variables used to make this calc... love it
Sir, nowadays your video is not coming on TH-cam. Are you ok?
A function containing stored variables is a game-changer. The power of this thing is immense and seems limited only by our creativity. I see this from MS: "Names of variables align with valid names that can be used in the name manager. E.g. "a" is valid but "c" is not because it conflicts with R1C1 style references." I notice that, surprisingly, you can have a named range like ABE and then a variable like ABE in LET and there won't be a problem.
I was surprised that the intellisense was offering me variable names when I typed the formula. I am surprised you can have range names that match variable names. Seems confusing.
Mr. Jelen, can you please share the excel sheet for this example with us
See the link in the video description.
@@MrXL Thanks very much Sir
I love these new Excel tools that have been rolled out over the last several months. LET is going to help a lot.
wow, thanks for putting an example of LET together
Thank You Bill for Let Function and this Formula
For the solution before LET Function
This function can solve the same problem (222)
=MID(A6,LARGE(IFERROR((MID(A6,ROW($A$1:$A$50),1)=" ")*ROW(INDIRECT("1:"&LEN(A6))),0),2)+1,MAX(IFERROR(SEARCH(" ",A6,ROW($A$1:$A$50)),""))-LARGE(IFERROR((MID(A6,ROW($A$1:$A$50),1)=" ")*ROW(INDIRECT("1:"&LEN(A6))),0),2)-1)
Nice function! It will change syntax in most functions from now on!
Impressive !! Thank you, Mr. Excel !!
Lol you hit on my second complaint from last week, we need to be able to add in line comments in the formula itself to explain what each step does. Naming the variables in Let does help but in line comments are still needed.
If I could use the combo of Let, alt+enter and comments all in one cell that would go a long way to making formulas readable for the next person.
When I'm using DAX, I often prefix my variables with underscores. I think Excel's formula bar could really benefit from font colouring for names, cell references, table references and the new variables. It also could be doing with being a bit bigger, and as you mentioned, be more implicit about the formula escaping the row to encourage the use of multiple lines and even tabbed lines in a similar fashion to DAX.
Either way, it's fantastic news, and I love the syntax. I just went into Excel to see if I had the updates yet and it appears that when re-installing recently I didn't notice that you have to "change level" on the Office Insider option under Account in Excel. I was on Monthly Targeted rather than Insider. I'm waiting patiently for it to download.
I'm not suggesting that the LET function will cure Corona Virus, but it's a step in the right direction.
So you have it now? I am expecting big reductions in those formulas you created! :-)
@@MrXL Yes, just got it last night. On first inspection, it appears that the LET() function has its limitations or works differently than other formulas. Indeed, your episode 2316 challenge is a really good example for testing the LET() function I'd say. I'll check it further in case I'm talking nonsense!!! As you're a big deal in the Excel world ;.), do you know what the best way is to report my findings, or who to?
WOW... this is VAR of #DAX in Excel!👍
Love it, thank you❤
Hi Mr. Excel.. thanks for this real-world example using LET. It seems like somewhat of a trade-off. I've always used ALT-ENTER in an expanded formula bar to make vertical sense out of really long horizontal formulas. So.. that method is always available. LET seems to allow for some simplification at the expense of another layer of interpretation to manage.. like keeping track of variable names in VBA. I guess it depends on the complexity of the formula required to solve the problem. Looking forward to playing with it and seeing more examples of its usefulness. Thanks for all the great insights and learning. Thumbs up for MrExcel.com!!
Very insightful, Wayne. Right now, I find that I can build the formula in steps and the consolidate into a single (impossible-to-understand) mega formula faster than I can build the LET function. My biggest problem: while you are in the middle of building a formula in the formula bar, you can't go look at other formulas. There were a few earlier versions of this long video. In the first, I was trying to build the LET from scratch, and I had to keep putting an apostrophe before the equals sign to go look at other formulas. In the second version, I typed everything into the cells and then re-typed it into the formula bar. The TEXTJOIN trick in the final video was my third iteration. It is one of those questions... "Do I take longer to build this formula so more people can understand it later?" Not always the same answer depending on the situation.
@@MrXL Yeah.. your TEXTJOIN trick is very cool.. thanks for sharing that one!!
1 more unique formula to extract 2nd last word: FILTERXML(""&SUBSTITUTE(C3," ","")&"","a/a["&LEN(C3)-LEN(SUBSTITUTE(C3," ",""))&"]") where C3 contains the text to extract...the length of the formula is just 106 characters..I hope it is new to many of our excel lovers... I guess a tutorial will be required to explain this formula...
This is a good one. If you have five minutes, you should post that in response to www.mrexcel.com/board/threads/how-to-return-the-second-from-last-word-in-a-cell.457816/
Sir i am waiting
Waiting for ?
caret