As we develop tools in Excel, it’s nice to be at a place where everything is automated. One change in a cell cascades through the spreadsheet and updates everything that’s impacted by the single change. However, there are times when we need to override a calculation or handle an exception. Example:

A company has discounts based on the number of items purchased in a single transaction.

WAIT! There’s no 10% discount in the grid. How did transaction A7692 end up with a 10% discount? A 12-item purchase gets 0% discount.

In real life, we’d research this and perhaps discover that an account manager okayed this one-time hook-up for a longtime customer. Now let’s look at the underlying formulas:

Uh oh! Someone went into cell E14 and typed in the value. And here is that person’s future:

WHAT’S THE BIG DEAL?

With the formulas corrupted the spreadsheet is no longer integrated. Suppose we’re considering eliminating discounts below 50 items and want to do analysis on previous transactions.

Notice that the 10% did not change but the 9% in the third transaction (40 QTY) did drop to 0%. So, any analysis is going to be inaccurate.

MAKING ROOM FOR OVERRIDES

One solution would be to insert an override column

In cell G11 the following formula is used, then copied down the column:

=IF(F11<>””,D11*(1-F11),D11*(1-E11))

The summary: If cell F11 is not empty, then use that value to calculate the discount; otherwise, use the calculated Discount in cell E11

THINK ABOUT IT!

We build tools and do calculations in Excel and weird stuff always comes up. One time I got an emergency call from someone who’s life has completely stopped because her company couldn’t find the cause of a mismatch between some critical calculations.

What I discovered was that deep in the bowels of a massive spreadsheet someone had negotiated a fixed bonus payment rather than a bonus tied to a calculation. That fixed bonus was typed into a cell and a year later no one remembered that the number had been typed in.

The override was buried and forgotten until it’s ghost rose up from Hell and tormented the entire payroll process.

So, let’s think about other areas where overrides are needed?

One example in Guerrilla Data Analysis 2nd Edition shows a way to handle name-tags when people want a tag that has something other than their first name:

The formula in D2:

=IF(C2<>””,C2,A2)

Other Overrides and Exceptions:

• Paid time off is calculated based on time with the company. Exception: 2 people who earned 3 additional days off.
• A program needs to be completed 45 days after starting, and the completion date is calculated based on the start date. Exception: someone who had health problems was given 15 additional days.
• An inventory report says what items are low and calculates the amount to order this week. Exception: 2 items that are being phased out and must not be re-ordered.

FINAL THOUGHTS: SPREADSHEET LAYOUT & DEVELOPMENT

The solution to these overrides and exceptions isn’t difficult. The problem is more in spreadsheet development and data integrity. Also, creating an override column and maintaining the calculated column can help you look back and see the difference between what would have been normal and what the override was. So, a lot of forethought is necessary; thinking ahead enough to see if a quick solution might create problems later. But even if no problems ever arise, the real issue is poor spreadsheet development, inconsistent formulas, and other sins that compound into a reward of eternal damnation.

I don’t want that to be you.