- BY Kevin Lehrbass
- POSTED IN Tips
- WITH 1 COMMENTS
- PERMALINK
- STANDARD POST TYPE

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**

**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**!

## 1 Comments