SOLVED

Avergae of Date range

Copper Contributor

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 ReceivedDate Sent
26/02/202005/03/2020
28/02/202003/03/2020
03/03/202011/03/2020
04/03/202007/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

@Mo_Islam00 

For such sample

image.png

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

@NikolinoDE 

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

best response confirmed by Mo_Islam00 (Copper Contributor)
Solution

@Mo_Islam00 

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:

image.png

with

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

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

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

@Mo_Islam00 , you are welcome

 

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

 

1 best response

Accepted Solutions
best response confirmed by Mo_Islam00 (Copper Contributor)
Solution

@Mo_Islam00 

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:

image.png

with

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

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

View solution in original post