Everyone agrees that keeping an Excel spreadsheet in the
works beyond its expiration date is a bad idea. Unfortunately, in practice,
starting from scratch isn’t always an option. Often, the key to success is perspective — a simple change in
strategy. The sheet shown in Figure A is a good example of how a change in
perspective can expose an easy solution. It’s a simplified version of a legacy
sheet that’s outgrown its original purpose.
A legacy sheet that’s outgrown its original purpose.
In use for 15 years, the original
workbook has been pushed to its limits. In this case, an existing business rule
has thrown a monkey wrench into a new request. Management wants absentee hours
broken down by full and partial days. Initially, this sheet’s caretaker asked
for a summing formula to handle this, with the following considerations:
- Existing formulas must remain as they are
because of dependencies.
- Workday hours are inconsistent: Monday through
Thursday, the workday is 8.75 hours. Friday is a 7.5 hour day. A full workweek
is 42.5 hours.
- Must maintain existing structure, but adding
columns is allowable.
- Blank cells, rather than the value 0, denote
Management’s request is reasonable, and it’s hard to imagine why
it might be problematic in the simple example you’re seeing. I need to beg your
indulgence a bit — you’re not seeing the dozens of rows of employee records and
the dozens of columns accommodating overtime, union dues, special rates,
insurance premiums, vacation hours, tax rates, and so on.
The original workbook is packed full of details. Many
companies purchase off-the-shelf software to do what this (complete) workbook
does. The downside of all this functionality is management; it can be difficult
to see the forest for the trees.
Before we start hammering out a solution, let’s review the
- Column G: =SUM(B6:F6) sums the actual hours
- Column H: =(42.5 -G6) returns the number of
absentee hours by subtracting the actual hours worked from the full workweek
value of 42.5.
- Column I: =COUNTBLANK(B6:F6) returns the number
of missed workdays.
Now, watch what happens when the same employee misses a full
day and a few hours from another
workday (Figure B). Technically, the values in column H are correct,
but it isn’t what management wants to see. The current formula is totaling the
hours; management wants the number of full and partial days missed calculated
separately. In other words, we need two new sums: blanks that equal 8.75 or
7.50 and values that are less than 8.75 and 7.50.
The current formula totals the hours.
The user spent some time trying to add and subtract hours
and days, but you could test several complex formulas only to throw your hands
up in the air in defeat. In this case, the key is to stop thinking in hour and
day components — that’s a train wreck. It isn’t impossible, but it’s harder than
necessary. We don’t have consistent values that we can sum. We have blanks, so
let’s use them. Applying a value to each blank, depending on the day of the week, is easier than trying to calculate what’s supposed to be, but isn’t. Figure C
shows the new solution, and it took the two new simple formulas to meet management’s
- Column J: =COUNTBLANK(B6:E6)*8.75+COUNTBLANK(F6)*7.5
- Column K: =(42.5-G6)-J6
The new solution.
The new formula in column J multiplies blanks found in a
Monday through Thursday cell by 8.75, a blank in the Friday cell by 7.5, and
then sums those products. The formula in column K subtracts the new value in
column J from the total number of hours worked, minus the value in column G (the
In this case, summing work hours wasn’t working, so we gave a
value to the blanks and summed those instead. A switch in strategy exposed a
Growth is good, and if you’re lucky, the simple workbook you
began with will accommodate that growth. Realistically, new requirements are
often difficult to add after the fact — “If
I’d known you were going to need…, I’d have….” Often, the culprit isn’t
bad design or anything so academic. Sometimes, the business simply outgrows the
workbook’s original purpose, and that’s a good problem to have! When this
happens and you can’t rebuild, rethink.
You can use the downloadable .xlsx and .xls files to
discover a different solution, but to make things interesting, try to maintain
the user’s limitations. Please share your solutions in the comments section below.
Send me your question
I answer readers’ questions when I can, but there’s no
guarantee. When contacting me, be as specific as possible: For instance, “Please troubleshoot my workbook and fix
what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. I’m not reimbursed by TechRepublic for my time or expertise, nor do I
ask for a fee from readers. You can contact me at firstname.lastname@example.org.