Getting Goal Seek or Solver to work
 

I am trying to calculate the number of servings of a recipe that must
be produced so that the per unit cost in cash exactly matches the
variable unit cost.

Column A is the list of ingredients, the columns B&C show the number
and unit of measure for making 1 batch. Columns D&E show the count
and unit of measure for the minimum purchase for each ingredient.
Column F lists the cost of those minimum purchases. Column G
calculates the cash cost per ingredient, given the number of units
that are being considered.


G$27= servings being considered
Batch_Yield=6


A B C D E
F
Row
28 Lemon 6 each 12 per dozen $6
29 Sugar 1 cup 2 cups per lb $1.25
30 Ice 24 oz(v) 160 oz per 10lb page $1.39
31 Cups 6 each 50 cnt per pkg $1.50

G
=ROUNDUP(ROUNDUP(G$27/Batch_Yield,0)*$C28/$E28,0)*F28

This equation is filled down to row 31. G32 is totals column G and
divided by the G$27 and compared to the variable unit cost .67 by
subtracting one from the other.
=(sum(G28:G31)/G$27)-.67

I have tried Goal Seek and Solver to change G$27 to set G32 to zero.
I get erratic answers. What am I doing wrong?

I know the answer is 600, but need help understanding what's wrong.

Getting Goal Seek or Solver to work
 

=ROUNDUP(ROUNDUP(G$27/Batch_Yield,0)*$C28/$E28,0)*F28


I have tried Goal Seek and Solver to change G$27 to set G32 to zero.
I get erratic answers. What am I doing wrong?


Hi. In general, Solver will not work well because you are using the discontinuous function "RoundUp."

I can not follow your equation too well because it looks like you are referencing text.
--
HTH :)
Dana DeLouis


"Geri" wrote in message ...

I am trying to calculate the number of servings of a recipe that must
be produced so that the per unit cost in cash exactly matches the
variable unit cost.

Column A is the list of ingredients, the columns B&C show the number
and unit of measure for making 1 batch. Columns D&E show the count
and unit of measure for the minimum purchase for each ingredient.
Column F lists the cost of those minimum purchases. Column G
calculates the cash cost per ingredient, given the number of units
that are being considered.


G$27= servings being considered
Batch_Yield=6


A B C D E
F
Row
28 Lemon 6 each 12 per dozen $6
29 Sugar 1 cup 2 cups per lb $1.25
30 Ice 24 oz(v) 160 oz per 10lb page $1.39
31 Cups 6 each 50 cnt per pkg $1.50

G
=ROUNDUP(ROUNDUP(G$27/Batch_Yield,0)*$C28/$E28,0)*F28

This equation is filled down to row 31. G32 is totals column G and
divided by the G$27 and compared to the variable unit cost .67 by
subtracting one from the other.
=(sum(G28:G31)/G$27)-.67

I have tried Goal Seek and Solver to change G$27 to set G32 to zero.
I get erratic answers. What am I doing wrong?

I know the answer is 600, but need help understanding what's wrong.