Averageing Costs

[Guest]

I have created a spreadsheet that does recipe costing and inventory control
in one. Now I'm trying to add another component. I would like to be able to
enter the prices on the invoices that we get for various products and have
them be averaged for the entire period. I then want those averages to be
reflected in the inventory master form. The problem as I see it, is setting
up a way to enter the prices over the given period for each item. Is there a
way to possibly nest a column of cells within a sheet?
Any help would be appreciated.
Thanks

 

[Guest]

Hi,

What does "and have them be averaged for the entire period" means excatly?

Perhaps something like this?

Say you had dates in A1:A100 and values in B1:B100 and wanted to average
only the values from the month of Feb. then you would use:

=AVERAGE(IF(MONTH(A1:A100)=2,B1:B100))

or

=AVERAGE(IF((A1:A100>=DATE(2007,2,1))*(A1:A100<=DATE(2007,2,28)),B1:B100))

both formula needs to be entered using Ctrl+Shift+Enter

HTH
Jean-Guy

 

[Guest]

I don't really think that's what I'm looking for. Let me give you a little
more info. By the way, I use this workbook at the restaurant that I work at.
Sorry if this is long, but I want to explain enough so that you (or anyone
else) has a good idea of what I'm working with.
The whole workbook is something in the neighborhood of 100 worksheets. The
first worksheet is a small table with the US measurements (cups, oz., etc.)
and their ounce equivalents. It is used as a lookup for for almost all of
the other sheets.
The next sheet is the main inventory form. It is divided into sections
(meat, dairy, fish, production items, pantry items etc.). There are about
600 items. In the first column is the name of the item. The second column
is how much a single unit of each item weighs (this column is not filled in
all the way down. The third column is the pack that we buy the items in and
the fourth is the price per pack. Fifth and six are price per each and price
per ounce, respectively. After that is
the count unit, the unit that we count the items in for inventory. Next is
count number, which is the number counted when we do the inventory each
period (every four weeks). This number changes the most. After that is the
price that the items are when they are counted (ie. price per pound for
meats or price per can for clam juice). The column after that is the
extension - count number times the count price. Finally the last column is
the totals for each category.
The next 100 or so sheets are costed recipes. These recipes are all linked
to the inventory form. I created a template for costing recipes. When a new
recipe is entered into the workbook, all the user has to do is click on the
drop down boxes under the item column and enter in the amounts of each item.
Everything else is filled in automatically. Once the user determines the
unit that the item will be counted in, then the price per unit is determined
automatically.
That is where I am at right now. At the end of each period, we do counts of
everything in the restaurant to see what our food costs are. We also update
prices of various items, especially proteins. We go through all the invoices
for the period, find the price that the item was, and average all the prices
for the period.
What I want to be able to do is find a way to enter each price (as purchased
price) from the invoices for the period, and have them automatically averaged
and then placed in the as purchased column in the inventory master.
Thank you in advance.