Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Newbie: worksheet function advise (huge please)
I work for a restaurant that uses a point-of-sale system and a liquor
control system to keep track of sales and inventory. My problem is how to reconcile what was ordered in the pos system with what was actually poured in the lcs system. Since the liquor system dispenses potion sizes of brands and the pos system orders a recipes, I thought that the easiest way to balance would be to do a recipe explosion on the pos side and creating a variance report. However, first thing first. I have so far created a recipe matrix that looks like below. Example, an Amaretto Sour uses 1oz of Amaretto in the recipe as indicated in the table. The idea is then to add all of the instances in which Amaretto is used in all of the recipes for a total number of ounces poured. My problem is how to calculate the total amount of ounces of any given product in a recipe if that recipe has been ordered several times...example, how can I create a calculation that resides in the cell where the recipe's portion size is found, so that in this example the 2 Amaretto Sours sold will have 2oz of Amaretto? What function or combination of functions can I use? Can I use a pivot table? Lastly, please remember that I am a newbie and that I can't program VBA. Thanks in advance...Carlos # COCKTAILS SOLD 1 | 2 | 3 | TOT OZ POURED/BRAND | BRAND NAME | COCKTAIL NAME Alabama Slammer | Amaretto Sour | Apricot Sour | Etc. 0.00 Absolute 0.00 0.00 0.00 1.00 Amaretto 0.00 1.00 0.00 0.00 Amarula 0.00 0.00 0.00 1.00 Apricot Brandy 0.00 0.00 1.00 0.00 Bacardi White 0.00 0.00 0.00 0.00 Bailey's 0.00 0.00 0.00 |
#2
|
|||
|
|||
On Fri, 23 Sep 2005 13:48:20 -0400, "CF" wrote:
I work for a restaurant that uses a point-of-sale system and a liquor control system to keep track of sales and inventory. My problem is how to reconcile what was ordered in the pos system with what was actually poured in the lcs system. Since the liquor system dispenses potion sizes of brands and the pos system orders a recipes, I thought that the easiest way to balance would be to do a recipe explosion on the pos side and creating a variance report. However, first thing first. I have so far created a recipe matrix that looks like below. Example, an Amaretto Sour uses 1oz of Amaretto in the recipe as indicated in the table. The idea is then to add all of the instances in which Amaretto is used in all of the recipes for a total number of ounces poured. My problem is how to calculate the total amount of ounces of any given product in a recipe if that recipe has been ordered several times...example, how can I create a calculation that resides in the cell where the recipe's portion size is found, so that in this example the 2 Amaretto Sours sold will have 2oz of Amaretto? What function or combination of functions can I use? Can I use a pivot table? Lastly, please remember that I am a newbie and that I can't program VBA. Thanks in advance...Carlos # COCKTAILS SOLD 1 | 2 | 3 | TOT OZ POURED/BRAND | BRAND NAME | COCKTAIL NAME Alabama Slammer | Amaretto Sour | Apricot Sour | Etc. 0.00 Absolute 0.00 0.00 0.00 1.00 Amaretto 0.00 1.00 0.00 0.00 Amarula 0.00 0.00 0.00 1.00 Apricot Brandy 0.00 0.00 1.00 0.00 Bacardi White 0.00 0.00 0.00 0.00 Bailey's 0.00 0.00 0.00 If I'm not interpreting this correctly, please say so. It appears that your column for Tot oz Poured/Brand is the simple sum of all the ozs for all the cocktails. This is probably irrlelevant since I suspect what you want is the total oz/cocktail multipled by the Cocktails sold. However leaving your original matrix in place I have the following A1 = "Cocktails Sold"; C1 = 1; D1=2; E1=3 A2 = "Total oz poured"; C2= =C1*SUM(C5:C10) copy C2 to D2 and E2 A4 = "Tot oz poured/Brand"; B4= "Brand Name"; C4="Alabama Slammer"; D4="Amaretto Sour"; E4="Apricot Sour" In the Range A5:E10 all the numbers are zero except A6, D6, A8, E8 which are 1. I've interpreted the question so that it is row 2 which is giving you the totals you're seeking based on the input to C1:E1 HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
changing font style in a complex worksheet function | Excel Worksheet Functions | |||
Worksheet function in excel only works once | Excel Worksheet Functions | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
SUMIF function referring to values on different Worksheet | Excel Worksheet Functions | |||
clock | Excel Worksheet Functions |