Averaging-completed e-mail question

[John Sullivan]

[last post accidentally sent before finished, sorry] I
have 12 workbooks that each contain a worksheet for each
day of each month (thus, up to 31 worksheets for each
month for each workbook). I have a 13th workbook which
sums up the totals from the first 12 workbooks' data.
Each workbook contains inventory items that are sold
throughout the year. I would like a formula that would
calculate the average units sold and the average dollars
sold for each inventory item but averaging ONLY in the
months in which sales were made. For example, if
inventory item #12456 is listed in all 12 workbooks but
only sold in 4 of the 12 months, I would like the formula
to disregard the other 8 months and only provide an
average based on the number of months in which sales were
generated, to show a per-month average number of units
sold and average dollars sold. A formula would somehow
need to know to pull data only from the months in which
sales occurred, not for months in which sales did not
occur, for averaging.

 

[Jim]

I think you are going to need to use additional columns if you are doing
this using worksheet functions. I have no idea how you currently have your
data arranged. Add an additional column for the month and format it to
display the month name. Then you may use a couple of solutions, either
Data>Filter>Autofilter -OR- Data>Pivot Table. In the pivot table, you may
add a calculated item based on the data. Post back once you have made a
start if you hit a snag.