Quick update on the Excel LET function. Two questions: Will it work with arrays and how is the formula speed? This video shows that it does work with arrays and the calc speed is faster.
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2326.
1/2: LET() speed and LET with arrays. Hey, just following up on those last two videos about the new LET function in Excel. Some questions that came in.
A) is it faster? B) does it work with arrays.
Yes, it definitely works with arrays, so here's an original data set: FILTER to Andy and you see that all those blank cells in column C end up as zeros.
Beautiful solution here.
So we say this variable "Results" is equal to the FITLER and then if Results equals 0 then quote quote otherwise Results and you get rid of the zeros. Very nice.
Is it faster? Alright so we have two tests here.
This is 250,000 phrases using the first formula from yesterday with all of these repeated items.
Let's see: go to Formulas, Calculation Options, Manual to switch to manual and then Profiler, Calc R range.
So 1994 milliseconds, just about 2 seconds to do those 250,000.
1979 milliseconds, so it's it's pretty consistent. Now let's compare that to 250,000 cells.
Using the new LET function.
Now just one side note here, opening this workbook took significantly longer.
I will have to report that to the Excel team.
You know we're still in beta here, so I'm not going to panic about that. Calculation manager will calc the range.
In 715 milliseconds, so almost 65% faster.
Try another one here, calc the range.
Alright 712 so we went from 1974 or so to 712.
That is an impressive timesaving, although I remember when I was calculating this up the other day.
There were some of these terms were repeated. 12, 4, 2 times and 4 times.
So, given that we now we're doing that one calculation 1/12th of the time I don't know -- I might have thought it would be even faster than that. Not complaining this is still in beta.
You know things in beta aren't always as fast as are going to be in the final version.
Again, please Subscribe and ring that bell Feel free to post any questions or comments down in the comments below. MrExcel 2020.
I'll be updating this book to include the LET function click that "I" in the top right hand corner.
If you buy the PDF now, you'll get the new version when it comes out. I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
1/2: LET() speed and LET with arrays. Hey, just following up on those last two videos about the new LET function in Excel. Some questions that came in.
A) is it faster? B) does it work with arrays.
Yes, it definitely works with arrays, so here's an original data set: FILTER to Andy and you see that all those blank cells in column C end up as zeros.
Beautiful solution here.
So we say this variable "Results" is equal to the FITLER and then if Results equals 0 then quote quote otherwise Results and you get rid of the zeros. Very nice.
Is it faster? Alright so we have two tests here.
This is 250,000 phrases using the first formula from yesterday with all of these repeated items.
Let's see: go to Formulas, Calculation Options, Manual to switch to manual and then Profiler, Calc R range.
So 1994 milliseconds, just about 2 seconds to do those 250,000.
1979 milliseconds, so it's it's pretty consistent. Now let's compare that to 250,000 cells.
Using the new LET function.
Now just one side note here, opening this workbook took significantly longer.
I will have to report that to the Excel team.
You know we're still in beta here, so I'm not going to panic about that. Calculation manager will calc the range.
In 715 milliseconds, so almost 65% faster.
Try another one here, calc the range.
Alright 712 so we went from 1974 or so to 712.
That is an impressive timesaving, although I remember when I was calculating this up the other day.
There were some of these terms were repeated. 12, 4, 2 times and 4 times.
So, given that we now we're doing that one calculation 1/12th of the time I don't know -- I might have thought it would be even faster than that. Not complaining this is still in beta.
You know things in beta aren't always as fast as are going to be in the final version.
Again, please Subscribe and ring that bell Feel free to post any questions or comments down in the comments below. MrExcel 2020.
I'll be updating this book to include the LET function click that "I" in the top right hand corner.
If you buy the PDF now, you'll get the new version when it comes out. I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
