Aug 06 2020 09:29 AM
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.
Aug 06 2020 09:43 AM
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))
Aug 06 2020 09:48 AM
Aug 06 2020 10:00 AM
That doesn't work if some dates in column B are empty
Aug 06 2020 10:15 AM
SolutionPrevious 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.
Aug 06 2020 11:25 AM
Aug 06 2020 01:41 PM
@Mo_Islam00 How can the date sent be later than the date received??
Aug 06 2020 10:15 AM
SolutionPrevious 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.