Excel Magic Trick 1341: Standard Deviation with Criteria: Power Query, PivotTable, or Array Formula?
HTML-код
- Опубликовано: 21 авг 2024
- Download Files:
Start File: excelisfun.net...
Finish File: excelisfun.net...
See how to use Power Query, a PivotTable and an Excel Formulas to calculate to calculating Average and Standard Deviation with Criteria:
1. (00:14) Introduction & Setup
2. (01:28) Array Formula for calculating Average and Standard Deviation with Criteria. See AVERAGEIFS function and the STEDV.S & Two IF functions in an Array Formula.
3. (08:46) PivotTable for calculating Average and Standard Deviation with Criteria.
4. (14:01) Power Query Example 1: Group By All Rows & Extra Column with List.StandardDeviation Power Query Function. Use Group By to calculate Average and Standard Deviation with Criteria.
5. (21:06) Power Query Example 2: Use Group By Power Query Feature and then change M Code from List.Average Function to List.StandardDeviation function.
6. (26:04) Summary
Power Query Group By to Calculate Standard Deviation. How to use Power Query Group By to Calculate Average and Standard Deviation. How Do I Calculate Standard Deviation in Power Query. Where is Standard Deviation in Group By Power Query?
Good lord, this is such world class instruction! As usual, I'm gobsmacked by the clarity, organization, length, and supplemental materials.
Calculating standard deviation with conditions part just saved my life. wow. excellent Job. please never stop
Okay, I'll never stop : ) ... It has been over 12 years so far : )
Hi Mike, as always... very good job. I really like the idea to compare different methods like Pivot, PQ, Array in one tutorial. Keep up the excellent work - Thumbs Up !!
You are welcome! Thanks for the Thumbs Up!
(20:40) "I'm gonna live dangerously!" LOL!!!! :) :) :) This is why I love your classes, you are not one of those boring tutors. Great video btw.
Yes, we love living dangerously : )
This video saved my 20 minutes and rework..worth while video..thanks
You are welcome, Shankar!!!
Another great lesson!! Thanks :-)
A few words about PQ solution (the last one)
Of course, you can change the code in the advanced editor window but, exactly like in excel, you can change it in the formula bar also.
I think, this is more convenient because you see only the step you want to change (of course only in case when you don't want to add some complex changes or write the function code).
Thanks for the great tip of using the Formula Bar, O, Power Query Master! : )
You could select a column in the table by clicking in the header instead of selecting it all the way down.
This is a much faster way and it is one of the reasons why I like tables rather then lists.
Thanks for the hot tip!
Couldn't figure out what you meant by this. Kept getting [#Headers]. Has to be the top of the header when it changes to downward arrow. How did I not know this? Great tip. Going to save a lot of time. Thanks!
Once again, as usual, you choose important topics. Thanks a lot. :)
You are welcome a lot!
Hey Mike, another great shortcut for number formatting I usually use, SHIFT+CTRL+1 = number with 2 decimals places and thousands separator
Thanks for the tip!
The pivot table option seems to be the most optimum. Thanks for the share
You are welcome! Yes, PivotTable is THE best!
Yes, absolutely agree, in this case PT is the best. But what if you have ID instead of product name and for example "A100" is not the same as "a100"? In this case ( and in many different cases) PQ can show its POWER :-)
Love that example!!!!! Power!
Brilliant video !! Simply loved this one !! Thank you Mike !
Mike you never cease to amaze me. :-) Great video.. I loved the part where you modified the M code.
Thank you so much kind stranger.
You are welcome kind commenter!!!!
I love your channel, man. Keep it up. Greets from Brazil
Glad you like it!
There is a 4th method as long as your data is sorted by the columns in your condition criteria. You can use the index function inside the STDEV function to dynamically find the range to apply the STDEV too. It is a complex formula, but it performs faster than the array formula on large datasets.
Thanks for the hot tip with INDEX!
Awesome Lesson
Glad you like it!
Great video, thanks Mike!
You are welcome!
excellent presentation
Wonderful!
Glad it helps!!!!
your videos are just great to learn!!
Glad you like them!
Hello, Have you uploaded the excel basics videos for amateur learners.
please provide me some link
Yes, here is a playlist of Excel Basics:
ruclips.net/p/PL3FBEE51974F03CCF
Great!!!
Great video , thanks a lot
Yeah Girvin! I request, you to add one more section in your teachings i.e. Google Spreadsheet. I don't like Google Sheets but I want to if you're teaching.
I just don't see any serious business analysis being done in Mac Excel or Google Spreadsheets and so I have not learn either and therefore do not teach either. Sorry about that.
It's okay dear brother. It just came in mind.
Great, Mike, great
Hi Mike,
If I could, I would raise 2 thumbs up!
I truly enjoyed that video.
Is there a way to continue with Power Query and calculate the Normalized Score (I need to get the distance from the average in Std units).
Thanks,
Tamir
Hi Great Master Mike, I made another way - =STDEV(AGGREGATE(15;6;MATCH(A10:A35;E10;0)*MATCH(B10:B35;F10;0)*(C10:C35);INDEX(ROW(INDIRECT("1:"&COUNTIFS($A$10:$A$35;E10;B10:B35;F10)));))) without CSE, in your Example, Best Regards
Excelent!!
EXCELlent!!!! : )
This is using STDEV.S which is suboptimal if you have data for every item. How to use STDEV.P in Power Query?
I wanted to double check the array functions and I got different numbers. When I manually deleted everything so only the numbers I wanted could be used for a simple STDEV(), the number was very close but different...
Thank you for the explenation.
One question: Is there a way to implement the formulas such that it refers to a filtered list?
I am trying to implement the standard diviation of a table based on a criterion. But I also want to be able to filter the data table and calculate the new standard diviation based on the criterion but on the filtered data. Is that possible?
Yes it is possible. Here is video: ruclips.net/video/3mCPWvFJTRc/видео.html There are three amazing solutions in this video : )
WHO ARE YOU???!!!
What is your name?! I have learned so much from you!
Glad you like the video!
Not just this video. I became addicted to all your videos! I'm currently going through BUSN 218 and it's changing my life at work. I'm recommending it to all my friends and I'm so grateful for you for putting all that effort this material.
Awesome!!! I am glad it is changing your Excel life! Thanks for Thumbs Up on each video, Sub, and telling others about how to have fun with Excel!!!!
One quick question:
Would you explain when I should consider using the keyboard short cut : Ctrl+Shit+Enter. Don't understand how to use it. Thanks!!!
I have a whole book on this subject, and a whole video series on this topic. The very basic rule is: when you have an array operation (operation (like multiply, equals, divide, ampersand operations) not on two single items, but an operation on an array of items) and it is sitting in a function argument that doesn't innately allow array operations, then in order to get Excel to calculate the array operation, you must enter the formula with Ctrl + Shift + Enter, not just Enter. There is more to it, but here is one video that may help:
Highline Excel 2016 Class 05: Excel Array Formulas: Comprehensive Lessons: 12 Examples
ruclips.net/video/RDP1uF7HafU/видео.html
a whole book on this topic?!?!(how deep is the Excel knowledge), you are amazing. Thank you! May I ask you how do you learn all of this?What method?by your own? Do you have your master to teach you?
Yes, here is the book:
www.amazon.com/Shift-Enter-Mastering-Excel-Formulas/dp/1615470077
How did I learn? It is the same way that every human has learned since the beginning of time: practice , practice, practice, practice, and a lot of books and asking a lot of questions at mrexcel.com/forum. Most of what I have learned is here at RUclips for you to learn also.
Your sentence becomes one of my life quote. I like to learn. But sometimes I feel myself as a not smart person because concepts I learn I will forget pretty soon. Also, my comprehension skills are not as good as other people. Do you think we, as human, can improve our thought process and comprehensive skills?