FREQUENCY Array Function for Quantitative Data. LET function to create full report! EMT 1693.

No video

FREQUENCY Array Function for Quantitative Data. LET function to create full report! EMT 1693.

Рет қаралды 7,126

excelisfun

Күн бұрын

Пікірлер: 91

@excelisfun 3 жыл бұрын

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

@ricos1497 3 жыл бұрын

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!

@excelisfun 3 жыл бұрын

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 : ) : ) : ) : )

@DougHExcel 3 жыл бұрын

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!

@excelisfun 3 жыл бұрын

You are welcome, Teammate : ) : ) : )

@chrism9037 3 жыл бұрын

Wow that was amazing Mike! Great video!!

@excelisfun 3 жыл бұрын

Glad you like it, Chris : ) : ) : )

@Softwaretrain 3 жыл бұрын

You made me crazy. Lots of variables in Let function. Excel is really fun with Mike. Thanks.

@excelisfun 3 жыл бұрын

You are welcome!!!!!!

@patrickschardt7724 3 жыл бұрын

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

@excelisfun 3 жыл бұрын

Ya, =IF({1,0},"You are welcome ","Patrick!!!")

@patrickschardt7724 3 жыл бұрын

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)

@excelisfun 3 жыл бұрын

@@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 : )

@patrickschardt7724 3 жыл бұрын

ExcelIsFun good to know. I’ll have explore this

@excelisfun 3 жыл бұрын

@@patrickschardt7724 : )

@ExcelDude 3 жыл бұрын

I love array functions, great summary and it helped me with a few of my issues in Excel!

@excelisfun 3 жыл бұрын

Great! Glad to help.

@johnborg6005 3 жыл бұрын

Thanks mike. The let formula is a sculpture. Amazing!!!

@excelisfun 3 жыл бұрын

It is fun - since I use frequency distributions all the time, the new spilled arrays make it so much easier!!!

@mattschoular8844 3 жыл бұрын

"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

@excelisfun 3 жыл бұрын

Yes, it seems eassier to do the steps in the cell, rather than LET, but talk about an all-in-one : ) : ) : )

@barttitulaerexcelbart9400 3 жыл бұрын

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 ...;)

@excelisfun 3 жыл бұрын

Yes, but how do you not display the zero dynamically? You are welcome for the follow up, Bart : )

@wayneedmondson1065 3 жыл бұрын

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!!

@excelisfun 3 жыл бұрын

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!!!

@wayneedmondson1065 3 жыл бұрын

@@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.

@excelisfun 3 жыл бұрын

FILTER is more complicated than the INDEX solution . Something like : =FILTER(FREQUENCY(fSalesAnswer[Sales],F8#),SEQUENCE(D4+1)

@wayneedmondson1065 3 жыл бұрын

@@excelisfun Thanks Mike!

@dontgetcaughtslippn6075 3 жыл бұрын

This is kinda crazy, A bit lost, but I think you’ve done an outstanding job making it easy to follow as you can.

@excelisfun 3 жыл бұрын

Don't worry about the stuff at the end, just use the FREQUNCY on some typed in upper limits. It is great too : )

@darrylmorgan 3 жыл бұрын

Boom!Wow Great Lesson With Some Awesome FUNCTIONS/FORMULAS...Thank You Mike :)

@excelisfun 3 жыл бұрын

You are welcome, darryl : ) : ) : ) Boom! : )

@Excelambda 3 жыл бұрын

Great LET function!!!!!✌

@excelisfun 3 жыл бұрын

Thanks, O Master of LET, cr gr0912 : ) : ) : ) : )

@sachinrv1 3 жыл бұрын

Wonderful video Mike, I liked the use of Index function. Spilled array is really a next level Excel. Cheers 👍

@excelisfun 3 жыл бұрын

You are welcome for the wonderful, Sachin : ) : )

@HusseinKorish 3 жыл бұрын

That's amazing ... thanks Mike

@excelisfun 3 жыл бұрын

You are welcome, Husein !!!!

@at-excel 3 жыл бұрын

Great! Thanks for sharing.

@excelisfun 3 жыл бұрын

My pleasure, Teammate!!!!

@MalinaC 3 жыл бұрын

Thank you MIke for this awesome video!

@excelisfun 3 жыл бұрын

You are welcome, Teammate!

@djl8710 3 жыл бұрын

LET is RAD!!!

@excelisfun 3 жыл бұрын

I agree!!! Rad!!!!!!!!

@unionafrican6094 3 жыл бұрын

1st

@excelisfun 3 жыл бұрын

I give you the first place trophy!!!

@unionafrican6094 3 жыл бұрын

@@excelisfun your videos are trophies whatever the place.

@excelisfun 3 жыл бұрын

@@unionafrican6094 Thank you for that clever phrase : ) : )

@maitreerimthong 3 жыл бұрын

Thank you. Good job

@excelisfun 3 жыл бұрын

Glad you like it : ) : )

@DIGITAL_COOKING 3 жыл бұрын

Smart Mike, very smart !👍

@excelisfun 3 жыл бұрын

Glad it is smart for you : )

@GeertDelmulle 3 жыл бұрын

Phenomenal! And the dynamics of it are astounding! Great job! :-)

@JonathanExcels 3 жыл бұрын

You are the master

@excelisfun 3 жыл бұрын

Master of fun ; )

@mireillecantrell4638 3 жыл бұрын

Awesome! Though, I admit, I have to sit down when editing those formulas! LOL

@excelisfun 3 жыл бұрын

Sit down then. Whatever it takes to get it done lol : ) : )

@t.pigeon2384 3 жыл бұрын

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.

@Lessonade 3 жыл бұрын

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

@excelisfun 3 жыл бұрын

If top apple starts in cell E12, then: =ROWS(UNIQUE($E$12:E12)) Then copy down.

@excelisfun 3 жыл бұрын

Or in cell F12: =(E11E12)+F11

@Lessonade 3 жыл бұрын

@@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.

@excelisfun 3 жыл бұрын

@@Lessonade You are welcome a ton! But it is only because Excel is fun ; )

@Sal_A 3 жыл бұрын

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?

@excelisfun 3 жыл бұрын

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...

@simfinso858 3 жыл бұрын

"Premium video" if I have to say in Bond valuation term .Let is Amazing.

@excelisfun 3 жыл бұрын

Thanks for the premium : )

@StevenWan11 3 жыл бұрын

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!

@excelisfun 3 жыл бұрын

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.

@excelisfun 3 жыл бұрын

Yes, LET using IFS or CHOOSE can return multiple columns. CHOOSE({1,2,3},Columns1,column2,column3) returns a 3 column report : )

@StevenWan11 3 жыл бұрын

@@excelisfun Thank you, worked like a charm! :)

@excelisfun 3 жыл бұрын

@@StevenWan11 Yes!!!! Charms are good : )