Average function question
 

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.

Average function question
 

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.

Average function question
 

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

Average function question
 

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

Average function question
 

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