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