28 Sep 2018

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

 

 

 

 

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!

Post a comment