I often hear from users struggling to produce a single-formula solution. Even long-time developers struggle to build complex formulas. My advice is to break down the problem into steps and then build those steps into the sheet in the form of helper columns. Your solution will be stable and easy to maintain and enhance. In addition, eventually, someone always asks for one of those mid-step results. When that happens, you're prepared. Instead of "I can have that for you in a few days," you can respond with "I'll have that on your desk in five minutes." This month, I helped two readers with helper column solutions.
Angel needs to count birthdates that fall within a specific time period - five days before today, today, and five days after today. Below is the sample sheet I sent Angel.
Column A contains birthdates, and that's where we begin. First, we need to evaluate dates in the current year, not the birth year. Column B uses the following formula to return the same birthdate day and month, and the current year:
This DATE() function generates a new date using today's year and the birthdate's (column A) month and day.
The span is five days prior and five days after, including the current date - an eleven day span. You could hard-code that number into a formula, but I wouldn't. By referencing an input value, the sheet becomes much more flexible. As soon as you come up with a complex formula that hardcodes that value, someone will ask you to expand or shrink the span. To accommodate the span, I entered the following formulas:
F1: =TODAY() F2: input value F3: =F1-F2 F4: =F1+F2 F5: =COUNTIF(C2:C8,FALSE)
The first two are self-explanatory. The next two return a date five days in the past and five days into the future, respectively. The COUNTIF() isn't working just yet, but don't worry. Enter the following formula in C2 and copy it to C3:C8:
This formula uses OR() to check two conditions and return TRUE or FALSE, accordingly:
- Is the date less than the date in F3?
- Is the date greater than the date in F4?
Now the counting formula F5 is counting as expected. A COUNTIF() function counts the number of FALSE values in column C. (You could express the OR() differently and count TRUE values.)
There's still a limitation. You can't easily add records to the data range. If you're working with a stable dataset, that doesn't matter. To add new dates in 2007 or later, convert the data range into a Table object as follows:
- Click inside the data range.
- Click the Insert tab.
- Select Table.
- In the resulting dialog, click OK. Be sure to adjust the header question to accommodate your data range.
When you're ready to enter a new record, enter the birthdate into the first empty cell in column A and press Enter. Excel will copy the formulas in the data range and automatically update the counting formula in F5. Excel's Table object doesn't play well with all the other features (Subtotal comes to mind), so keep that in mind.
Tim has a complex parsing problem. His data is in one cell and he needs to parse the individual components into separate cells. To complicate the problem, the middle component may or may not contain additional spaces. Yikes!
Again, I relied on helper columns, as shown below. The data in column A contains previous Derby winners with their positions and winning years. The position and year are easier to parse than the horse's name because of additional spaces that sometimes occur in the name.
The first thing I did was determine the length of each string using the following formula in column B:
We'll refer to this value a couple of times. Next, I used the following formulas in columns C and D to locate the position of the first and last space characters, respectively:
=FIND(" ",A2) =FIND("*",SUBSTITUTE(A2," ","*",B2-LEN(SUBSTITUTE(A2," ",""))))
The first one is self-explanatory. The second returns the position of the last space by substituting the * character for all the other space characters - just for counting purposes. It doesn't actually change the data. (This long-standing formula is used in many parsing techniques.)
With these three values in place, you're ready to start parsing using the following three formulas:
Position: =TRIM(LEFT(A2,C2)) Name: =TRIM(MID(A2,C2+1,D2-C2)) Year: =TRIM(RIGHT(A2,B2-D2))
The TRIM() function removes any trailing space characters. To decipher the formulas, let's evaluate the first string using each formula, starting with Position:
LEFT(Original String,First Space) LEFT("1. Willing Colors 1998", 3) 1.
This formula parses the first three characters from the original string. The TRIM() function removes the space character. If you don't want to carry over the period, adjust the formula accordingly.
Now, let's parse the Name:
MID("1. Winning Colors 1998", 3 + 1, 18-3) MID("1. Winning Colors 1998", 4, 15) Winning Colors
The formula begins parsing at character 4 and parses the next 15 characters.
The Year formula evaluates as follows:
RIGHT("1. Winning Colors 1998", 22 - 18) RIGHT("1. Winning Colors 1998", 4) 1998
This formula parses the last four characters from the original string.
I'd like to mention that the Text To Column feature could quickly parse the first component using the period character (.) as the delimiter. Unfortunately, the wizard can't handle the other two components because of the extra space characters in the name.Helper columns In both cases, neither reader needed to see the helper columns, but eliminating them means combining all of those formulas into one - good luck with that! I can produce both sheets faster than I can build just one of those one-formula solutions. The results are easy to maintain, troubleshoot, and enhance. Perhaps more importantly, both readers will be ready for the "This is great, but can you also" requests.
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.