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
@ricos14973 жыл бұрын
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!
@excelisfun3 жыл бұрын
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 : ) : ) : ) : )
@DougHExcel3 жыл бұрын
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!
@excelisfun3 жыл бұрын
You are welcome, Teammate : ) : ) : )
@chrism90373 жыл бұрын
Wow that was amazing Mike! Great video!!
@excelisfun3 жыл бұрын
Glad you like it, Chris : ) : ) : )
@Softwaretrain3 жыл бұрын
You made me crazy. Lots of variables in Let function. Excel is really fun with Mike. Thanks.
@excelisfun3 жыл бұрын
You are welcome!!!!!!
@patrickschardt77243 жыл бұрын
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
@excelisfun3 жыл бұрын
Ya, =IF({1,0},"You are welcome ","Patrick!!!")
@patrickschardt77243 жыл бұрын
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)
@excelisfun3 жыл бұрын
@@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 : )
@patrickschardt77243 жыл бұрын
ExcelIsFun good to know. I’ll have explore this
@excelisfun3 жыл бұрын
@@patrickschardt7724 : )
@ExcelDude3 жыл бұрын
I love array functions, great summary and it helped me with a few of my issues in Excel!
@excelisfun3 жыл бұрын
Great! Glad to help.
@johnborg60053 жыл бұрын
Thanks mike. The let formula is a sculpture. Amazing!!!
@excelisfun3 жыл бұрын
It is fun - since I use frequency distributions all the time, the new spilled arrays make it so much easier!!!
@mattschoular88443 жыл бұрын
"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
@excelisfun3 жыл бұрын
Yes, it seems eassier to do the steps in the cell, rather than LET, but talk about an all-in-one : ) : ) : )
@barttitulaerexcelbart94003 жыл бұрын
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 ...;)
@excelisfun3 жыл бұрын
Yes, but how do you not display the zero dynamically? You are welcome for the follow up, Bart : )
@wayneedmondson10653 жыл бұрын
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!!
@excelisfun3 жыл бұрын
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!!!
@wayneedmondson10653 жыл бұрын
@@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.
@excelisfun3 жыл бұрын
FILTER is more complicated than the INDEX solution . Something like : =FILTER(FREQUENCY(fSalesAnswer[Sales],F8#),SEQUENCE(D4+1)
@wayneedmondson10653 жыл бұрын
@@excelisfun Thanks Mike!
@dontgetcaughtslippn60753 жыл бұрын
This is kinda crazy, A bit lost, but I think you’ve done an outstanding job making it easy to follow as you can.
@excelisfun3 жыл бұрын
Don't worry about the stuff at the end, just use the FREQUNCY on some typed in upper limits. It is great too : )
@darrylmorgan3 жыл бұрын
Boom!Wow Great Lesson With Some Awesome FUNCTIONS/FORMULAS...Thank You Mike :)
@excelisfun3 жыл бұрын
You are welcome, darryl : ) : ) : ) Boom! : )
@Excelambda3 жыл бұрын
Great LET function!!!!!✌
@excelisfun3 жыл бұрын
Thanks, O Master of LET, cr gr0912 : ) : ) : ) : )
@sachinrv13 жыл бұрын
Wonderful video Mike, I liked the use of Index function. Spilled array is really a next level Excel. Cheers 👍
@excelisfun3 жыл бұрын
You are welcome for the wonderful, Sachin : ) : )
@HusseinKorish3 жыл бұрын
That's amazing ... thanks Mike
@excelisfun3 жыл бұрын
You are welcome, Husein !!!!
@at-excel3 жыл бұрын
Great! Thanks for sharing.
@excelisfun3 жыл бұрын
My pleasure, Teammate!!!!
@MalinaC3 жыл бұрын
Thank you MIke for this awesome video!
@excelisfun3 жыл бұрын
You are welcome, Teammate!
@djl87103 жыл бұрын
LET is RAD!!!
@excelisfun3 жыл бұрын
I agree!!! Rad!!!!!!!!
@unionafrican60943 жыл бұрын
1st
@excelisfun3 жыл бұрын
I give you the first place trophy!!!
@unionafrican60943 жыл бұрын
@@excelisfun your videos are trophies whatever the place.
@excelisfun3 жыл бұрын
@@unionafrican6094 Thank you for that clever phrase : ) : )
@maitreerimthong3 жыл бұрын
Thank you. Good job
@excelisfun3 жыл бұрын
Glad you like it : ) : )
@DIGITAL_COOKING3 жыл бұрын
Smart Mike, very smart !👍
@excelisfun3 жыл бұрын
Glad it is smart for you : )
@GeertDelmulle3 жыл бұрын
Phenomenal! And the dynamics of it are astounding! Great job! :-)
@JonathanExcels3 жыл бұрын
You are the master
@excelisfun3 жыл бұрын
Master of fun ; )
@mireillecantrell46383 жыл бұрын
Awesome! Though, I admit, I have to sit down when editing those formulas! LOL
@excelisfun3 жыл бұрын
Sit down then. Whatever it takes to get it done lol : ) : )
@t.pigeon23843 жыл бұрын
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.
@Lessonade3 жыл бұрын
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
@excelisfun3 жыл бұрын
If top apple starts in cell E12, then: =ROWS(UNIQUE($E$12:E12)) Then copy down.
@excelisfun3 жыл бұрын
Or in cell F12: =(E11E12)+F11
@Lessonade3 жыл бұрын
@@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.
@excelisfun3 жыл бұрын
@@Lessonade You are welcome a ton! But it is only because Excel is fun ; )
@Sal_A3 жыл бұрын
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?
@excelisfun3 жыл бұрын
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...
@simfinso8583 жыл бұрын
"Premium video" if I have to say in Bond valuation term .Let is Amazing.
@excelisfun3 жыл бұрын
Thanks for the premium : )
@StevenWan113 жыл бұрын
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!
@excelisfun3 жыл бұрын
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.
@excelisfun3 жыл бұрын
Yes, LET using IFS or CHOOSE can return multiple columns. CHOOSE({1,2,3},Columns1,column2,column3) returns a 3 column report : )
@StevenWan113 жыл бұрын
@@excelisfun Thank you, worked like a charm! :)
@excelisfun3 жыл бұрын
@@StevenWan11 Yes!!!! Charms are good : )
@kefttago19903 жыл бұрын
The pope from Excel
@excelisfun3 жыл бұрын
Thanks, Keft : )
@jaichander873 жыл бұрын
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
@excelisfun3 жыл бұрын
I am not sure. Try posting to this great Excel question site: mrexcel.com/forum