Forum Discussion
Solved
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 ...
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.
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))