This is the formula I have now in order to caculate the average sales price
that each salesperson has sold their item for.
=AVERAGE('R:\Sales Forcast\Current Forecast\2008 Forecast\[Randy
Customers.xls]Randy Totals'!AK4,'R:\Sales Forcast\Current Forecast\2008
Forecast\[Lynn Customers.xls]Lynn Totals'!AK4,'R:\Sales Forcast\Current
Forecast\2008 Forecast\)
But between these 2 worksheets, there are some items one sales person has
sold & one has not & some both sales people have sold, but not necessarily
for the same price....I want to calculate the average price of an item
between the two, but not include the average if that salesperson has not sold
the item.
Can someone help?

Thu, 5 Jul 2007 15:46:01 -0700 from RcWend
<(E-Mail Removed)>:
> This is the formula I have now in order to caculate the average sales price
> that each salesperson has sold their item for.
> =AVERAGE('R:\Sales Forcast\Current Forecast\2008 Forecast\[Randy
> Customers.xls]Randy Totals'!AK4,'R:\Sales Forcast\Current Forecast\2008
> Forecast\[Lynn Customers.xls]Lynn Totals'!AK4,'R:\Sales Forcast\Current
> Forecast\2008 Forecast\)
> But between these 2 worksheets, there are some items one sales person has
> sold & one has not & some both sales people have sold, but not necessarily
> for the same price....I want to calculate the average price of an item
> between the two, but not include the average if that salesperson has not sold
> the item.
> Can someone help?

=SUM(...) / COUNTIF(... <>0)
--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Thanks for the response, but still don't understand. Can you please put it
into the formula I have?

"RcWend" wrote:

> This is the formula I have now in order to caculate the average sales price
> that each salesperson has sold their item for.
> =AVERAGE('R:\Sales Forcast\Current Forecast\2008 Forecast\[Randy
> Customers.xls]Randy Totals'!AK4,'R:\Sales Forcast\Current Forecast\2008
> Forecast\[Lynn Customers.xls]Lynn Totals'!AK4,'R:\Sales Forcast\Current
> Forecast\2008 Forecast\)
> But between these 2 worksheets, there are some items one sales person has
> sold & one has not & some both sales people have sold, but not necessarily
> for the same price....I want to calculate the average price of an item
> between the two, but not include the average if that salesperson has not sold
> the item.
> Can someone help?

try this (no guarantees it's completely correct!)

=SUM(...) / COUNTIF(... <>0)


=SUM('R:\Sales Forcast\Current Forecast\2008 Forecast\[Randy
Customers.xls]Randy Totals'!AK4,'R:\Sales Forcast\Current Forecast
\2008
Forecast\[Lynn Customers.xls]Lynn Totals'!AK4,'R:\Sales Forcast
\Current
Forecast\2008 Forecast\) /COUNTIF(('R:\Sales Forcast\Current Forecast
\2008 Forecast\[Randy
Customers.xls]Randy Totals'!AK4,'R:\Sales Forcast\Current Forecast
\2008
Forecast\[Lynn Customers.xls]Lynn Totals'!AK4,'R:\Sales Forcast
\Current
Forecast\2008 Forecast\) <>0)

hth

susan


On Jul 6, 11:24 am, RcWend <RcW...@discussions.microsoft.com> wrote:
> Thanks for the response, but still don't understand. Can you please put it
> into the formula I have?
>
>
>
> "RcWend" wrote:
> > This is the formula I have now in order to caculate the average sales price
> > that each salesperson has sold their item for.
> > =AVERAGE('R:\Sales Forcast\Current Forecast\2008 Forecast\[Randy
> > Customers.xls]Randy Totals'!AK4,'R:\Sales Forcast\Current Forecast\2008
> > Forecast\[Lynn Customers.xls]Lynn Totals'!AK4,'R:\Sales Forcast\Current
> > Forecast\2008 Forecast\)
> > But between these 2 worksheets, there are some items one sales person has
> > sold & one has not & some both sales people have sold, but not necessarily
> > for the same price....I want to calculate the average price of an item
> > between the two, but not include the average if that salesperson has not sold
> > the item.
> > Can someone help?- Hide quoted text -
>
> - Show quoted text -

Thanks, Susan..but it just gives me an error message..anyone else?

"Susan" wrote:

