Using Excel with the Solver Add-In To Solve The Farmers Riddle

Hi everyone, Martin Pavion here. I have maybe a little bit too much time on my hands this week, and one of my friends sent me a really cool maths puzzle which I solved using Excel. I love using Excel. I consider myself great— or good with Excel for sure. I'm not good with macros, but I've been using it for many years. I'm great with all the formulas. I can generally solve all the problems I need to using it. So I did solve it, and one of my friends solved it in his head. And I'm like, wow, you know, that's really impressive. I've had to use Excel. Is there a smarter way of doing it? And this led me on to searching for an advanced add-on for Excel called Solver. And I've never used it before, and it's beautiful. It actually finds all the missing values for you automatically, pretty much. You tell it what the constraints are, and it's just such a beautiful way of solving things that I wanted to Explain my solutions with you here and give you an introduction as to how to use Solver for—

well, I'd like to say everyday problems, if you're a farmer and you've got £100. Or perhaps you can find some other uses for them.

But here's the riddle. So thank you very much, Jonathan, for this. Just for laughs, riddle me this.

There is an answer.

You have £100 exactly to spend on exactly 100 beasts. You have to buy at least one of each animal. Pigs cost £5 each. Sheep cost £1 each.

Hens cost 5 pence each.

How many of each do you need to buy? Remember, you must spend all £100 on exactly 100 animals. So ultimately, if you read the question, if you want to pause it and have a think about it, the kind of answer we're looking for is going to be a number.

I need to buy x amount of pigs, x amount of sheep, x amount of hens, and that number should Add up to 100, and it should also cost exactly £100.

So here's how I solved it myself.

It took me about 5 minutes, so I can explain it. So we have pigs, sheep, and hens. Pigs are £5, sheep are £1, and hens are 5 pence. So—

5 pence.

Here's our cost.

So first thing I noticed is we can only buy so many of each animals, and because the pigs are most expensive, we've got a range of answers we can have, really.

We've got to buy at least one of each, so we know what we've got to— the number of pigs we buy is going to be at least 1, but it can't be more than 20 because that would be exactly £100, and we know we've got to buy sheep and hens as well, so it would have to be a maximum of 19. So I'm going to write down all the different permutations here between 1 and 19. So our answer is going to be somewhere along those lines. OK, so here— this is our quantity here. OK, this is our quantity.

OK.

Now, this means that we know that the answer is going to be one of those.

18, so 19 answers. So we've got to work out what number of sheep and hens we're going to buy.

OK, so we don't need to learn— we don't need to need both of them because we know that ultimately the answer's got to equal 100 animals, 100 beasts. So what I'm going to do is, because hens are so cheap, we can buy loads of them, we can just make a formula here that The number of hens is going to be 100 minus however many pigs we've bought minus however many sheep we've bought.

OK, so 100 minus that minus that.

OK.

So this won't be our answer at the moment because we know that sheep has got to be at least 1.

OK?

But this is automatically going to fill in, so we can forget about this one as such now.

Whatever we add here—

OK.

This is what we're looking for.

Okay, so we're looking for a value in here which ultimately is going to lead to the total number to be 100.

Okay, and we can calculate that as the sum of these. These will all be 100 because we've added that formula for the hens just to add whatever number of hens we need to make 100.

Okay, but the other way—

the other thing we need to find is total cost. Okay, now the total cost we would calculate by the number of pigs times by the cost of the pigs plus the number of sheep times by the cost of sheep plus the Number of hens times the cost of hens. So ultimately this has to work out at £100 as well.

£100. Oh, hang on one second, because the costs are always fixed. Copy them across like that.

Okay, so now we need to work out how many sheep we're going to need so that these totals in here both add up to 100— 100 beasts and 100 pounds. Okay, so here's how I did it. Okay, I just guessed basically.

I realized there's only going to be a certain number.

Okay, so we'll set up here number of sheep.

Okay, I'm gonna stick it at 0 just for now. Okay, obviously we need to have at least 1. It's not a pound value, it's just a number value.

Okay, and we're gonna put that in all of these here, and we're going to play around with how many sheep we can have. And what we're looking for is this number here The cost here is going to change, and we're looking to find out whichever one of these shows with exactly £100, because whatever we use here, the number of hens is going to adjust to make sure that we always have 100 beasts anyway.

So let's give it a try.

We know that 0 definitely isn't going to be acceptable because we've got to—

you've got to— 100.

Say you must—

oh yeah, you must have at least one of each animal.

So we know that doesn't work.

If we enter 1, 1 sheep, you see it's adjusted the calculations.

