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.
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)