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.

Workings

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.

Duncan Williamson

One Response to “Use SOLVER to Solve Brain Teasers”


  1. I admired your helpful article. awesome stuff. I hope you release others. I will continue watching


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: