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.

 

 

 

 

 

 

Add Simple Formulas

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

 

 

 

 

https://www.ablebits.com/office-addins-blog/2016/06/22/how-to-use-solver-in-excel-with-examples/

 

 

About Me

I’m Kevin Lehrbass. I’m a Data Analyst from Markham Ontario. This was me in Montreal at a nice Polish restaurant called Stash Cafe.

Data tools have really evolved since I started working with data in 2001. Excel has DAX and M (also used in Power BI !) and just this week I heard about some amazing new Excel functions called Dynamic Arrays! Read Bill Jelen’s post Filter Excel Data with a Function  and get his free PDF!