If you have Excel 2003 or later, you can convert your data into a list...

Data List Create List

....and your formula will automatically adjust. Otherwise, you can use a
dynamic named range...

Insert Name Define

Name: Range (or name it whatever else you wish)

Refers to:

$D$2:INDEX($D$2:$D$65536,MATCH(9.99999999999999E+3 07,$D$2:$D$65536))

Click Ok

Then, use the following formula...

=AVERAGE(IF(Range=0,Range))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
Sum Limit and marking
wrote:

I have created a query pulling dates for lead time, I have a column for date
ordered, date expected, and actual date of arrival.

My data is organized as followed:

Date Ordered, Date Expected, Arrival Date, Lead Time, Average Lead Time.

In order for me to determine the lead time I am using the function,
"networkdays(A2+1,C2).

To determine the average lead time I am using the function,
"(If(D2:D10=0,D2:D10)

The issue that I am having is when I update my query for another supplier or
change my query date my Average lead time function will only read data up to
row 10 and nothing beyond.

Is there a reason why this function will not update to read all the data
within column D?

Thanks.