Averaging formula

[John Sullivan]

I have 12 workbooks, each of which contains 31
worksheets, each worksheet representing one day of the
month. In one column, I have a list of inventory items
that are sold throughout the month on various days of the
month. Workbook #13 contains the Year-To-Date totals for
all inventory items. I am looking for a formula that
would calculate the average number of units sold per
month, counting only months in which sales are made. For
example, if item A was only sold in 4 months out of 12, I
would like for the spreadsheet to show a monthly average
of items sold, counting only those months in which sales
were made. In this example, if 25 units were sold in Jan,
25 in February, 25 in May, and 25 in June, for a total of
100 units, I would like the spreadsheet to calculate the
average number of units sold, based on the number of
months in which sales were made. So the formula would say
to itself: "Look for the months in which sales were made
of item A. Total up the number of units that were sold
(100) in the months in which sales were made (4). Now
divide the total sold units (100) by the number of months
(4) in which sales were made, to arrive at an average
number of units sold per month (25) in the months in
which sales were made." In column K of another worksheet
called 'Month End Inventory', each inventory item on each
row has a formula that totals up the sales that were made
for that month, if any sales were made that particular
day. So the formula would need to key off of those totals
from that sheet.

 

[Kevin Stecyk]

John,

You might have a look at my solution, but you should wait to examine some
others as well.

I created four workbooks. On sheet 1 of each workbook, I created a named
range called "Sold".

Books 1-3 were my "monthly" workbooks.
Book 4 was my summary workbook.

To get the average monthly sales, I used the following formula in Book4

=(Book1.xls!Sold+Book2.xls!Sold+Book3.xls!Sold)/SUM((Book1.xls!Sold>0)+(Book
2.xls!Sold>0)+(Book3.xls!Sold>0))

Watch the line wrap.

The bracket placement is important. So in essence, I am simply adding up
the sales for each month as you would do normally, and then in the
denominator I am testing to see if sales were greater than 0. If TRUE, then
it is 1. If it is FALSE, then it is 0. Add up the 1s. I suppose you could
also use "<>" not equal to 0 in place of ">0" (drop the quotes). You can
try that if you wish.

I placed some values in the various books leaving some blank etc. It
worked.

So you can extrapolate this formula for your 12 month year.

Hope this helps.

Regards,
Kevin