Average function question

[Guest]

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(D210>=0,D210)

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.

 

[Domenic]

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+307,$D$2:$D$65536))

Click Ok

Then, use the following formula...

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

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

Hope this helps!

 

[Franz Verga]

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(D210>=0,D210)

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"&COUNTA(C:C))>=0,=INDIRECT("D2"&COUNTA(C:C)))

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

(If(INDIRECT("D2"&(COUNTA(C:C)-1))>=0,=INDIRECT("D2"&(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

 

[Guest]

Franz,

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

Thanks.

 

[Franz Verga]

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"&COUNTA(D))>=0,INDIRECT("D2"&COUNTA(D)),"")

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

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

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

Franz,

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

Thanks.

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