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

Download Files:

Start File and Finished File:

See how to calculate the Average Number of Days Between Order Date & Ship Date with three different methods:

1. (00:10) Introduction
2. (00:25) Helper Column Method & AVERAGE Function
3. (02:22) Array Formula #1: AVERAGE Function with Array Operation. This Requires the special keystrokes: Ctrl + Shift + Enter
4. (03:12) Learn how to Name Ranges (Defined Names) with Create Names From Selection feature
5. (08:36) Array Formula #2: SUMPRODUCT, Array Operation & COUNT function
6. (11:42) Summary

Reference Videos:
Excel Magic Trick 1389: Average Number of Days Between Two Columns of Dates
Excel Magic Trick 1390: Average Number of Days Between a Single Columns of Dates

[LeilaGharani]

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

[BeachMan]

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

[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.

[bricc]

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!

[BillSzysz]

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, "<>")

[brianstanfill]

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

[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!

[احمدمحمد-ععج]

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.

[terrianderson]

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

[ihyde]

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

[alandouglas]

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?

[tjpurohit]

The Craziest and Easiest formula in this case would be or

[JohnVergaraD]

Hi!

Another way to do it:

=AVERAGE(MMULT(A8:B32*{-1, 1}, {1;1}))

Blessings!

[ddelgadillo]

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.

[amnirup]

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!