Excel Magic Trick 1388 Average Number of Days Between Order & Ship Date (Basic Array Formula Lesson)

[@LeilaGharani]

Sumproduct rules! Thank you also for the other fun formulas :)

[@excelisfun]

Don't forget Tejendra's formula for this also: =(SUM(B11:35)-SUM(A11:A35))/COUNT(B11:B35).

[@LongTimeTTFan]

Thanks you very much, as always, Mike. Great video. Clear instruction. Very detailed but no nonsense instruction. Greatly appreciated. I cannot understand how anyone would give a Dislike to this video, or to any of Mike's videos.

[@excelisfun]

You are welcome! Thank you for your kind words about dislikes. I do work hard to provide free videos and Excel files to the world, and although some of the videos may deserve a dislike, unless the person who clicks dislike also works hard to provide free education to the world, it may be cavalier and disrespectful for them to do so.

[@LongTimeTTFan]

ExcelIsFun Totally agreed, Mike. Such dislikes say nothing about your videos, but all about the person(s) who gave dislikes.

[@bricc322]

Thank you for always being so patient and explain with so much detail. I always learn something new from those details. Today I learned that about creating names from selection. Just great! thank you once more for the awesome work that you do. cheers!

[@excelisfun]

You are welcome! Glad the video helps : )

[@BeachMan2007]

SO SO SO HELPFUL!!! Thank you so much, very informative!

[@excelisfun]

You are welcome so much, Yum Yumby!!!!

[@vijaysahal4556]

Nice 🙋

[@excelisfun]

Glad this helps : )

[@BillSzysz1]

Thanks Mike :-) Great video! For consideration: if there are empty cells then only SUMPRODUCT formula can work properly (not always). Other formulas give us wrong answer. In that case, formula below can help =(SUMIFS(B11:B35,B11:B35,"",A11:A35,"") - SUMIFS(A11:A35,B11:B35,"",A11:A35,""))/COUNTIFS(B11:B35,"",A11:A35,"")

[@excelisfun]

Thank you for that empty cell amendment! Very good formula : )

[@jmuqeet]

Hi Bill, How can I use the same formula with an if condition based on a cell

[@abtnineteenninety]

Thank you for the very informative video! I've recently thought about dipping my toe into array formulas, so to speak... I'd be lucky if all my wishes came true this fast, haha!

[@brianstanfill9711]

Great video! Is there a way to continue this formula if new Order and Ship dates were added at the bottom?

[@drm9514]

Awesome video

[@excelisfun]

Thanks. Don't forget Tejendra's formula for this also: =(SUM(B11:35)-SUM(A11:A35))/COUNT(B11:B35)

[@terrianderson1761]

How can I capture the correct average when I need to continue adding dates in the two columns?

[@alandouglas2789]

Thanks a lot Mike, very insightful and helpful as always. I've got a very similar problem at work except there is only one column of dates, and I want to know the average number of days between each of them... can it work without a helper column?

[@excelisfun]

I made this video from the question you posted the other day, but as is often the case, I did not understand that you wanted it from a single column. In this single column, they are sequential dates, always having the next date as a bigger date, right?

[@excelisfun]

I think it is quite easy if the dates are sorted from smallest to biggest. Dates are in the range E9:E14 then a formula like this should work: {=AVERAGE(E10:E14-E9:E13)}

[@alandouglas2789]

ExcelIsFun Thank you so much, I haven't tried it yet but can't wait until I do. I love your videos and love the way you listen to your subscribers... please keep up the great work (I'll let you know how the formula goes after I try it out)

[@excelisfun]

I will make a video in a day or two... However, I really can't listen to most of my KZbin Subscribers because I get 100s of correspondences each and and I am lucky if I can get to one of the 100... Even if all I did for my full-time job was to answer questions, I couldn't' t get to them all : (

[@ihyde22]

What do you do if you only have partial data? So in this example some of the ship dates haven't been populated yet?

[@احمدمحمد-ع4ع8ج]

thanks Mike learned a lot from you . I have another formula =mmult (average (b11: b35-a11:a35);1) but question mmult consider six functios work with array or not.

[@forzaalgiers2045]

Great !!

[@excelisfun]

Glad you like it! Don't forget Tejendra's formula for this also: =(SUM(B11:35)-SUM(A11:A35))/COUNT(B11:B35)

[@crht2902]

Grate, Thanks!!!

[@excelisfun]

You are welcome! Don't forget Tejendra's formula for this also: =(SUM(B11:35)-SUM(A11:A35))/COUNT(B11:B35)

[@ddelgadillo015]

is it possible to isolate within the results of two arrays, such as ignoring negatives? i made a pretty complex sheet and I'm trying to find ways of simplifying it. i change one number and it takes a while to process.

[@excelisfun]

Ignoring negatives would probably add another condition to the formula, perhaps increasing time to calculate. Most of my video focus on teaching efficient methods when it comes to array formulas. THE best document ever produced about making formulas efficient is Excel MVP, Charles Williams, White Paper: msdn.microsoft.com/en-us/library/office/ff700515(v=office.14).aspx

[@ddelgadillo015]

ExcelIsFun would it be possible to send you a sample, and get your input?

[@amnirup8075]

Hello sir I know basics and I want to learn functions and analytics point of view can you please suggest some playlist which I could follow!

[@excelisfun]

Here is my Advanced Excel Class: kzbin.info/aero/PLrRPvpgDmw0lcTfXZV1AYEkeslJJcWNKw Here is my Statistics Class: kzbin.info/aero/PLrRPvpgDmw0ngx_uPhvasTbOWLOztsaBj Here is my Analytics Class: kzbin.info/aero/PLrRPvpgDmw0mSJCZaqQPFj0eto4qnzkCZ

[@amnirup8075]

Thank you sir :)

[@excelisfun]

You are welcome! Thanks for your support with a Thumbs Up and comment on each : )

[@tjpurohit80]

The Craziest and Easiest formula in this case would be =(SUM(B11:35)-SUM(A11:A35))/COUNT(B11:B35) or =SUM(SUM(AnswerShipDate)-SUM(AnswerOrderDate))/COUNT(AnswerShipDate)

[@excelisfun]

No doubt, this is really a good formula: =(SUM(B11:35)-SUM(A11:A35))/COUNT(B11:B35)

[@SIEXLExcelyPowerBI]

Hi! Another way to do it: =AVERAGE(MMULT(A8:B32*{-1,1},{1;1})) Blessings!

[@excelisfun]

Very cool!!!! Thanks for the matrix formula!

[@planxlsm]

1:50