Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Goal seek - Is there a bug? | Excel Discussion (Misc queries) | |||
Solver and Goal Seek Tutorials | Excel Worksheet Functions | |||
Automate goal seek or solver | Excel Discussion (Misc queries) | |||
goal seek | Excel Discussion (Misc queries) | |||
goal seek vs solver | Excel Worksheet Functions |