Here are two brain teaser type questions that I came across today and I thought I’d demonstrate how you might use SOLVER to find the answers.
1 You have two pencils, a good one and a cheap one. The good one costs $1.00 more than the cheap one. You spend $1.10 for both. How much did the cheap one cost?
2 A farmer goes to the market with £100. He has to buy EXACTLY 100 animals and must spend the whole £100. He must buy at least one of each available animal. The animals for sale that day were
COWS £10 each
PIGS £3 each
CHICKENS £0.50 each
How many of each animal did the farmer buy?
The answers are
1 The expensive pencil costs £1.05 and the cheap pencil costs £0.05
2 The farmer must buy 5 cows for £50, 1 pig for £1 and 94 chickens for £47, giving a total of £100.
For problem one, work through the screen shots below to apply the SOLVER add in to these brain teasers. Do notice that in each case we are trying to find a specific value and not a maximum or minimum amount.
The target cell the total total cost in this case, contains an =SUM() formula that adds together the total cost for each of the pencils. The total total cost must exactly equal £1.10.
The total cost column, apart from its total, must have formulas in them too: number of pencils * cost each … of course we do not NEED the number of pencils column but I have included them in case you want to extend the example.
The SOLVER dialogue box for this problem follows:
The range for this problem starts in cell E7 with a blank cell!
For problem two, consider the following two screenshots: consider where I have used formulas and what those formulas must be, apart from the formula that the target cell MUST contain. Which is the target cell, too, by the way?
The range for this problem starts in cell A2 with a blank cell!
Need help? Just add a comment and I will see it and then respond.