FREQUENCY Array Function for Quantitative Data. LET function to create full report! EMT 1693.
HTML-код
- Опубликовано: 25 июл 2024
- Download Excel File: excelisfun.net/files/EMT1693.xlsx
Learn how to use the FREQUNCY array function to make a frequency distribution for quantitative data. See how to use INDEX to remove the last category created by the FREQUENCY function. See how to use SEQUENCE function and how to create the labels that show the correct bin categories.
Second part of video: LET function to build entire frequency distribution report in a single cell.
Topics:
1. (00:00) Introduction.
2. (00:33) MAX Function
3. (00:50) CEILING.MATCH function to create upper limit in last category
4. (01:16)Number Bins formula
5. (01:26) SEQUENCE function to create all upper limits
6. (02:22) FREQUNCY array function
7. (02:55) INDEX to remove the last category created by the FREQUENCY function
8. (05:07) Create labels for report, easy way.
9. (05:37) Create labels for report, harder way, but more accurate. Use IF function to append different labels into one column.
10. (07:15) LET function single cell formula with variables and final report.
11. (12:47) Summary, Closing and Video Links
Topics:
1. (00:00) Introduction.
2. (00:33) MAX Function
3. (00:50) CEILING.MATCH function to create upper limit in last category
4. (01:16)Number Bins formula
5. (01:26) SEQUENCE function to create all upper limits
6. (02:22) FREQUNCY array function
7. (02:55) INDEX to remove the last category created by the FREQUENCY function
8. (05:07) Create labels for report, easy way.
9. (05:37) Create labels for report, harder way, but more accurate. Use IF function to append different labels into one column.
10. (07:15) LET function single cell formula with variables and final report.
11. (12:47) Summary, Closing and Video Links
Beautiful LETing! Fantastic solution.
If you're looking for some inspiration for your next video, you should find the story online about public health England's mistake with their coronavirus data. Apparently they were collating data from different test locations in their Excel spreadsheet. The data was received in CSV/txt files. The files, in some cases, had more than 1M rows. In order to combine all the data, they simply opened the files in Excel. Of course, when the number of rows of data exceeded that of their spreadsheet, Excel simply cut off the remaining rows, meaning they simply didn't account for some of the test data! Crazy. Clearly hadn't seen your power query videos! Anyway, thought I'd share that with you, to show you that there are plenty of people left who need your training and new book!
Thanks for the share. Most people in the world that are given data, can deal with it. What happened in your example happens all the time. I will keep your story and try to use it later : ) Thanks, Rico S : ) : ) : ) : )
Downward point arrow on a table! Nice tip for getting column from table! Dynamic Array Functions! LET function! All those gems in one video...thanks!
You are welcome, Teammate : ) : ) : )
Wow that was amazing Mike! Great video!!
Glad you like it, Chris : ) : ) : )
You made me crazy. Lots of variables in Let function. Excel is really fun with Mike.
Thanks.
You are welcome!!!!!!
I love array functions, great summary and it helped me with a few of my issues in Excel!
Great! Glad to help.
Thanks mike. The let formula is a sculpture. Amazing!!!
It is fun - since I use frequency distributions all the time, the new spilled arrays make it so much easier!!!
"Let sometimes is tricky" he says. That may be an understatement in this case..... That was mind boggling and superfastastic awesome in the same breath. It may take me another watch to grasp the Let. Thanks for sharing Mike, always appreciated
Yes, it seems eassier to do the steps in the cell, rather than LET, but talk about an all-in-one : ) : ) : )
Boom!Wow Great Lesson With Some Awesome FUNCTIONS/FORMULAS...Thank You Mike :)
You are welcome, darryl : ) : ) : ) Boom! : )
Hi Mike. Awesome lesson, as always. Great trick to eliminate the un-needed last row from FREQUENCY. The LET solution is wild.. one formula does it all. That's some Monday fun with modern Excel :)) Thanks for sharing. Thumbs up!!
You are welcome for the share! I started to use FILTER to get rid of last row, then relized INDEX would be shorter : ) Thanks for the Monday Thumbs Up!!!
@@excelisfun Hi Mike. I tried FILTER to include the FREQUENCY results 0, but that eliminated more than just the last 0. Curious how you would set it up to eliminate just the last item when some of the other bins might also be 0.
FILTER is more complicated than the INDEX solution . Something like : =FILTER(FREQUENCY(fSalesAnswer[Sales],F8#),SEQUENCE(D4+1)
@@excelisfun Thanks Mike!
Wow, Mike, Amazing, glad that you added this video based on your previous one. Very good how you labeled every class. This is how it should be done! For the extra 0, I would use a not so elegant solution: just do not display the 0 ...;)
Yes, but how do you not display the zero dynamically? You are welcome for the follow up, Bart : )
Phenomenal! And the dynamics of it are astounding! Great job! :-)
Wonderful video Mike, I liked the use of Index function. Spilled array is really a next level Excel. Cheers 👍
You are welcome for the wonderful, Sachin : ) : )
Just by the title alone, I know this video will be awesome. Like the {1,0} trick. I’ve been experimenting with LET just to push it to its limits and see what it can do
Ya, =IF({1,0},"You are welcome ","Patrick!!!")
ExcelIsFun I see you what you did there. 👍
I wonder if this trick could be modified slightly to something like if great than 0, then display an array (like a sequence of numbers), else display “Number”.
Example:
=IF(SEQUENCE(,5)
@@patrickschardt7724 Yes, it can. I have been using this trick and other similar ones in LET for the last year. We can use IFS too, when we have three or more things to mash togther : )
ExcelIsFun good to know. I’ll have explore this
@@patrickschardt7724 : )
Thank you MIke for this awesome video!
You are welcome, Teammate!
Great LET function!!!!!✌
Thanks, O Master of LET, cr gr0912 : ) : ) : ) : )
Great! Thanks for sharing.
My pleasure, Teammate!!!!
That's amazing ... thanks Mike
You are welcome, Husein !!!!
Thank you. Good job
Glad you like it : ) : )
Smart Mike, very smart !👍
Glad it is smart for you : )
This is kinda crazy, A bit lost, but I think you’ve done an outstanding job making it easy to follow as you can.
Don't worry about the stuff at the end, just use the FREQUNCY on some typed in upper limits. It is great too : )
Hi,
The Excel Wizard solutions still need the INDEX function to cut off the last bin in the case where the highest sale equals the max of the last sales bin.
You are the master
Master of fun ; )
Awesome! Though, I admit, I have to sit down when editing those formulas! LOL
Sit down then. Whatever it takes to get it done lol : ) : )
Mantap
LET is RAD!!!
I agree!!! Rad!!!!!!!!
that's a nice trick with the IF({1,0},Categories,Freq) - don't know if i fully understand it yet.. will have to play with it some more! Can the "Report" variable be extended to return more than 2 columns? aka return something similar to whats in the range starting at F7? Since the LET function in K9 already has Categories and UpperLimts, i'm thinking it is?
thanks and amazing video as always!
Anytime you have an array in a function argument it makes function give an answer for all elements. Then since 1 = TRUE and 0 = FALSE, the array {1,0} just asks IF to give both answers at one time. Similarly, If you have an array of criteria in SUMIFS, SUMIFS would give multiple answers, like: =SUMIFS(NumberRAnge,CriteriaRange,{"Quad","Bellen","Aspen"}) would give three answers , one for each product name, Quad, Bellen and Aspen.
Yes, LET using IFS or CHOOSE can return multiple columns. CHOOSE({1,2,3},Columns1,column2,column3) returns a 3 column report : )
@@excelisfun Thank you, worked like a charm! :)
@@StevenWan11 Yes!!!! Charms are good : )
"Premium video" if I have to say in Bond valuation term .Let is Amazing.
Thanks for the premium : )
Hey Mike! Is there a way to type only uppercase letters? Suppose if i type a word in small letters it automatically change to uppercase
I am not sure. Try posting to this great Excel question site: mrexcel.com/forum
@@excelisfun 👍👍
Excellent. Although you entered LET variables in separate lines which made it easier to understand, is it me or is it just harder to use the LET function if you did not do it step by step first (like the first part of your video) then use LET to help you grasp what you did?
No, it is me too. LET is hard to create becasue you have to see all variables in formula before you start creating it. I always have to create all steps in cells and then latter mash them all together in LET...
I really like your videos. Thank you so much. Kindly suggest on how to achieve the below scenario in excel
How To Increment Numbers only when value changes In another column?
In the below example, the values in a cell are as follows
Apple
Apple
Orange
Orange
Orange
Cucumber
Peach
Peach
Peach
Peach
In the above set, I need to generate number as follows
1 Apple
1 Apple
2 Orange
2 Orange
2 Orange
3 Cucumber
4 Peach
4 Peach
4 Peach
4 Peach
If top apple starts in cell E12, then: =ROWS(UNIQUE($E$12:E12))
Then copy down.
Or in cell F12:
=(E11E12)+F11
@@excelisfun You are brilliant!!! Much impressed and I have subscribed as well.. Thanks very much. For me, =ROWS(UNIQUE($E$12:E12)) formula results in name error. However the second formula you provided, =(E11E12)+F11 works like a charm. Now I could generate sequence numbers as per my need. I am using this for my study tracker and it is really helpful. Thanks a ton.
@@Lessonade You are welcome a ton! But it is only because Excel is fun ; )
1st
I give you the first place trophy!!!
@@excelisfun your videos are trophies whatever the place.
@@unionafrican6094 Thank you for that clever phrase : ) : )
The pope from Excel
Thanks, Keft : )
tech-Jesus descended to the mortals
That is too funny lol