> try this (no guarantees it's completely correct!)
>
> =SUM(...) / COUNTIF(... <>0)
>
>
> =SUM('R:\Sales Forcast\Current Forecast\2008 Forecast\[Randy
> Customers.xls]Randy Totals'!AK4,'R:\Sales Forcast\Current Forecast
> \2008
> Forecast\[Lynn Customers.xls]Lynn Totals'!AK4,'R:\Sales Forcast
> \Current
> Forecast\2008 Forecast\) /COUNTIF(('R:\Sales Forcast\Current Forecast
> \2008 Forecast\[Randy
> Customers.xls]Randy Totals'!AK4,'R:\Sales Forcast\Current Forecast
> \2008
> Forecast\[Lynn Customers.xls]Lynn Totals'!AK4,'R:\Sales Forcast
> \Current
> Forecast\2008 Forecast\) <>0)
>
> hth
>
> susan
>
>
> On Jul 6, 11:24 am, RcWend <RcW...@discussions.microsoft.com> wrote:
> > Thanks for the response, but still don't understand. Can you please put it
> > into the formula I have?
> >
> >
> >
> > "RcWend" wrote:
> > > This is the formula I have now in order to caculate the average sales price
> > > that each salesperson has sold their item for.
> > > =AVERAGE('R:\Sales Forcast\Current Forecast\2008 Forecast\[Randy
> > > Customers.xls]Randy Totals'!AK4,'R:\Sales Forcast\Current Forecast\2008
> > > Forecast\[Lynn Customers.xls]Lynn Totals'!AK4,'R:\Sales Forcast\Current
> > > Forecast\2008 Forecast\)
> > > But between these 2 worksheets, there are some items one sales person has
> > > sold & one has not & some both sales people have sold, but not necessarily
> > > for the same price....I want to calculate the average price of an item
> > > between the two, but not include the average if that salesperson has not sold
> > > the item.
> > > Can someone help?- Hide quoted text -
> >
> > - Show quoted text -
>
>
>

"RcWend" <(E-Mail Removed)> wrote...
....
>"RcWend" wrote:
>>This is the formula I have now in order to caculate the average sales
>>price
>>that each salesperson has sold their item for.
>>=AVERAGE('R:\Sales Forcast\Current Forecast\2008 Forecast\[Randy
>>Customers.xls]Randy Totals'!AK4,'R:\Sales Forcast\Current Forecast\2008
>>Forecast\[Lynn Customers.xls]Lynn Totals'!AK4,'R:\Sales Forcast\Current
>>Forecast\2008 Forecast\)
....

So you're pulling single values from multiple files? If so, don't try to
average them directly. You'd be MUCH better off pulling the values into
separate cells in your summary workbook, e.g.,

C3:
='R:\Sales Forcast\Current Forecast\2008 Forecast\[Randy Customers.xls]
Randy Totals'!AK4

C4:
='R:\Sales Forcast\Current Forecast\2008 Forecast\[Lynn Customers.xls]
Lynn Totals'!AK4

etc., then using a formula like

=SUM(C4:C#)/COUNTIF(C4:C#,">0")

where C# is a placeholder for the bottommost row in the range containing the
separate external reference links to the other files.

That's it!! Thanks much..

"Harlan Grove" wrote:

> "RcWend" <(E-Mail Removed)> wrote...
> ....
> >"RcWend" wrote:
> >>This is the formula I have now in order to caculate the average sales
> >>price
> >>that each salesperson has sold their item for.
> >>=AVERAGE('R:\Sales Forcast\Current Forecast\2008 Forecast\[Randy
> >>Customers.xls]Randy Totals'!AK4,'R:\Sales Forcast\Current Forecast\2008
> >>Forecast\[Lynn Customers.xls]Lynn Totals'!AK4,'R:\Sales Forcast\Current
> >>Forecast\2008 Forecast\)
> ....
>
> So you're pulling single values from multiple files? If so, don't try to
> average them directly. You'd be MUCH better off pulling the values into
> separate cells in your summary workbook, e.g.,
>
> C3:
> ='R:\Sales Forcast\Current Forecast\2008 Forecast\[Randy Customers.xls]
> Randy Totals'!AK4
>
> C4:
> ='R:\Sales Forcast\Current Forecast\2008 Forecast\[Lynn Customers.xls]
> Lynn Totals'!AK4
>
> etc., then using a formula like
>
> =SUM(C4:C#)/COUNTIF(C4:C#,">0")
>
> where C# is a placeholder for the bottommost row in the range containing the
> separate external reference links to the other files.
>
>
>