Newbie: worksheet function advise (huge please)

[CF]

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

 

[Richard Buttrey]

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
__________________________