When we go along here, we have an answer.

Here it is. Okay, so we need 19 pigs, 1 sheep, 80 hens, and And that gives us 100 beasts at exactly £100.

So that's how I solved it. It would have been kind of trial and error though.

If that hadn't have worked, I would have had to have gone up to 2 sheep, 3 sheep, etc., until I got the— until I found that £100 somewhere.

So I kind of got lucky that the first guess I tried worked. But let me show you Solver.

Solver is beautiful and will do this even quicker than that. Only learning—

the only time taken is to actually understand what Solver is.

So we're gonna go back to putting the basics in.

What we're doing here, we're just setting up all the formulas we're going to need, and then we're going to tell Excel basically we're going to look for these 2 values—

quantity—

to make sure that Whatever our total answer is going to be, 100 animals at £100.

So, total number, total— okay, oops, a little bit of OCD here.

Make a typo. Okay, so what's great about Solver is it will look for more than one value.

Okay, we don't need all 3 because we can still apply that same formula before of You know, the quantity of hens is going to be 100 minus the number of sheep minus the number of pigs. Okay, so we know as well the total number is going to be the sum of these. Okay, obviously we haven't got the answers yet, so these numbers don't, don't really make sense, but we're just putting the formulas in. Okay, and again, do you remember that total cost? It's going to be equals that Number of pigs times by the cost of pigs plus the number of plus the number of sheep times the cost of sheep plus the number of hens number of hens times the cost of the hens. Okay, and As you'll remember, these have to end up at 100, and they're not doing it yet because we haven't applied Solver, but we'll come to that in a minute. Okay, actually, this is— yeah, I think this is all we actually need to get Solver working.

So Solver is an add-on, so to find it, you may— I don't know what version of Excel you're using.

I'm assuming it's available for the most modern versions of Excel anyway. Here it is, Developer. Okay, you may have to Google how to activate developer mode if it's not up. I think it's— just double-check, I can't quite remember. I think there's an option somewhere like Activate Developer Mode.

I think there is.

Yeah, you can Google it.

It's fine.

But yeah, I think you need to have developer mode enabled when you have and you get this add-in section.

Okay, Excel add-ins, and this is it: Solver add-in.

You need to make sure that's ticked and click on OK.

When you've done that, you go back to add-ins.

We see Solver here. So yeah, Solver is beautiful. I'm so happy to have discovered it.

It's overly complicated for what we need to do.

I don't understand what we need for objectives, so you leave it blank.

Variables though, we need to tell Excel what we need to change.

Okay, so we're going to add one, and that's going to be C13 here.

Just type that in, C13.

Okay, and also here.

Here, C14.

So that's easy enough, I can see them there.

So this is what we're going to change to get our answer.

Now constraints is the fun one.

Okay, so this is what needs to happen in order— if Excel solves the formula, what are the rules?

So here's the first rule. The first rule is that this here So C16, C16 has got to be equal to 100.

So we add that, and then the next one is C17 here, has got to be equal to £100.

Right, and what I found was, you see these 2 here, I'm not sure why it keeps adding this one.

But what I realized originally was when I got Solver working on this, it found me solutions, but it was using decimals.

So it was telling me to use 3/4 of a pig and things like that. And obviously we don't want that for this type of solution. So we need to add 2 more constraints here, and that constraint has got to be that C13 Is an integer. Integer.

Okay, that's all we need to do, and the same with C14.

Okay, and I don't know why it keeps adding this one. I'm not sure. It seemed to have caused problems, so I just delete that.

This is all we need. Basically, we say to Excel, put the values in here, make sure that they're whole numbers, not decimals, And whatever you do, it needs to make sure that the total number and the total cost is 100. So that's all we do.

We click on Play.

It sounds really complex—

posting model to JSON service. Don't quite know what that means, but it does sound like it's doing a load of work for us. Not sure why it's taking longer than usual. Normally it just takes a few seconds.

Maybe an internet issue. We'll see where it goes.

Literally, progress loading.

Solver found a solution.

All constraints are optimally conditioned and optimality conditions are satisfied.

So you see here, Excel has found this for us.

It's put 19 pigs and 1 sheep, and it's given us our answer.

So I love this way.

I think there's so many uses for that, not that I've found any yet, but I'm probably going to spend a lot of my Christmas going online, finding maths puzzles, and using Solver to see how I can get the results for that.

So I hope that's been an education for you, and if you have as boring a Christmas as me and you enjoy solving maths puzzles during it, good luck! Thank you very much.

I might do some more videos when I find some more riddles like this as well. So have a great Christmas.

Bye.