Average function question

[Sum Limit and marking]

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.

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

In article <[email protected]>,
Sum Limit and marking <[email protected]>
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.

[Franz Verga]

Nel post news:[email protected]
*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

[Sum Limit and marking]

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 news:[email protected]
> *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 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:D"&COUNTA(D:D))>=0,INDIRECT("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 news:[email protected]
>> *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