Date Calculation

  • Thread starter Thread starter Dudley Wright
  • Start date Start date
D

Dudley Wright

I have a spread sheet with dates entered in the range K2:K750 and in
O2:O750. There are many dates that have not yet been entered. I am
calculating the number of weeks between the two dates with the formula
=(O2-K2)/7 and then filled the formula down to row 750. This is done
in column P.
My problem is that I must calculate the average number of weeks.
How do I create a formula to calculate the average of values in the
range P2:P750, without including those cells in rows where one or both
of the dates in column K or O have not yet been entered. The formula
should update as date information is added.
Thank you for any suggestions.

Dudley
 
Hi
=AVERAGE(P2:P750)

it will ignore blank cells. If you need to exclude zeros use the
following array formula (entered with CTRL+SHIFT+eNTER):
=AVERAGE(IF(P2:P750<>0,P2:P750))
 
I would change the formula in column P to

=IF(COUNT(O2,K2)<>2,"",(O2-K2)/7)

This will return empty text if you haven't entered the dates in both columns K
and O. AVERAGE ignores text entries, so these rows won't be included.
 
Back
Top