Date Calculation

[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 P2750, 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

 

[Frank Kabel]

Hi
=AVERAGE(P2750)

it will ignore blank cells. If you need to exclude zeros use the
following array formula (entered with CTRL+SHIFT+eNTER):
=AVERAGE(IF(P2750<>0,P2750))

 

[Myrna Larson]

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.