I see now that there was some mistake (due to copy & paste) in the formulas
I posted. To determine the average lead time try this:

=AVERAGE(IF(INDIRECT("D2:D"&COUNTA(D:D))=0,INDIRE CT("D2:D"&COUNTA(D:D)),"")

or if you have an header column in D1 you should instead use:

=AVERAGE(IF(INDIRECT("D2:D"&(COUNTA(D:D)-1))=0,INDIRECT("D2:D"&(COUNTA(D:D)-1)),"")

both array entered (press simultaneously Ctrl + Shift + Enter).

Sum Limit and marking wrote:
Franz,

Thanks for the tip, however, I could not get it to work. Do you have
any other suggestions?

Thanks.

"Franz Verga" wrote:

Nel post
*Sum Limit and marking* ha scritto:

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.


Maybe you can use:

(If(INDIRECT("D2:D"&COUNTA(C:C))=0,=INDIRECT("D2: D"&COUNTA(C:C)))

if you have an header column in D1 you should instead use:

(If(INDIRECT("D2:D"&(COUNTA(C:C)-1))=0,=INDIRECT("D2:D"&(COUNTA(C:C)-1)))

--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Ciao

Franz Verga from Italy

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy