Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averageing Costs
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averageing Costs
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<=DA TE(2007,2,28)),B1:B100)) both formula needs to be entered using Ctrl+Shift+Enter HTH Jean-Guy "Harlan" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averageing Costs
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. "pinmaster" wrote: 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<=DA TE(2007,2,28)),B1:B100)) both formula needs to be entered using Ctrl+Shift+Enter HTH Jean-Guy "Harlan" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COMPARE CURRENT MORTAGAGE COSTS TO NEW MORTAGAGE COSTS | Excel Discussion (Misc queries) | |||
COMPARE CURRENT MORTAGAGE COSTS TO NEW MORTAGAGE COSTS | Excel Discussion (Misc queries) | |||
Estimating costs | Excel Worksheet Functions | |||
Formula For Costs. ? | New Users to Excel | |||
shipping costs using if then | Excel Worksheet Functions |