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 missed days.
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 existing formulas:
- Column G: =SUM(B6:F6) sums the actual hours worked.
- 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 request:
- 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 hours worked).
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 simple solution.
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 about Office
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.
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.