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.
@excelrobot4 ай бұрын
Thank you for giving me my Excel nerd fix for the day!
@juangs11684 ай бұрын
Llegue a este canal por *EXCEL HECHO FACIL* y me suscribí, gracias
@DimEarly4 ай бұрын
Bienvenido!
@ExcelHechoFacil4 ай бұрын
Gracias, siempre aprendo algo nuevo con tus videos, muchas gracias por compartir tu conocimiento. Thanks Diarmuid
@HadynWiseman4 ай бұрын
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.
@DimEarly4 ай бұрын
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.
@HauiUSA4 ай бұрын
This is such an intriguing video. Thanks for making and sharing this all.
@DataVisualisation4 ай бұрын
... thank you for the inspiring and informative video 👏👏👏
@jldocherty4 ай бұрын
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'?
@DimEarly4 ай бұрын
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.
@mikedulrich4 ай бұрын
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.
@DimEarly4 ай бұрын
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): kzbin.info/www/bejne/h4TJnqB3f72fmKM
@omb_884 ай бұрын
I always wondered why some of these functions do not work in every case, only if I paste as values! Thanks :D
@sledgehammer-productions4 ай бұрын
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").
@DimEarly4 ай бұрын
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).
@JoseAntonioMorato3 ай бұрын
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)))) 🤗
@willfreestone81124 ай бұрын
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?
@DimEarly4 ай бұрын
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.
@GCopenhaver4 ай бұрын
Very interesting. FYI, we cannot hear the audio you are attempting to play at 12:17
@DimEarly4 ай бұрын
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.
@LeviBailey4 ай бұрын
Great content!
@DimEarly4 ай бұрын
Thanks Levi! Long time no see - I hope you’re well.
@LeviBailey4 ай бұрын
@@DimEarly you too, Dim
@nytsiory4 ай бұрын
I just wanna laugh at the title 😂 (Team sub who rang the bell)
@DimEarly4 ай бұрын
I have to have my fun somehow : ) And yes, it looks like you got here about 15 seconds after I posted! 😂