Skip to content

Email Newsletter Challenge 2

This week’s challenge is all about testing your knowledge of formulas and cell references. After all, it’s very important to have a good understanding of these types of fundamentals to further your spreadsheet knowledge.

As always, every Spreadsheet Life Newsletter challenge is designed for both Excel and Google Sheets. You can download the spreadsheet files using the links below.

The Challenge

This week, your task is to write one formula in cell D5 that can be copied to calculate the discounted price for each combination.

Think you can solve this one? Download the spreadsheet file using the link above and give it a try.

The Solution

The formula for this one isn’t too complicated. You just need to multiply the original price by the discount rate subtracted from one.

= OriginalPrice * ( 1 - DiscountRate )

The tricky part is getting the cell references right. If you only use relative references (with no dollar signs) then you will not be able to copy the formula in cell D5 to the other cells to get the rest of the results.

You actually need to use mixed references to complete this challenge.

= $C5 * ( 1 - D$4 )

You include a dollar sign in front of the column letter for the C5 reference (original price) because all of the prices are in column C but in different rows. So you want the column letter to stay the same and the row number to change.

As for the D4 reference (discount rate), all of these values are in row 4 but in different columns. So you place a dollars sign in front of the row number to keep it from changing, and you leave off the dollar sign for the column letter so it will change.

Once you have your references right, you can successfully copy the formula in cell D5 to populate the rest of the table.

And that sums up the solution for your Spreadsheet Life Newsletter challenge for this week!

Also, if you want to learn more about cell references and really get the hang of writing formulas in Excel, I highly encourage you to sign up for the Excel Launchpad course. This is the best course you can take for nailing the fundamentals of Excel and getting this amazing skill onto your resumé.

And, if you’re not signed up for my weekly email newsletter, you can sign up right here!

This Post Has 2 Comments

  1. This is such a great challenge! And your explanation is super helpful as always! I love this new weekly challenge news letter, it really is something I look forward to each week now!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top