Partilhar via

Excel Conditional Formatting Against a Goal Value

  • Artigo

Today, for a metrics worksheet I'm doing, I had a conditional formatting problem with Excel.  I suspect this is a pretty common problem. 

I had a column of numbers showing the monthly results for a set of metrics (the rows) for this fiscal year, and I had an annual goal for each metric.  I summed up the values across the months that have passed in this FY (our FY starts in July).  I hid the columns for the months that haven't come yet.  Then I have an annual goal for each metric.

I want to use conditional formatting to show whether I am on track to meet the metric.  An Icon Set format should do nicely, but at first I was puzzled because it looked like I could only use an icon set against the value itself - but in fact, I wanted to compare the value to the goal.

I realized after playing around a bit that the trick is to add a column for the icon and use a formula in that column to compare the value so far against the goal, and then format that column with the icon set, chosing "Show Icon Only" in the conditional formatting dialog.  What I end up with is something like this:

Because the value in the Total column (I'm not dealing with conditional formatting for the metrics where I'm showing averages) is only for a part of the FY, but the total goal is for the entire year, I need to scale the comparison to say whether we are on track or not.  This involves a bit of date arithmetic to figure out how far into the year we are.  Here's the formula; this complex parenthesized expression is basically figuring out how far we are into the FY, as a fraction from 0 to 1 (i.e. in the first month, we will be at 1/12).  Note that I am using TODAY() to get today's date; if instead, you wanted to make this be a specific date, i.e. the end of the month, you could create another named cell into which you enter the date for which metrics are current and refer to that in this formula instead of TODAY().

 =(((YEAR(TODAY())-YEAR(FYStart))*12+(MONTH(TODAY())-MONTH(FYStart)+1)/12)

I created a workbook with two named cells, one with the starting date of the Fiscal Year (which I named FYStart) and below it a cell with this formula in it, which I named PercentOfYear.  I can then refer to those named cells in formulas elsewhere.  This lets me reuse this sheet next year by just changing the FY start date.

Now to figuring out how far off I am from the goal.  For each row, column "N" is the value so far, i.e. the total of the values for all the months that have passed in this FY.  The "O" column will contain the icon showing on track or not.  The "P" column has the goal.  The formula for the icon column (O) is:

=(N4-PercentOfYear*P4)/(PercentOfYear*P4)

This scales the entire FY goal to what it should be this month if we are on track (this assumes the metric increases linearly each month).  We then subtract the scaled goal from the actual value, which gives us a positive value if we are ahead of the FY goal at this point, a negative number if we are below the goal at this point, and zero if we are right on target for the goal.  We then scale how far we are off from the goal by the scaled goal - so for instance, if the metric value is "2" and the goal is "5" and we are in December, we will should be at 2.5; because we are at 2, we are off by .5 or 20% of the scaled goal.

Finally, we apply conditional formatting to the cells using a custom rule.  That looks like this:

This applies a green icon if the value is zero or positive - i.e. we are on track for the goal; yellow if the value is not less than 25% below the goal at this point; red if we are further below the goal than that.

Comments