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?
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:
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)
- 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 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
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!