@@excelisfun I learn it not because I have to work in any office Because I have to feel the confidence after getting something new from here... Loved it from India .... ... May I ask you something?
Sooooo.... At about the 1:32 mark I paused the video to work the formula. Before I hit play again i tested it by using 19,999.999999 to see if it would take it. Of course it didn't because of formatting. When I hit play you showed how it shows 20000 but its really 19999.99 and because of formatting it wont bump to the bonus. So yeah, You've taught me well! :) In addition i studied the notes and am doing the HW probs. Thank you for all of this!
This is so great, Mr. Girvin! I just saw your tip for building conditional formatting by row at 45:00 and had to stop to say thanks. I've always struggled with those and now I can't wait to try it at work tomorrow!
Your extensive use of annotations via Camtasia still amazes me. I can't imagine how long it takes to add them as you include tons of them in every video.
@@PlaybookGamer I work a lot. For example, two days ago, Saturday, I was awake for 17 hours and worked 14 hour straight through to finish the videos : )
@@PlaybookGamer Yes!!!! I am happy to inspire and be on the Team with you!!!!!! UA-cam Fact: Most viewers like the short stuff that doesn't explain why and just gives the answer. I go against the grain even though it costs views. I think of the videos I make about Excel more as art that is for those who REALLY want to know and who want to become TRUE masters : )
You mentioned the reason for using formulas instead of a PivotTable is to ensure that calculations automatically update. This is especially true when providing reports to upper management, who either don't want, or don't realize they need, to refresh a PivotTable. Another thing to consider is: Do you want to be able to select a particular calculation and see the details behind it? If that's the case, you should use a PivotTable. (I say this as someone who would MUCH rather use spillable formulas.) I have used Excel for years (20+), and have been using a lot of the new functions since they were released in beta, but I still learned some things! Hilariously, I didn't realize that anything greater than 0 worked in IF statements. Everything I create has ISNUM in it. LOL I just created an interesting formula that allows you to enter all of your OR conditions in one cell, separated by commas. It uses the above knowledge, TEXTSPLIT, and SUM. (I say created. Someone else has probably done it before, but I can claim it if I haven't seen it before, right?) I think you could probably even use LET to avoid cluttering the spreadsheet, but haven't tried putting that together yet.
Just trying to do my part in making the world a better place by telling the best possible stories about Excel and Power BI : ) Many you are welcomes!!!!
Wow, Chris M, That is quite a compliment!!! I do try to tell a better story each time I make a video, but you are such an Excel master!!! Were there new things that you learned, or was it just the new story that was engaging?
HI Mike.... Great video . Quick question, on Minute 52:34 instead of using SUMIFS , I tried to use SUMPRODUCT to add however it returned an error when using the # operator to reference the spill array . SUMPRODUCT is having a hard time when working with Dynamic arrays. Have you encounter this behavior in the past?. Thanks
Thanks for your reply. SUMPRODUCT works if you reference the cell like this : On cell H9 of the workbook =SUMPRODUCT(--(ProduceSales[Cust]=$G9),--(ProduceSales[Prod]=H$8),ProduceSales[Sales]) BUT it does not if you reference the spill operator like so =SUMPRODUCT(--(ProduceSales[Cust]=G9#),--(ProduceSales[Prod]=H8#),ProduceSales[Sales]) . This has been a mystery to me.
@@jazzista1967 No formula can equate arrays that are different sizes. ProduceSalesAN[Prod]=G9# is a 20 element array compared against a 2 element array. You can't do that directly. It is not SUMPRODUCT that is causing the issue.
@@jazzista1967 Maybe something like this would work for different sized arrays: MMULT(--(ProduceSalesAN[Cust]=TRANSPOSE(G9#)),{1;1}) or LET(r,--(ProduceSalesAN[Cust]=TRANSPOSE(G9#)),CHOOSECOLS(r,1)+CHOOSECOLS(r,2)) But why are you using SUMPRODUCT when you have SUMIFS? From an efficacy standpoint, you should almost never use SUMPRODUCT when a SUMIFS solution is available.
Mike. Thanks for the explanation and recommendation in using SUMIFS. I completely forgot about the number of arrays in the SUMPRODUCT calculation. I will play around with the formulas above
Did you watch the whole thing yet, Formula Guy John? This is one of the most "packed" with second by second content that I have ever made. I though you, as a formula guy, would enjoy this video : )
@@excelisfun That is a long time for one video!!!! However it is expected with all the details and sketches in every video.!!! So with all the videos in your Channel.....how many hrs do you forecast??? :) :)
What?? Excel has FORMULAS!!?! That's gonna save me a LOT of time! Sorry Remy! Sorry Rene! It's been a fantastic ride but it's 1989 and things are achangin'! It's been 20 great years but my rusty, trusty, dusty LANPAR just isn't cutting it any more. It's come time for me to get one one of those fancy desktop PCs. Besides, that Bill Gates guy said if I start using Excel he will give me a new set of WINDOWS!! You can put away the parka Edith 'cuz it's about to get LOTS warmer in here!! Sure do need those new windows! Great video! I made it through in one sitting (or "setting"? depending on your state of origin!) except for a brief potty/popcorn break!!
Richard "Poet and Word Master" Hay, WOW!!!! You made it through in one sitting? 1:48 hours? I am not sure I could have even done that lol... But I am glad that you had a popcorn break : ) Was there an tips that were new? For an Excel Master like you, I bet not much.
@@excelisfun New? When I watch an Excel video if I don't find New I find Nuance. This video has an overriding principle: Filter, Filter, Filter!!! My brother-in-law is a Texas preacher who makes good "side hustle" money as a wood carver. He also dabbles in Excel. We often talk about Excel and I try to tie Excel to his wood carving. Start with a blob of wood and use creativity, insight, and imagination in conjuction with a set of tools and design criteria to create interesting shapes that increase the value of the wood. Carving is reductive. Value is added by selectively eliminating wood. Likewise Excel is data carving by taking a blob of data and increasing its value by selectvely and creatively eliminating data to expose hidden patterns that fit specified design criteria. The carver cuts and the Excel analyst filters. Filter is our primary cutting tool just as a knife is the carvers primary filtering tool. My brother-in law readily grasped the analogy and said it greatly helped him unify Excel. So I am laser focused on filtering. Regardless of the terninaolgy, I view Excel formulas as a set of filtering tools and virtually all data analyis as a reductive, filtering process. When the topic is filtering I am all ears!!
@@richardhay645 You are such a masterful and inspiring word articulator, Richard!!! I just love to read what you write. It is like a new and wonderful adventure as I read each word that you write. Thanks for the delicious word smithing : ) Also, your Texas preacher who makes good "side hustle" money as a wood carver., sounds like rad dude! I hope you can get him to also carve up Excel ; ) As for this video, since it is all about logical tests, and FILTERing is your things: BAM! This, by default, should be a good video for you : )
@@excelisfun Indeed it is "my thing" Over time he has seemlessly evolved from a "Help! I have to get this done" Excel user to an increasingly proficient data carver!
38:12 Not exacly, I mean you can also create another filter function, or add other formulas. adding text message is just 1 option out of infinity of possibillities
Great one but i am unable to run a if condition with multiple or logics in power query ie if column A has "Y" Then column B should have "1" Or "2" Or "3" Then "okay" Else" Not okay"
Thanks forthe new video Mike. 1 thing re average grades VIA power query - you made na average at table bottom, while it is sth you yourself said is not correct (its an average of averages and not a total average - I think its not possibile to have a proper total in this case actually :(
@@excelisfun It was my bad not reviewing the video again, sorry. :( In minute 28 you make a power query table to calculate averages and then load to an output table. Then you add an average at the bootom of that output table. I missed how that average is not a 'usual' average of the same table (which would be incorrect), but a completely separete function - average of the source table (I did not know it was possible to use the total row for a separate calculation like that :O ) The only isue is then with consistency between the data in the output table and its 'total/average'' - they are inconsistent until the query is refreshed (it should be refreshed, so it's an error based on an error, a proper refresh should do it good :) On reviewing this I encountered another problem with the total row in the table though - if it exists in the source table unexpectedly power query gets it as part of the data to be transformed, an I could not find a folproof way to get rid of it (filter by name does not always work as the total row can be labelled differently byd different users, cut last row would not work as the total row may exist or may not...) - would you have a solution based on your broad experience?
Greetings from France professor Mike @ExcellsFun ! As one of your abroad students, i had a question based on the MSPTDA classes. i'd like to know if theres a way/trick to merge multiple files into one table from columns (append or merge multiple columns and not rows ?). PS is there a way to donate to your magical way of teaching ? Many thanksssssss, especially if i could get a reply 🤞
Hi, i have a question for this video. In the example "Goal 1" on worksheet SC, the Result for the grand total of Average of GPA on my pivot table comes out different than the one in your video. Mine is 2.9....but my worksheet formulas come out right which is 2.79....I have double checked all the GPA numbers on the excel table to make sure they are correct and they are. I notice on my pivot table, the average of GPA for Business is 2.4 and History is 3.5...I dont know how it gets those numbers. And I follow the exact same steps as in the videos 😵
![Lp. Сердце Вселенной #60 РОЖДЕНИЕ ЛОЛОЛОШКИ [Финал] • Майнкрафт](http://i.ytimg.com/vi/YoR0pAV9FVQ/mqdefault.jpg)
I can't believe you made an hour and fifty minute free Excel class for 10,000+ people to learn. You are amazing Mike. Thank you!
Thank is what I do : ) For 14 years at UA-cam I have loved what I do : )
@@excelisfun I learn it not because I have to work in any office
Because I have to feel the confidence after getting something new from here...
Loved it from India ....
... May I ask you something?
Thanks!
Thank you so much, Eric: for the support to help keep making videos!!!! Thanks for helping the Team : )
May God bless you Mike. You have done all these amazing videos for free.
Free is good : ) Glad you like them!!!
Sooooo....
At about the 1:32 mark I paused the video to work the formula. Before I hit play again i tested it by using 19,999.999999 to see if it would take it. Of course it didn't because of formatting. When I hit play you showed how it shows 20000 but its really 19999.99 and because of formatting it wont bump to the bonus.
So yeah,
You've taught me well! :)
In addition i studied the notes and am doing the HW probs.
Thank you for all of this!
Yes! Yes! Yes!!!!! I wish even 10% of the students at my college were are as attentive as you. The attentive rule the world ; )
This is so great, Mr. Girvin!
I just saw your tip for building conditional formatting by row at 45:00 and had to stop to say thanks.
I've always struggled with those and now I can't wait to try it at work tomorrow!
Mixed Cell references still have a use in M 365 : ) : ) : ) : )
Your extensive use of annotations via Camtasia still amazes me. I can't imagine how long it takes to add them as you include tons of them in every video.
This 108 minute video took 106 hours to make. It is typical: on average, for each 1 minute of video it takes me one hour to make.
@@excelisfun Incredible! How do you have the time to do it all and teach as well? I feel like I just don't work hard enough lol.
@@PlaybookGamer I work a lot. For example, two days ago, Saturday, I was awake for 17 hours and worked 14 hour straight through to finish the videos : )
@@excelisfun Thanks for always motivating me to do better and wanting to make more content, especially the long form stuff.
@@PlaybookGamer Yes!!!! I am happy to inspire and be on the Team with you!!!!!! UA-cam Fact: Most viewers like the short stuff that doesn't explain why and just gives the answer. I go against the grain even though it costs views. I think of the videos I make about Excel more as art that is for those who REALLY want to know and who want to become TRUE masters : )
MECS is the one and only reference video collection.
It is sort of a "complete" listing for each topic : )
You mentioned the reason for using formulas instead of a PivotTable is to ensure that calculations automatically update. This is especially true when providing reports to upper management, who either don't want, or don't realize they need, to refresh a PivotTable. Another thing to consider is: Do you want to be able to select a particular calculation and see the details behind it? If that's the case, you should use a PivotTable. (I say this as someone who would MUCH rather use spillable formulas.) I have used Excel for years (20+), and have been using a lot of the new functions since they were released in beta, but I still learned some things! Hilariously, I didn't realize that anything greater than 0 worked in IF statements. Everything I create has ISNUM in it. LOL
I just created an interesting formula that allows you to enter all of your OR conditions in one cell, separated by commas. It uses the above knowledge, TEXTSPLIT, and SUM. (I say created. Someone else has probably done it before, but I can claim it if I haven't seen it before, right?) I think you could probably even use LET to avoid cluttering the spreadsheet, but haven't tried putting that together yet.
Very good point!!!!
I'm proficient in the topic but managed to learn something new even from here, thanks as usual, you're a star. :))
Always glad to help when I can, Alberto!!!!
Best place to learn Excel.. Thank u so much sir.
You are welcome so much, Vinay!!!!
You are Legend! Many thanks!
Just trying to do my part in making the world a better place by telling the best possible stories about Excel and Power BI : ) Many you are welcomes!!!!
This never gets old. Thanks for all you do Mike!
Wow, Chris M, That is quite a compliment!!! I do try to tell a better story each time I make a video, but you are such an Excel master!!! Were there new things that you learned, or was it just the new story that was engaging?
Hi Mike, I knew pretty much all of this, just liked the way you laid it out in the video. Very logical and easy to follow!
@@chrism9037 Yah, I guess it was my duty, in a logical test video, to lay the vid out logically ; )
Boom!Whoa This Was One Heck Of A Super Fun Class...Thank You Mike :)
Glad it is fun, Bike Brother!!!!
Oho wow Mr mike wlcm i m so happy to see your new update you are the "Rock"
I am glad to rock it out with you, Vijay!!!!!
Love your videos!
Yes!!!!!
Informative, thanks
Glad you like it!!!!
Always the best content. Thank you!!!
You are welcome, J L!!!!!
Excellent video! This is exactly what I'm looking for 😉 can't thank you enough!
You are welcome R x!!!!!
Thanks Mike for this EXCELlent video.
You are welcome, Fellow Teacher : ) : ) : )
As always awesome job mike.
Glad the content is awesome for you, Arshad!!!
Thank you Sir
You are welcome for the logical test fun!!!
You are really great teacher can u make budget
HI Mike.... Great video . Quick question, on Minute 52:34 instead of using SUMIFS , I tried to use SUMPRODUCT to add however it returned an error when using the # operator to reference the spill array . SUMPRODUCT is having a hard time when working with Dynamic arrays. Have you encounter this behavior in the past?. Thanks
I have not encountered that. I have used SUMPRODUCT with dynamic arrays and they worked, like:
=SUMPRODUCT(H12:H19,J12#)
Thanks for your reply. SUMPRODUCT works if you reference the cell like this : On cell H9 of the workbook =SUMPRODUCT(--(ProduceSales[Cust]=$G9),--(ProduceSales[Prod]=H$8),ProduceSales[Sales])
BUT it does not if you reference the spill operator like so =SUMPRODUCT(--(ProduceSales[Cust]=G9#),--(ProduceSales[Prod]=H8#),ProduceSales[Sales]) . This has been a mystery to me.
@@jazzista1967 No formula can equate arrays that are different sizes. ProduceSalesAN[Prod]=G9# is a 20 element array compared against a 2 element array. You can't do that directly. It is not SUMPRODUCT that is causing the issue.
@@jazzista1967 Maybe something like this would work for different sized arrays:
MMULT(--(ProduceSalesAN[Cust]=TRANSPOSE(G9#)),{1;1})
or
LET(r,--(ProduceSalesAN[Cust]=TRANSPOSE(G9#)),CHOOSECOLS(r,1)+CHOOSECOLS(r,2))
But why are you using SUMPRODUCT when you have SUMIFS? From an efficacy standpoint, you should almost never use SUMPRODUCT when a SUMIFS solution is available.
Mike. Thanks for the explanation and recommendation in using SUMIFS. I completely forgot about the number of arrays in the SUMPRODUCT calculation. I will play around with the formulas above
Thanks Mike ;) :) Amazing Video!!!
Did you watch the whole thing yet, Formula Guy John? This is one of the most "packed" with second by second content that I have ever made. I though you, as a formula guy, would enjoy this video : )
P.S. This video only took me 106 hours to make... which is just about my average for making videos: 1 hour per minute of video...
What parts did you like most, John Borg?
@@excelisfun That is a long time for one video!!!! However it is expected with all the details and sketches in every video.!!! So with all the videos in your Channel.....how many hrs do you forecast??? :) :)
@@johnborg5419 It is typical for every vid I make: on average, for each 1 minute of video it takes me one hour to make.
Awesome video
Glad you like it, Mario!!!!
What?? Excel has FORMULAS!!?! That's gonna save me a LOT of time! Sorry Remy! Sorry Rene! It's been a fantastic ride but it's 1989 and things are achangin'! It's been 20 great years but my rusty, trusty, dusty LANPAR just isn't cutting it any more. It's come time for me to get one one of those fancy desktop PCs. Besides, that Bill Gates guy said if I start using Excel he will give me a new set of WINDOWS!! You can put away the parka Edith 'cuz it's about to get LOTS warmer in here!! Sure do need those new windows!
Great video! I made it through in one sitting (or "setting"? depending on your state of origin!) except for a brief potty/popcorn break!!
Richard "Poet and Word Master" Hay, WOW!!!! You made it through in one sitting? 1:48 hours? I am not sure I could have even done that lol... But I am glad that you had a popcorn break : ) Was there an tips that were new? For an Excel Master like you, I bet not much.
@@excelisfun New? When I watch an Excel video if I don't find New I find Nuance. This video has an overriding principle: Filter, Filter, Filter!!! My brother-in-law is a Texas preacher who makes good "side hustle" money as a wood carver. He also dabbles in Excel. We often talk about Excel and I try to tie Excel to his wood carving. Start with a blob of wood and use creativity, insight, and imagination in conjuction with a set of tools and design criteria to create interesting shapes that increase the value of the wood. Carving is reductive. Value is added by selectively eliminating wood. Likewise Excel is data carving by taking a blob of data and increasing its value by selectvely and creatively eliminating data to expose hidden patterns that fit specified design criteria. The carver cuts and the Excel analyst filters. Filter is our primary cutting tool just as a knife is the carvers primary filtering tool. My brother-in law readily grasped the analogy and said it greatly helped him unify Excel. So I am laser focused on filtering. Regardless of the terninaolgy, I view Excel formulas as a set of filtering tools and virtually all data analyis as a reductive, filtering process. When the topic is filtering I am all ears!!
@@richardhay645 You are such a masterful and inspiring word articulator, Richard!!! I just love to read what you write. It is like a new and wonderful adventure as I read each word that you write. Thanks for the delicious word smithing : ) Also, your Texas preacher who makes good "side hustle" money as a wood carver., sounds like rad dude! I hope you can get him to also carve up Excel ; ) As for this video, since it is all about logical tests, and FILTERing is your things: BAM! This, by default, should be a good video for you : )
@@excelisfun Indeed it is "my thing" Over time he has seemlessly evolved from a "Help! I have to get this done" Excel user to an increasingly proficient data carver!
@@richardhay645 Data "FILTER" carvers GO!!!!
38:12 Not exacly, I mean you can also create another filter function, or add other formulas. adding text message is just 1 option out of infinity of possibillities
Thanks, my rad friend : )
Waiting for the moment when will you 1M subscribes, can educational UA-cam channel do this? Yes, because your work is marvelous
Thank you very much : ) : ) : ) : ) I am glad you like my work : )
Mike loves you bro...
Thanks for the love, Budy!!!! Did you get many logical test tips that are useful to you in this vid?
@@excelisfun your logical concept is very useful for us..
@@TheAlwaysGoel Rad!!! (cool!!!)
with the IFS() function, I like to just use 1 as the final logical_test. Faster than typing "TRUE" 😁
I LOVE that!!!! The new Mayday Method = 1 not TRUE ; )
Superb
Glad you like it!!! Love IS the key to all : )
Great one but i am unable to run a if condition with multiple or logics in power query ie if column A has "Y" Then column B should have "1" Or "2" Or "3" Then "okay" Else" Not okay"
Thanks forthe new video Mike.
1 thing re average grades VIA power query - you made na average at table bottom, while it is sth you yourself said is not correct (its an average of averages and not a total average - I think its not possibile to have a proper total in this case actually :(
I am sorry, Sebastian: I do not understand what you are communicating to me. Can you re-state?
@@excelisfun It was my bad not reviewing the video again, sorry. :(
In minute 28 you make a power query table to calculate averages and then load to an output table. Then you add an average at the bootom of that output table.
I missed how that average is not a 'usual' average of the same table (which would be incorrect), but a completely separete function - average of the source table (I did not know it was possible to use the total row for a separate calculation like that :O )
The only isue is then with consistency between the data in the output table and its 'total/average'' - they are inconsistent until the query is refreshed (it should be refreshed, so it's an error based on an error, a proper refresh should do it good :)
On reviewing this I encountered another problem with the total row in the table though - if it exists in the source table unexpectedly power query gets it as part of the data to be transformed, an I could not find a folproof way to get rid of it (filter by name does not always work as the total row can be labelled differently byd different users, cut last row would not work as the total row may exist or may not...) - would you have a solution based on your broad experience?
@@dziadeck47 Funny things is, I did not know you could do a calc in a Table Total Row on a different area either, till I tried it in this vid ; )
Hi, is there a reason why you prefer IF over IFS? Nesting IF functions is just awful.
I find putting the TRUE for the last argument in IFS awkward. However, I have been already accustom into using nested IF for decades lol
♥ ♥ ♥
Glad you love it : ) : ) : )
Greetings from France professor Mike @ExcellsFun ! As one of your abroad students, i had a question based on the MSPTDA classes. i'd like to know if theres a way/trick to merge multiple files into one table from columns (append or merge multiple columns and not rows ?).
PS is there a way to donate to your magical way of teaching ?
Many thanksssssss, especially if i could get a reply 🤞
Hi, i have a question for this video. In the example "Goal 1" on worksheet SC, the Result for the grand total of Average of GPA on my pivot table comes out different than the one in your video. Mine is 2.9....but my worksheet formulas come out right which is 2.79....I have double checked all the GPA numbers on the excel table to make sure they are correct and they are. I notice on my pivot table, the average of GPA for Business is 2.4 and History is 3.5...I dont know how it gets those numbers. And I follow the exact same steps as in the videos 😵
First comment
Yes!!!! You get the first place trophy, Sawai!!!!
Thanks!
Thank you for your donation, N Sanch01!!! That helps me keep making videos for our Team : ) : ) : )
Thanks!
Thank you very much for the donation, MaanEid!!!! The donation helps very much : ) : )