When dealing with a small but complex set of calculations, it is very tempting for many of us to put long, complex formulas in a few cells. Or sometimes, as Excel experts, we would be asked to find calculation errors in such situations. Of course, who wouldn’t like to have neat-looking workbooks? But there is just one plain and simple problem with it… debugging!
We know how much unnerving it could be, especially if it’s someone else mess that we are trying to clean. This is exactly why Oz du Soleil decided to help us out.
Let’s see what he has in the bag for us.
1 – The Excel Problem
Let’s consider the problem of allocating cost of using a condo to a number of people given the monthly rent for it. We know who was in the condo, with whom and at what dates. The only caveat is that when the condo is shared by more people on a specific date, the cost per person should be lower. For simplification, we will consider only one month as the horizon of the problem.
Note that Oz was actually given the task of solving this question as a challenge.
2 – The Problem With Complicated Formulas
The problem with the Excel file Oz received was that the entire calculation for the amount owed by a specific person was in a single cell! That’s right, just one cell. Which meant that not only was the formula too long, its accuracy was also under doubt.
3 – Using Helper Columns In Excel
Creating intermediate steps or, what Oz calls them, “helper columns”.
1st Step: calculate the ‘per day’ rent of the condo. This is easy and equals monthly rent divided by the days in the month in consideration.
2nd Step: use the number of people sharing the condo on a specific date to calculate the rate applicable to them for that day. This also is very easy! Just divide the daily rental rate of the condo by the number of people sharing the condo that day. Now do the same for all the days in the month.
3rd Step: for each person, sum up the daily rates applicable whenever he or she was in the condo. A simply SUMIF function should achieve this.
And we’re done with all that was to calculate. Never be afraid of creating a mess. You can always clean it later.
4 –Spreadsheet Accuracy Checks
Accuracy check on calculations is as important as the calculations themselves. This is the key difference between a good data analyst and a great one. So, let’s get into it.
We were calculating allocation of one month’s rent over people. So, when we have calculated how much each person owes for the rent, the sum must add up to this one month rent. Also, summing the daily total rent of the room should also give us the same number.
That’s it. It’s time to share our workbook.
What’s next?
You now know what to do to debug small to large sets of complex formulas! Just break them.
Also, have fun while you’re at it. And do not forget to share this tip with your friends and colleagues.
- SSSVEDA DAY 7 – Every Team Needs Someone Who Understands Data - February 18, 2018
- SSSVEDA DAY 5 – When Data Analysis is Wrong - October 31, 2017
- SSSVEDA DAY 4 – Sharing the Excel Knowledge - July 18, 2017
