I've been working with Excel for 25 years and as a data analyst for the last 11 of them and I got tremendous value from this video. These are the types of in depth advanced knowledge things that I've been lookin for. I love having a piece of knowledge in the back of my head like this. It doesn't get used often but when the time arises it is like magic.
Been waiting for this one! I hope that some of the old formulas/ones like this that don't work in dynamic arrays are updated in a future version of excel.
Yes, I’ve got a whole wishlist of things I’d like them to straighten out like this - so EDATE etc would work with ranges, and COUNTIF and friends would work with calculated arrays (and so that an array with one element would be treated the same as the one element if it’s passed to a function). I look back on the first Excel training I ever gave now, and it’s amazing how much of it is hack-y workarounds for things that were hard then but are easy with native functions now. I’m sure someday we’ll look back on this phase as ‘growing pains’ for dynamic arrays.
Dear Diarmuid, I enter the 1st formula, transcribed below, in my Excel 365, but the result is the first item in column B (Х-МN12 21). Therefore, there is neither a replacement for P1МN12 21 nor a spill. What could be wrong? Thanks for any help. =REDUCE(B3:B11,SEQUENCE(ROWS(E3:F10)),LAMBDA(a,v,SUBSTITUTE(a,INDEX(E3:E10,v),INDEX(F3:F10,v)))) 🤗
I believe some of the TEXTSPLIT formulas also struggle with array/range inputs. I'll have to try some of these tricks to see if they work in these contexts.
There are lots of flavors of array struggles… I don’t think these tricks will help with the likes of TEXTSPLIT, although I’d love to hear about it if it does! This Wyn Hopkins video explains a good workaround for making TEXTSPLIT work with a range (as long as the total length of the text isn’t too big): th-cam.com/video/QSfmmAJXlbA/w-d-xo.html
Very interesting and useful as always, thanks. I'm reading that potentially the "single" function is removed from M365 and replaced with the @ you also discussed. Do you know if single is removed or maybe just 'hidden'?
I think @ and SINGLE are synonymous. I assume they were introduced at the same time, but I’m not sure if that. The SINGLE function seems to be hidden, in that it won’t auto-complete as you type, but it’s there if you know to look for it.
Amazing! Only, the beef I have with CELL is that it doesn't translate well to other-language-Excels. CELL("filename") will remain just that if you were to send the file over to me. And then it throws an error at my end because it wants CELL("bestandsnaam").
Good point, I never thought of that! I guess that’s why they’ve written a lot of the newer ones to take an index number in that kind of situation (e.g. to choose a function in SUBTOTAL or AGGREGATE).
Ugh, that’s a bummer… sorry! There’s a link to Owen’s original video in the video description. It’s not a long one, but the bit I was playing is around the 2:30 mark.
Fascinating, thanks! I've noticed that SINGLE doesn't even autocomplete when you're typing a formula. I assume this is because it's a hidden / removed function, do you know of any other functions like this which are potentially worth looking into?
The only other hidden one I know of (I think?) is DATEDIF - you can use it to work out differences between dates in human-friendly format (e.g. 3 years, 4 months, 10 days). Bill Jelen has a good explainer video for it.
I've been working with Excel for 25 years and as a data analyst for the last 11 of them and I got tremendous value from this video. These are the types of in depth advanced knowledge things that I've been lookin for. I love having a piece of knowledge in the back of my head like this. It doesn't get used often but when the time arises it is like magic.
Thank you for giving me my Excel nerd fix for the day!
Been waiting for this one! I hope that some of the old formulas/ones like this that don't work in dynamic arrays are updated in a future version of excel.
Yes, I’ve got a whole wishlist of things I’d like them to straighten out like this - so EDATE etc would work with ranges, and COUNTIF and friends would work with calculated arrays (and so that an array with one element would be treated the same as the one element if it’s passed to a function).
I look back on the first Excel training I ever gave now, and it’s amazing how much of it is hack-y workarounds for things that were hard then but are easy with native functions now. I’m sure someday we’ll look back on this phase as ‘growing pains’ for dynamic arrays.
This is such an intriguing video. Thanks for making and sharing this all.
Gracias, siempre aprendo algo nuevo con tus videos, muchas gracias por compartir tu conocimiento. Thanks Diarmuid
... thank you for the inspiring and informative video 👏👏👏
Llegue a este canal por *EXCEL HECHO FACIL* y me suscribí, gracias
Bienvenido!
Dear Diarmuid,
I enter the 1st formula, transcribed below, in my Excel 365, but the result is the first item in column B (Х-МN12 21). Therefore, there is neither a replacement for P1МN12 21 nor a spill. What could be wrong?
Thanks for any help.
=REDUCE(B3:B11,SEQUENCE(ROWS(E3:F10)),LAMBDA(a,v,SUBSTITUTE(a,INDEX(E3:E10,v),INDEX(F3:F10,v)))) 🤗
I believe some of the TEXTSPLIT formulas also struggle with array/range inputs. I'll have to try some of these tricks to see if they work in these contexts.
There are lots of flavors of array struggles… I don’t think these tricks will help with the likes of TEXTSPLIT, although I’d love to hear about it if it does!
This Wyn Hopkins video explains a good workaround for making TEXTSPLIT work with a range (as long as the total length of the text isn’t too big):
th-cam.com/video/QSfmmAJXlbA/w-d-xo.html
I always wondered why some of these functions do not work in every case, only if I paste as values! Thanks :D
Very interesting and useful as always, thanks. I'm reading that potentially the "single" function is removed from M365 and replaced with the @ you also discussed. Do you know if single is removed or maybe just 'hidden'?
I think @ and SINGLE are synonymous. I assume they were introduced at the same time, but I’m not sure if that.
The SINGLE function seems to be hidden, in that it won’t auto-complete as you type, but it’s there if you know to look for it.
Amazing! Only, the beef I have with CELL is that it doesn't translate well to other-language-Excels. CELL("filename") will remain just that if you were to send the file over to me. And then it throws an error at my end because it wants CELL("bestandsnaam").
Good point, I never thought of that! I guess that’s why they’ve written a lot of the newer ones to take an index number in that kind of situation (e.g. to choose a function in SUBTOTAL or AGGREGATE).
Great content!
Thanks Levi! Long time no see - I hope you’re well.
@@DimEarly you too, Dim
Very interesting. FYI, we cannot hear the audio you are attempting to play at 12:17
Ugh, that’s a bummer… sorry! There’s a link to Owen’s original video in the video description. It’s not a long one, but the bit I was playing is around the 2:30 mark.
Fascinating, thanks! I've noticed that SINGLE doesn't even autocomplete when you're typing a formula. I assume this is because it's a hidden / removed function, do you know of any other functions like this which are potentially worth looking into?
The only other hidden one I know of (I think?) is DATEDIF - you can use it to work out differences between dates in human-friendly format (e.g. 3 years, 4 months, 10 days). Bill Jelen has a good explainer video for it.
I just wanna laugh at the title 😂
(Team sub who rang the bell)
I have to have my fun somehow : )
And yes, it looks like you got here about 15 seconds after I posted! 😂