Complex problems require complex solutions, right? Not
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:
F2: input value
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
- 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
- Click inside the data
- Click the Insert tab.
- Select Table.
- In the resulting dialog,
click OK. Be sure to adjust the header question to accommodate your data
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
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("*",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:
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)
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)
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)
This formula parses the last four characters from the
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.
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”