Filling in the blanks - Interesting $50 Project

Recently I completed a $50 Project for a client who had what could be a fairly common data problem -- missing data points.

This client has large tables of data over a series of dates, but for some reason he's missing data points for some of the dates. To fill them in, his plan was to simply average the values of the four prior dates. In cases where the blanks occurred within the first four dates' data points, he was just going to put zeros.

The formulas needed to do this were simple, but there was a problem: His data table was very large. It would take him a long time to copy-paste that formula over and over.

"Isn't there a way to use to Excel's replace function to find blanks and replace them with these formulas?" my client asked. "Oh, and it'd be nice if when blanks were replaced, the font color was changed, so I can see which points are averages and which ones are authentic data values."

Here's kinda how my thought process went as I tried to solve this problem:

Hmmmm.

Well, the replace function idea wasn't really a go, because even though you can use it to replace blank cells with formulas (simply leave the "find what" field blank, put the formula -- e.g. "=1+4" -- in the "replace with" field and select the "match entire cell contents" option) there's no way to specify which cells to reference in the formulas. Plus the replace function, by itself, couldn't change the font color. You could use conditional formatting and change the font color if the cell contains a formula, but wait...there's no built in Excel formula to test if a cell contains a formula...and you can't just look to see if the leftmost character in the cell is a "=", since that evaluates the value in the cell (the result of the formula) not the text of the formula. I could have just wrote a custom function in VBA -- say, called ISFORMULA -- to evaluate whether or not the cell contains a formula. That VBA might have looked something like this:

Function ISFORMULA(CELL_REF as Range)

If Left(CEL_REF.Formula, 1) = "=" Then

     ISFORMULA = True

Else

     ISFORMULA = False

End If

End Function

But none of that mattered because, again, the replace function path wasn't going to work, so I instead wrote a relatively simple macro that would allow the user to select the range of cells he or she wanted to check for blanks and replace them with either zeros (if they were in the first four date columns) or formulas to average the prior four date columns' values. Then if the blank was replaced with a zero the font would be changed to green, and if the blank was replaced with the average formula, the font would be changed to red.

In the end, the result was a solution that didn't make any material changes to what the client was already doing, but solved his problem with the click of a button.

Insiders Subscribers, your file's on the way. I also made some explanatory notes in the spreadsheet in a comment and put lots of explanatory comments in the code itself.

Enjoy.

Excel_Geek

Oh, and did anyone notice two shiny new features (one temporary, one permanent) on the blog?

1. An image and link on the left side promoting a charity fundraiser my wife and I are involve with in the near future, and
2. A Google search feature on the right side. Now you can easily look for those oldie but goodie posts from way way back...