The tutor shows how to use a spreadsheet (Excel) to compute a recursive deduction rate using a circular reference.

In yesterday’s post I brought up an example in which a deduction depends upon the income minus that same deduction. It’s a recursive situation: to calculate the deduction, it seems, you must already have subtracted it from the income; therefore, you must already know it. As I showed in yesterday’s post, algebra handles the calculation of the deduction nicely.

For those not keen on using algebra, there is a spreadsheet method, as well, to calculate the deduction. Let’s revisit the problem, then solve it the spreadsheet way:

An employer offers a health plan that costs 6% of net income. The company’s definition of net income is gross pay minus the health plan deduction. Find the monthly health premium of an employee whose gross monthly pay is 2500.

Solution:

On a spreadsheet, we solve the problem by naming a few cells, then defining a couple of formulas. Of course, you can use whichever cells you want. Let’s imagine you start with cell B6 and name it Income. To name it, just right-click while it’s selected, then click Name a Range… A text box will appear in which you can type the name, then click OK.

It’s easy to forget which cell you actually named. I highlight the ones I use by clicking the down arrow next to the Fill icon (which is a bucket of paint spilling, found with the font change icons). I click the shade I want, then the cell is highlighted that color.

Now I select another cell – let’s say it’s D6. I’ll name it Net_Income (names can’t have spaces), then type, inside it, the formula

=Income – Health_Premium

Next, I highlight that cell so I can see where it is.

Yet another cell needs to be selected. It will be named Health_Premium. Inside that cell will be typed the formula

=0.06*Net_Income

Of course, * means multiply.

Now, depending on how Excel is set up, a blue line might appear between the Net_Income and Health_Premium cells. In addition, you might see a pop-up: Circular Reference Warning. It explains that a circular reference is a formula that depends on its own result. If offers more information if you click OK; we do so.

In the ensuing write-up about circular references, the first option mentioned is to withdraw the circular reference. Down further, it tells how to enable the circular reference:

1. Click the top left button (the round one with the four coloured squares), then click Excel Options.
2. In the next menu that appears click Formulas.
3. To the right, under calculation options, click the box Enable Iterative Calculation. Then, at the bottom, click OK.

When you return to the spreadsheet, the blue line between the Net_Income and Health_Premium cells will be gone.

If you type the value 2500 into the Income cell, 2358.49 will appear in the Net_Income cell, and 141.51 in the Health_Premium cell. Those values are the same from yesterday’s post.

I think you’ll agree that the spreadsheet solution is appealing. However, the user needs to grasp the problem very well in order to define the cells and the formulas they must contain. It truly is a case of telling the computer the problem to solve, then having the computer solve it.

I’ll be talking more about spreadsheets in future posts:)

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.