I was answering this question when I decided on a three part solution: John invested $4,000.00 for a year, part at 7% per annum and part at 9% per annum. The interest on the investment is $300.00. How much did John invest at each rate?

The answer is that he would invest $3,000 at 7% and $1,000 at 9%. The following screenshots illustrate my approach:

I set out the problem as logically as possible and then used Goal Seek to give me the answer … like this:

You can see the answer I got!

Now I created two Data Tables:

traditional

Dynamic Array driven table

I used conditional formatting to confirm/highlight my answers in this case.

Download my Excel file to work along with me:

31st July 2020