Newbie: worksheet function help
 

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



Number of C & S Sold 1 2 3

TOTAL OZ POURED/BRAND Brand Name ALABAMA SLAMMER AMARETTO SOUR APRICOT
SOUR
ABSOLUTE 0.00 0.00 0.00
ALCOOL 0.00 0.00 0.00
1.00 AMARETTO 0.00 1.00 0.00
AMARULA 0.00 0.00 0.00
1.00 APRICOT BRANDY 0.00 0.00 1.00
BACARDI 0.00 0.00 0.00
BAILEY'S 0.00 0.00 0.00

Hi!

How is this data laid out in the spreadsheet? Please be very specific!

Biff

"CF" wrote in message
...
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



Number of C & S Sold 1 2 3

TOTAL OZ POURED/BRAND Brand Name ALABAMA SLAMMER AMARETTO SOUR
APRICOT
SOUR
ABSOLUTE 0.00 0.00 0.00
ALCOOL 0.00 0.00 0.00
1.00 AMARETTO 0.00 1.00 0.00
AMARULA 0.00 0.00 0.00
1.00 APRICOT BRANDY 0.00 0.00 1.00
BACARDI 0.00 0.00 0.00
BAILEY'S 0.00 0.00 0.00