28 Sep 2018

## Let Excel’s Solver do the heavy math!

HOW DO WE SOLVE THIS PUZZLE?  I’m 7 times as old as Lisa is now. In 20 years Lisa will be just half the age I will be then. How old is Lisa now?

### The Puzzle I saw this puzzle recently. I had to really concentrate…I solved it by substituting numbers.

In the business world these puzzles are more challenging. What can we do?

• Write an Algebra equation?
• Call a Data Scientist?

The secret is using Excel’s Solver Add-In!

### What Are We Solving?

What we’re solving is:  How old is Lisa now?

Get the Excel file here.

### What Do We Know?

Let’s start with the facts. We don’t know Lisa’s age yet but we do know:

• Dad is 7 times as old as Lisa is now
• In 20 years Lisa will be half dad’s age.

For now we’ll simply type in a 7 and a 20 as seen below. I put 9 in cell L12 so the formulas would populate.

Cells L17, L18 and L19 have these formulas:

• =IF(L12=””,””,L12*L14)
• =IF(L12=””,””,L12+L15)
• =IF(L12=””,””,L17+L15) ### Solver Magic

Look at red cell L21. It should be a 2 (Dad’s future age double Lisa’s future age).

Yes we can adjust Lisa’s current age in cell L12 but in a more complex case this could take hours!

Let’s use Solver! (Never used solver? See solver tutorial link below)

Solver set-up:

• cell L21 formula result must be a 2 (Dad’s age double Lisa’s age)
• change Lisa’s current age (cell L12) until cell L21 = 2
• cell L12 must be an integer (whole number) ### Solver Results!

Solver has solved the puzzle for us! Press “OK” to accept the solution! So….Lisa’s current age is 4 ! ### Solver Tutorial !

Solver is one of Excel’s secret weapons!

Here is an excellent solver tutorial from Svetlana Cheusheva at ablebits.com How to use Solver in Excel with examples 