Forum Discussion
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.
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.
8 Replies
Mo_Islam00 How can the date sent be later than the date received??
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.
- 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
- =AVERAGE(C1:C6)
- or a Matrix formula
{=AVERAGE(B1:B6-A1:A6)
Leave the cell editor with Ctrl + Shift + Enter instead of Enter alone.That doesn't work if some dates in column B are empty
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))
