Topics: 1. (00:00) Introduction. 2. (00:31) SMALL Function. 3. (01:10) SMALL & UNIQUE function. Works only in Microsoft 365 Excel. 4. (01:46) SMALL, MIN and IF Functions. Works in any version of Excel. 5. (04:03) FREQUENCY, IF and SMALL Functions. Works in any version of Excel. 6. (05:42) Pick out the second Biggest Number using LARGE. 7. (06:10) End Video Links
Hi Mike.. another great one. I like the MIN MIN formula.. did not think of that one. UNIQUE makes it so easy now. Thanks for revisiting old reliable FREQUENCY. All good fun and good learning. Thumbs up!!
FREQUNCY does many, many amazing things and used to be the foundation for many advanced aarrray formulas before the new Microsoft Excel ... The video link at the end for FREQUNCY is a really good Old School Formula Video.
Thanks for the video Mike! Those are great example. That trick where you used Frequency to find the unique list of values and falses where there are duplicates =(IF(FREQUENCY(C3:C19,C3:C19),C3:C19)) made my day. Thank you!
Just for some of us who haven't gotten the subscription version of Excel and are using the 2010 or later versions, here is a variation of the FREQUENCY based formula that doesn't need to be array entered (just press Enter): =AGGREGATE(15,6,C3:C19/(FREQUENCY(C3:C19,C3:C19)>0),G3) Note that cell G3 contains the Kth argument value.
I learned something new in the Old School department today: didn’t know that about Frequency(). Very nice. Although, as usual, those Old School formulas feel like “Artificial Programming” and thus like “magic”. And that aspect is taken away by the dynamic array / new calc engine formulas. Makes it far more intuitive and easy to use for everyone. On a less serious note: be careful on how you describe things: I mean: using Small’s sister, and she’s Large... I can see why, but it’s a little risqué ... ;-) LOL!
Awesome trick to combine FREQUENCY and IF functions!! I encountered this similar problem before amd created a helping table with FREQUENCY and then used Small fn. Never realised how IF fn could come handy to avoid a separate table. Hats off sir!!
Thamks Mike. I'm grateful for two things...1) I have Office 355 and 2) ExcelIsFun is always available to solve my challenges. Thanks for the great video....
Sure, but I am pretty sure FREQUENCY is much faster. I did research for my Ctrl + Shift + Enter book, and FREQUENCY was always faster than SUMPRODUCT for Unique Formuals...
Boom!Great Tutorial Loved The Way You Used The FREQUENCY Function.The New Calc Engine In 365 Certainly Makes Formulas So Much Easier Though...Thank You Mike :)
Hi, Thanks for sharing up this trick. I encountered with #N/A when checked the formula at if stage. Ultimately at the end of the formula not getting the required output. Thanks
Hello. , I have been searching on the internet on how to convert polar to rectangle coordinates in excel. Do you have any videos I can watch for this procedure? If not, can you make one? I need this for work and I'm not finding much success in this matter? Thank you so much. Keep up the awesome work!!!
Hello Sir, I'm doing masters in statistics and I want to become a data analyst. To accomplish my goal I have to learn Excel from an expert. And finally I found out your RUclips channel... Here are a lot of videos and playlist... Now I'm not understand from where to start... Can you suggest to me a sequence of playlist from which I should start to learn... Right now I'm *Zero* in Excel... Thankyou so much to build this amazing Channel.
Watch this 2 minute intro video that teaches about my channel, then from my home page start with 1) Excel Basics, 3) Advanced Excel, 3) Data Analysis Basics, 4) Advanced Data Analysis, then 5) Statistics. Here is the 2 minute welcome video: ruclips.net/video/l1-1aVgFth4/видео.html
The following trick will enable you to find the Nth SMALLEST number using the….LARGE(!!!!) function. So if, for example, N=4 (we want to find the 4th smallest number), then our parameter in cell G4 will be: 4 The following formula, using the SMALL function (which is, of course, the straightforward solution) with the same data that were used in the video: =SMALL(UNIQUE(C3:C19),G4) Will result in: 5 (the fourth smallest number)
However, to achieve the same goal (to find the fourth smallest number) we can use the….LARGE function, thus: =LARGE(UNIQUE(C3:C19),COUNT(UNIQUE(C3:C19))-G4+1) And the result? Yes, you guessed right: 5
As alternative for new UNIQUE function such construction like MODE.MULT(CHOOSE {1,1},IF(FREQUENCY (ARRAY,ARRAY),ARRAY)) can be used. It was surprise for me that IF(FREQUENCY()...) or IF(MMULT()...) or sometimes IF(N(INDEX(..,0))...) have no possibility CSE.))) I'm sorry for my English... It's hard for me. And many thanks for You, Mike! Cause my Excel started from your book about arrays and CSE!
@@user-ji8mj6tm3h Yes one of the rules in my Ctrl + Shift + Enter book is about how many times the Array Functions used in other aggregate functions do not require Ctrl + Shift + Enter, but is was not 100 % consistent. So I guess FREQUENCY is working that way. It is nicer in Microsoft 365 Excel because we never have to worry about CSE anymore : )
@@user-ji8mj6tm3h I love your alternative formula with MODE.MULT and MMULT : ) : ) The MODE.MULT is such a cool way to get a unique list from an array of unique numbers and FALSEs.
Topics:
1. (00:00) Introduction.
2. (00:31) SMALL Function.
3. (01:10) SMALL & UNIQUE function. Works only in Microsoft 365 Excel.
4. (01:46) SMALL, MIN and IF Functions. Works in any version of Excel.
5. (04:03) FREQUENCY, IF and SMALL Functions. Works in any version of Excel.
6. (05:42) Pick out the second Biggest Number using LARGE.
7. (06:10) End Video Links
Awesome! I'm so happy to have 365 and UNIQUE ;). Frequency trick is ♥♥♥. Thanks for sharing!
You are welcome for the share, Malina C. : )
Incredible Mike! Great job!
Thanks Chris : ) : ) : )
Great comparison of old and new functions
Glad you like it, Vida : )
Hi Mike.. another great one. I like the MIN MIN formula.. did not think of that one. UNIQUE makes it so easy now. Thanks for revisiting old reliable FREQUENCY. All good fun and good learning. Thumbs up!!
Yes, indeed, Wayne either way we can have fun with FREQUENCY or UNIQUE : ) : )
I like the trick using Frequency. You are an excellent teacher Mike. Thank you .. Salim
FREQUNCY does many, many amazing things and used to be the foundation for many advanced aarrray formulas before the new Microsoft Excel ... The video link at the end for FREQUNCY is a really good Old School Formula Video.
Thanks for the video Mike! Those are great example. That trick where you used Frequency to find the unique list of values and falses where there are duplicates =(IF(FREQUENCY(C3:C19,C3:C19),C3:C19)) made my day. Thank you!
Just for some of us who haven't gotten the subscription version of Excel and are using the 2010 or later versions, here is a variation of the FREQUENCY based formula that doesn't need to be array entered (just press Enter): =AGGREGATE(15,6,C3:C19/(FREQUENCY(C3:C19,C3:C19)>0),G3)
Note that cell G3 contains the Kth argument value.
Love your AGGREGATE : )
Very smart and simple.
Thanks, Mike for the great video.
You are welcome for teh great video : )
I learned something new in the Old School department today: didn’t know that about Frequency(). Very nice.
Although, as usual, those Old School formulas feel like “Artificial Programming” and thus like “magic”.
And that aspect is taken away by the dynamic array / new calc engine formulas. Makes it far more intuitive and easy to use for everyone.
On a less serious note: be careful on how you describe things: I mean: using Small’s sister, and she’s Large... I can see why, but it’s a little risqué ... ;-) LOL!
This is keen insight, Geert: Old School formulas feel like “Artificial Programming” and thus like “magic”. : ) : ) : )
Thanks Mike. Keep your head down up there.
You are welcome - keep your head down too, WRH : )
Impressive, as always :-) Thanks, Mike!!
Thanks, teammate : ) : ) : )
I love your explanation, thanks you so much Mr Mike
Glad you love it, Ogwal : )
Awesome trick to combine FREQUENCY and IF functions!! I encountered this similar problem before amd created a helping table with FREQUENCY and then used Small fn. Never realised how IF fn could come handy to avoid a separate table. Hats off sir!!
Yes, you are welcome, Amardeep! It is all in my Ctrl + Shift + Enter book at amazon : )
Excellent video Mike. Thanks for sharing those tricks.
You are welcome for the tricks, Ivan!!!
Great mike briliant frequency it is fun to have the solution from the old school :-)
Thamks Mike. I'm grateful for two things...1) I have Office 355 and 2) ExcelIsFun is always available to solve my challenges. Thanks for the great video....
You are welcome, Matt : )
Awesome trick using the frequency function. Great video Mike :)
Glad you like the FREQUENCY, Nader!!! The FREQUENCY video link at the end is pretty epic.
So Many ways shown in a Single video.Super sir
You are welcome for the many super ways, SIMFINSO!!!!
Wow....amazing. You make working with Excel fun. Thanks so much
Glad we can have fun with Excel together, Nono Obott!!!
you are the legend. Thanks for your time and effort
You are welcome for the time and effort, Amin : )
Thanks for the video!
You are welcome, Teammate : ) : ) : ) : )
Amazing as always Mike!
Glad you like it, Edge : )
Thank you for your hard work, you have helped so many people.
You are welcome!
Thanks Mike. Frequency was awesome. Loved all of them. : ) : )
Glad you liked them all, but that is to be expected, you being the Formula Guy : ) : )
Always great videos!
Glad they are great for you, renegadek!!!
Another great video. I was wondering if you could do this with SUMPRODUCT and the double minuses?
Sure, but I am pretty sure FREQUENCY is much faster. I did research for my Ctrl + Shift + Enter book, and FREQUENCY was always faster than SUMPRODUCT for Unique Formuals...
Boom!Great Tutorial Loved The Way You Used The FREQUENCY Function.The New Calc Engine In 365 Certainly Makes Formulas So Much Easier Though...Thank You Mike :)
FREQUENCY is one of the original power house BOOM Functions - going way back : )
Hi,
Thanks for sharing up this trick. I encountered with #N/A when checked the formula at if stage.
Ultimately at the end of the formula not getting the required output.
Thanks
Thank You Mick
Cool Trick FREQUENCY & IF
this my try
=MIN(IF(C3:C19>SMALL(C3:C19,1),C3:C19))
with 2nd Smallest Number or Biggest only
Cool, thanks : )
Thank you so much!
You are welcome so much, Emilio : )
Awesome as always
Glad it is awesome for you, Alan C : )
Tha'ts Amazing ... thanks Mike
You are welcome for the amazing, Hussein : )
Hello. , I have been searching on the internet on how to convert polar to rectangle coordinates in excel. Do you have any videos I can watch for this procedure? If not, can you make one? I need this for work and I'm not finding much success in this matter? Thank you so much. Keep up the awesome work!!!
Thanks Mike
You are welcome, Dave Bowman : ) : )
This is what I need, thanks for solving my problem
You are welcome, demon : )
Fantastic; Cheers !
Thanks, Sachin : )
Thanks Mike
my try...
=SMALL(C3::C19,COUNTIF(C3:C19,MIN(C3:C19))+1)
Very Nice!
you are superb sir!!!
Glad you like it, hassan : ) : )
Hello Sir, I'm doing masters in statistics and I want to become a data analyst. To accomplish my goal I have to learn Excel from an expert. And finally I found out your RUclips channel... Here are a lot of videos and playlist... Now I'm not understand from where to start... Can you suggest to me a sequence of playlist from which I should start to learn... Right now I'm *Zero* in Excel... Thankyou so much to build this amazing Channel.
Watch this 2 minute intro video that teaches about my channel, then from my home page start with 1) Excel Basics, 3) Advanced Excel, 3) Data Analysis Basics, 4) Advanced Data Analysis, then 5) Statistics. Here is the 2 minute welcome video: ruclips.net/video/l1-1aVgFth4/видео.html
Thank You so much... Thanks a lot ❤😊
Thank you sir!
You are welcome, Paul!
Finding the Nth smallest number using….LARGE
The following trick will enable you to find the Nth SMALLEST number using the….LARGE(!!!!) function.
So if, for example, N=4 (we want to find the 4th smallest number), then our parameter in cell G4 will be: 4
The following formula, using the SMALL function (which is, of course, the straightforward solution) with the same data that were used in the video:
=SMALL(UNIQUE(C3:C19),G4)
Will result in: 5 (the fourth smallest number)
However, to achieve the same goal (to find the fourth smallest number) we can use the….LARGE function, thus:
=LARGE(UNIQUE(C3:C19),COUNT(UNIQUE(C3:C19))-G4+1)
And the result?
Yes, you guessed right: 5
Wow ♥
: )
Nice. I notice that the Frequency of your videos is back to normal these days...
FREQUENCY is great : )
Hi I have two find unique items in a single column from two columns ( both columns are in separate sheets), is there any way to do this
Hi, Mike!
Great old school trick with FREQUENCY function!
Doesn't it work without CSE?)))
Not in older versions of Excel. If you have the new Calculation Engine, you do not need Ctrl + Shift + Enter. What version are you using?
@@excelisfun 2010, and it works without CSE)
As alternative for new UNIQUE function such construction like MODE.MULT(CHOOSE {1,1},IF(FREQUENCY (ARRAY,ARRAY),ARRAY)) can be used.
It was surprise for me that IF(FREQUENCY()...) or IF(MMULT()...) or sometimes IF(N(INDEX(..,0))...) have no possibility CSE.)))
I'm sorry for my English... It's hard for me.
And many thanks for You, Mike! Cause my Excel started from your book about arrays and CSE!
@@user-ji8mj6tm3h Yes one of the rules in my Ctrl + Shift + Enter book is about how many times the Array Functions used in other aggregate functions do not require Ctrl + Shift + Enter, but is was not 100 % consistent. So I guess FREQUENCY is working that way. It is nicer in Microsoft 365 Excel because we never have to worry about CSE anymore : )
@@user-ji8mj6tm3h I love your alternative formula with MODE.MULT and MMULT : ) : ) The MODE.MULT is such a cool way to get a unique list from an array of unique numbers and FALSEs.