Mynda Treacy’s recent blogpost at MyOnlineTrainingHub is a hot one! Excel Custom Validation to Limit Entries poses a fun and excellent solution that Santa probably deals with every year:
How does Santa set up Excel to allow a kid to request no more than 3 presents for Christmas?
Mynda creates a solution that takes us into underused territory in Excel: Custom Data Validation. The blogpost goes further and shows a business-related example for Custom Data Validation.
Check out the blogpost: Excel Custom Validation to Limit Entries.
LET’S USE EXCEL TO MAKE CHRISTMAS EVEN MORE TECHIE
That blogpost lit me up and I was ready to jump on the “get techie for Christmas” theme. The ideas started churning.
What if Parents Could Quantify their children’s behavior over the year and determine if the child is deserving of a visit from Santa or Krampus?
WHO IS KRAMPUS?
He’s the anti-Santa. Krampus smells bad, he’s got horns and a tongue that is always hanging out of his mouth. On Christmas, Krampus shows up to capture naughty children and carry them back to his lair where he beats them with birch switches, pulls their ears and generally pesters the kids for having been so bad during the year.
To help Mynda bring Excel to Christmas, I present the Naughty-Nice-Ometer. Download it to see all of the formulas, named ranges and other details.
Download the Naughty-Nice-Ometer
NAUGHTY-NICE-OMETER
The Problem
Children have no clarity around what’s naughty or nice and how actions are weighted. Parents don’t have a clear and consistent scoring system. The result is a lot of undeserving kids from Siberia to Sydney get to participate in Christmas because the parents err in favor nice instead of naughty. This teaches nice kids that there is no justice in this world. Naughty kids learn that they can give in to their passions and the result is the same as if they’d shown some restraint and decency.
Parents are left mired in shame and helplessness because they had no way of measuring and documenting what they knew had been a naughty year for one or more of their children.
The Solution
Kyle and James are brothers. Their parents have a list of actions that are labeled naughty or nice, and the actions are assigned points.
Scores below 0 = Krampus for Christmas
Scores equal to or above 0 = Santa for Christmas
NAUGHTY-NICE-OMETER DETAILS
CLICK ON ANY IMAGE BELOW TO SEE FULL-SIZE IN A DIFFERENT WINDOW
The Scoring worksheet uses an Excel table for storing the details. Parents would fill out this table as they determine actions that need to be scored as naughty or nice.
Download the Naughty-Nice-Ometer
The Main Section has multiple components
1
The pictures change based on each boy’s score. The video shows the detail of how to get the picture to change based on the value in a cell.
2
The text in the rectangles change to reflect the number of days until Christmas and if the child is headed for Krampus or Santa. The formula in James’ text box is =$S$9 because we can’t build the full statement in a shape. We have to build the statement in a cell and then link the shape to that cell.
3
The scoring happens in a table and includes dropdown lists in the Activity column.
The spreadsheet has frozen cells so the parents can scroll down to the end of the list and keep the headers visible.
Cell U4 contains the number of days left until Christmas, the result of 25DEC14 minus TODAY.
4
Numerical and visual representation of the scores broken down by child and by Naughty/Nice.
The Guts of the Naughty-Nice-Ometer
5
Columns Q:X (i.e., the guts) would be hidden during normal use. They support everything that’s visible, columns A:O.
- T8 and T9 choose the picture from the “sork” worksheet (‘sork’ stands for Santa or Krampus)
- U3 and U4 calculate the final scores for each boy
- R3 and R4 calculate Santa or Krampus based on the scores in U3 and U4
- U8 is just the date of Christmas
- U9 calculates the number of days between today and Christmas
- R8 and R9 generate the text that go into the text boxes, using the results from U9, R3 and R4
Here are the formulas inside the guts:
CONCLUSION
There’s a lot going on in the Naughty-Nice-Ometer. It’s an example of building an app and thinking through the pieces that would be needed for an effective tool. It started with the idea of keeping points and having the right picture show up based on the points tally. But more is needed so, we layer and weave Excel features together to develop a tool that’s useful, transparent and fully integrated.
I know that I don’t go deep into the details of each piece. So, please do ask any questions. And please share your thoughts about Excel and the Naughty-Nice-Ometer.
Ho Ho Ho!
Download the Naughty-Nice-Ometer
Krampus image courtesy of Hungry Sparrow
Santa with pipe by Nyrek
Trackbacks/Pingbacks