SOLVED

New Contributor

# Avergae of Date range

Hello,

I have two columns of dates and I need to be able to work out the total average without adding an additional helper column. Also, I need the formula to not count blank cells.

E.g

 Date Received Date Sent 26/02/2020 05/03/2020 28/02/2020 03/03/2020 03/03/2020 11/03/2020 04/03/2020 07/03/2020 06/03/2020

Average No. of days to send: Now, I know one formula is to do

= AVERAGE(Date Sent - Date Received)

However, this even takes a blank cell in to count.

How can I do this without including rows which do not have a date entered in Date Sent?

Thank you.

8 Replies

# Re: Avergae of Date range

For such sample

that could be

``=AVERAGE(B2:INDEX(B2:B100,MIN(COUNTA(A:A),COUNTA(B:B))-1)-A2:INDEX(A2:A100,MIN(COUNTA(A:A),COUNTA(B:B))-1))``

=AVERAGE(C1:C6)

# Betreff: Avergae of Date range

or a Matrix formula
{=AVERAGE(B1:B6-A1:A6)
Leave the cell editor with Ctrl + Shift + Enter instead of Enter alone.

# Betreff: Avergae of Date range

That doesn't work if some dates in column B are empty

Best Response confirmed by Mo_Islam00 (New Contributor)
Solution

# Re: Avergae of Date range

Previous variant works if only blanks are at the end of second column. More simple and more reliable variant shall work if in the middle as well:

with

``=AVERAGE(IF(B2:B100=0,"",B2:B100-A2:A100))``

You may expand by dynamic ranges based on column A as in previous formula.

# Re: Avergae of Date range

Aah, great. thank you for double checking. You are right, previous only works with the last being empty, however this formula works perfectly.

# Re: Avergae of Date range

@Mo_Islam00 , you are welcome

# Re: Avergae of Date range

@Mo_Islam00 How can the date sent be later than the date received??