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.

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.

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

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 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