averages without zero

[Guest]

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?

 

[Stan Brown]

Thu, 5 Jul 2007 15:46:01 -0700 from RcWend

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)

 

[Guest]

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

 

[Susan]

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

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

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 -

 

[Guest]

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

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

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

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 -

 

[Harlan Grove]

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.

 

[Guest]

That's it!! Thanks much..

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.