EMT 1535: Average Daily Revenue: SUMIFS with INDIRECT? or SEQUENCE? Inside AVERAGE
HTML-код
- Опубликовано: 18 сен 2024
- Download Excel File: people.highlin...
Entire page with all Excel Files for All Videos: people.highline...
In this video learn how to create an Excel Spreadsheet Formula solution that calculates the Average Daily Revenue with the Average Function with SUMIFS inside. See a battle between the New Office 365 method using SEQUENCE Function or the old Excel way with the INDIRECT function.
Entire page with all Excel Files for All Videos: people.highline...
Full Lesson on these new Dynamic Arrays and new Excel Calculation Engine: Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516) • Comprehensive Excel Dy...
Hi Mike. Really enjoy your videos. A sumifs and divide by days in months seems a less complicated approach for this data. Here is a formula: =SUMIFS(fSales35[Sales],fSales35[Date],">="&I5,fSales35[Date],"
I was thinking the same.
You are right!! and it will calculate much faster too : ) Thanks for the hot tip!
@@sjn7220 You both have a good idea : )
I pinned this to the top so more of our Teammates wills ee it : )
Good tip sir
Hi Mike, Beautiful way to get the average. Really enjoyed it.
Glad you really enjoy it : ) Thanks for watching and thanks for the support : )
Battle between fun and fun! Love it! Thanks Mike!
I LOVE this: "battle between fun and fun"!!!!!!!
Hi Mike, finally I had the time to check it. DAX simply ignores days without sales(I used AVERAGEX), even if you have a separate date table, whereas the fomuala solution counts every day....Have a nice weekend.
Thank you Mike! Love the new Sequence function.
Me too: LOVE the SEQUENCE - so many uses : )
Thank you Mike... I'm always in awe when you do it Mike.
Just having fun with Excel!!!! Be sure to check out Chris McNeil's post about an alternative formula : )
@@excelisfun Hi Mike, does the new Office 365 will have the new functions as well such as textjoin and concat etc aside from the dynamic array new calculation engine?
@@edgiedapogi4848 Yes : )
Thanks Mike. I just simply LOVE IT when there are new videos from you. :) :) I enjoyed that very much!!!!
Thanks for your LOVE, John!!!! That makes both of us; cuz I LOVE making and posting the videos!!!! Thanks for the support : )
Nice! I like what MSFT has done with the dynamic array functions
Great video Mike...I just upgraded to office 365 and I am going to start playing around with the modern array formulas!
Awesome!!! You are going to find that there are some very useful new features.
This is cool Mike ..!! I'm just loving Dynamic Arrays
Yes, they are truly amazing for the formula solutions that we Excel people need to create!!!! Glad it is cool for you, fshaikh Excel Master!!! Thank you for your support with your comment, Thumbs Up and Sub : )
Thumbs up! Thanks for the video!
Hi Mike.. as usual.. awesome video and tricks. Seeing the creative process you use to combine functions and solve problems is invaluable and enhances and expands my own growing experience with Excel far beyond just the solution presented. It makes me a better problem solver vs. just mimicking or copying what I see from others. Many thanks and Thumbs up!
You are welcome and keep up the fun and creativity with Excel !!!!!
Chalk up another victory for SEQUENCE!!! Tied for first as my favorite DAF (tied with FILTER). A number of users still seem to like the old methods (based on their comments to your earlier videos!!). Who supplied the "Rocks" for their Heads? LOL.
Yes!!!!! I agree: FILTER is my favorite by far!!!! But Sequence does have so many different types of uses : ) Thanks for your support and for the lack of rocks in your head!!!!!
despite having a cleaner formula from @Chris McNeil, i thought it shows yet again another way of using SEQUENCE, which seems like a must-learn!
Yes, indeed, SEQUENCE has so many uses : ) Thanks for the support, Excel Bear : )
Thanks for all benchmarking between traditional and dynamic :-)
Brilliant video Mike 📹 thank u for ur hard work 👏
I am always confused whenever I see the formula that combined with ROW and INDIRECT.
You explained it very clearly.
Thank you!!!
Glad it helped with the ROW(INDIRECT())!!!! Thanks for your support : )
EXCELlent video Mike. Thanks for sharing
You are welcome for the share, Syed : ) Thanks for your consistent support : )
Thank u so much Mr. Mike that's really powerful video
You are welcome, Ismail!!! Thanks for you consistent support : )
Hi Mike,its a wonderful one again from you,i am newly starting excel,more keen to in functions,which series of your should i follow to get exactly the all the functions
Awesome dear, lot of thanks.
You are welcome a lot!!!!
This is awesome thanks MIke!!! Another great video from the Master.
Thanks Chris!!! Be sure to check out Chris McNeil's post about an alternative formula : )
New Sequence is Easy for me among 2.Great video
Great!!! Sequence is easy : )
Nice video sir
Glad it is nice for you, Dayanand HIRT!!! Thank you for your support with your comment, Thumbs Up and Sub : )
Hi Mike, Is there a way to get SUMIFS to works with non-contiguous named ranges? The formula =SUMIFS(FPQTY,FPTYPE,"=S/S") works just fine as long as the named ranges are continuous
Hi Mike, thanks for the great video. I noticed that I am getting a different result for some months if I am doing the same calculation with PQ or DAX (e.g. June 18). It would be interesting to understand why.
I do not understand what methods you used. Did you use a Data Table with a unique list with your DAX? How did you do it in Power Query, in succession Group By? Other?
Hi Mike, excactly. PQ = Group by year/month/date and Group by year/month. DAX = Iteration over unique list of dates. Its really strange.... Example June 18: AvgDailyRev with formulas: $12,593.21 / AvgDailyRev with PQ & DAX: $13,027.45
5:28 Sequence Function
Great vid. could this approach of inflating the dates between the two values, be extended to flag when listed dates overlap (in the "dates from and dates to" columns (eg "from 1 jan 2010 to 3 jan 2010" intersects with a duplicate of "from 3 jan 2010 to 4 jan 2010"). Ive used a work-a-round to using a helper column of all dates, but this is way more elegant
I am not sure. Did you try it?
Great
Glad you like it!
which mic do you prefer form creating youtube video, my area is little noisy, i was planning to purchase blue yeti, but in amazon review someone is saying that blue yeti capture hge noice
I have no good news for you. I have made over 4,000 videos since even before RUclips time and I have NEVER found a good mic. I have always had trouble with sound and it is always the most painful part of editing. I use a Blue Yeti cuz it seems the best, but it does NOT solve all the problems that i have...
@@excelisfun thank you sir for your advise
Happy New Year , i want office 365 new array function in office 2019
Microsoft says that there will be no Dynamic Array in 2019, only in Office 365.
Hey bro.. I'm one of your old subscribers..
I'm really struggling with a stupid Average thingy since the last few days and I searched out the entire internet with no luckk.. can you please help me??????
Basically, all I want to do is get an average of the text field counts, as my data has just text values and I want to calculate the cases resolved by an employee on an average per hour..
To each case resolved, they enter their names.. I get the count properly in pivot by just putting the agent name in the value field. However, when I try to find the average, I get a #Div error.. Tried many alternatives with no luck..
Any help would be greatly appreciated
Why not use avaregeifs function?
As I said in the video, you can't because the grain of the column in the table is at the transactional level, and we need to get daily sum totals, first, before we average. We need the grain of the numbers going into the AVERAGE Function to be at the daily level.I hope you liked the video, Geovane!!!!
Video visible clarity very poor.
Video posted has high quality. I hope you can get your device to work clearly soon : )