Excel Challenge 40: Create a Custom Excel Calculator

This practice exercise is extra-special because it was suggested by one of our community members, Avery Dace!

Creating your own calculator in Excel is a useful skill because you might perform certain operations fairly often and would appreciate a way to just plug the numbers in and arrive at the answer quickly. That's what we'll be doing in this Excel challenge.

Everything you need to participate can be found below. To take part:

• First, watch the challenge video and read the instructions below the video.
• Review the previously published video(s) and article(s) on which the challenge is based.
• Download the Excel worksheet you will use to complete the challenge tasks.
• Put yourself to the test!

Want to chat about your approach and process with other Excel fanatics? Join our Slack channel to share your insights and questions with learners like you.

The challenge 🤺

Here’s the scenario to be solved from the download file:

The goal here is to create an interactive calculator that calculates the annual insurance premium for the requested property based on the building specifications. On the Calculator sheet, the Input table allows you to enter the building value and make selections from the dropdown lists. The calculator on the right populates the rating factors based on selections made from the Input table.

On the Factors sheet, there are two tables — one for building age and construction (the Building table), and the other tells us how factors like the age of the plumbing, heating, roofing, etc. will affect the rate quoted. This is called the Finishing table.

All these factors will come together to determine the annual premium for the requested property. 

Tasks🏋️

To successfully complete this challenge, you will need to:

1. Restrict the entries that users can make in cells B3:B9 to only the relevant values in the Finishing table.

2. Create formulas in cells E4:E9 that will extract the correct rating based on the factors selected in cells B3:B9.

3. Multiply the building value by each fraction, and use the total to determine the annual premium in cell E11. 

Hints😉

To get you started, we'll share some of our previously published resources that you might find helpful in deciding how to tackle this problem.

And pick up a trick or two from this article on handling lookups for multiple criteria in Excel.

If you’re just getting started with Excel, most of the knowledge you need to solve this challenge has been shown in the above videos. If not, explore our GoSkills Excel Resource hub for more Excel tips and tricks.

For those of you already proficient in Excel, this should be fairly easy. Maybe you can tell us a creative way to solve this problem in our Slack channel.

Have fun!

The reveal ✨

We hope you enjoyed taking part in this challenge! Check out our range of expert-led Excel courses for all skill levels to further sharpen your skills.