Scattered Productivity (also, conditional formatting example)

Don't you just hate it when you have periods where you are just unproductive? The last two weeks have been like that with the two conventions, so I'm hoping that with them over, I can get more things done. At work, I'm doing various Excel work: using Power Query on another set of downloads (still unsuccessful - not because of Power Query); creating conditional formatting based on timing (finally succeeded); and creating a quick graphic that supposed to give you information in one quick glance (still unsuccessful).

Yep, this past week was unsuccessful.

The conditional formatting puzzle was interesting: what we wanted to do was have color show up based on the time of the month. Each week we report the delinquency as a percentage of what was collected. Originally, we were highlighting cells in orange when the delinquency was over 10% of that collected. The problem is that at the beginning of the month, the delinquency percentage will be very high because we just invoiced and thus was just beginning to collect the money. So showing something over 10% at the beginning of the month was meaningless.

But at the other end of the time spectrum, we were not highlighting those delinquencies that were over 1% of the collections (not just 10%) by the end of the month; we should have collected all of the money by the end of the month. We weren't highlighting enough "out of bounds" delinquency.

So my boss had this brilliant idea of having the delinquency limit get stricter the closer we moved toward the end of the month. I added in the gradations of color from light red to dark red to differentiate which units had severely large delinquency percentage and which were just slightly over our limit.

It actually took a while for me to get the conditional formatting to work.

The big difficulty I have with conditional formatting is that I can't see the entire formula on the screen. Maneuvering around the formula in that little conditional formatting edit box is problematic, to say the least. Maybe there's a trick that I don't know. Anyway, I finally decided to set up the formulas right on the spreadsheet itself rather than in the conditional formatting formula box; that way, I can fix the formula to make it work. When I have it working, then I copy that formula and paste it into the conditional formatting formula box. Another advantage to setting up the formula first on the spreadsheet is that you can copy it and re-use it for the next format. It's just easier to fix your formula when you can see it as a whole.

[ap_divider color="#CCCCCC" style="solid" thickness="5px" width="100%" mar_top="20px" mar_bot="20px"]

Here's an example of varying the colors according to the level of delinquency percentage.

See how the 10% is in really strong red whereas anything less than 3% is in lighter red?

(Note: I don't know why the 10% number is so blurred; this is the second time I used the snipping tool and the tool still blurs this number)

[ap_divider color="#CCCCCC" style="solid" thickness="5px" width="100%" mar_top="20px" mar_bot="20px"]

The image to the left is a table of rules that I set to be flexible so that my boss could change the levels to what she wanted. The yellow bar running horizontally across denotes which rules are being used. Since the date of the report was the 24th, the second to the last row was highlighted. Between the 16th and 25th of the month, any delinquency greater than or equal to 1% would be highlighted in light red, anything greater than or equal to 5% would be in medium red and anything greater or equal 10% would be in dark red.

[ap_divider color="#CCCCCC" style="solid" thickness="5px" width="100%" mar_top="20px" mar_bot="20px"]

To the left is a sample formula for conditional formatting rules for dark red highlights found under column AS.

The formula is broken down as follows: $A$1 refers to the date of the report so we can apply the appropriate set of rules based on date; $AO is the from date and $AN is the to date; $AH is the report's delinquency % and is compared against the delinquency limit $AS.  Since there are four sets of dates with delinquency % levels (dates 6 - 10, dates 11 -15, dates 16 - 25, and dates greater than 25), we use OR to choose which of the 4 rules for dark red to use. The date of the report determines which of the four rules apply and then we compare the report's delinquency percentage against the rule for dark red.