One of my hobby horses for up and coming science students has long been a recommendation that they learn to use a "real" analysis package fairly early on. Something that supports fitting things other than lines, knows about error intervals, and the like. A common counter-comment is that spreadsheets can do an awful lot and the students already know them. I don't disagree with the basic premise of those comments. Indeed I used to be very happy to show students who weren't going into science how to use a spread sheet to perform their lab analysis.1 I also stand by my recommendation because the domain specific support in spreadsheets runs out with roughly the tools that are also used by business majors and they scale poorly as programming environments (even the ones that are Turing complete).
Despite knowing this I've just run right up against that limit.
I've mentioned my daughter's honorary Grandma before. She's a friend who lives with us. She has a neurodegenerative disease and can't take care of herself and my wife holds her powers of attorney (with me as the backup).2 Now the good news here is that Grandma has sufficient financial resources to pay for plenty of high quality care. She could afford a really good nursing facility, but when presented with the choice she opted to stay with us.3 Even when I had to move the family cross-country for a new job. So, that care takes the form of full time aides, and for more than a year and half it's been a private pay arrangement instead of using an agency. Long story, but the result is that my wife is in effect a healthcare manager, HR department, and payroll department. I pitch in as much as I can, but the main burden falls on her.
Right now payroll is a highly manual thing. Hours are recorded on paper and collated at the end of each pay period, then individual spreadsheets are worked up taking into account various categories of overtime and PTO.4 Now, my wife passed the programming courses that were required of her in her engineering education and puts up with my rattling on about trade-offs and choices I encounter in my work, but she doesn't naturally reach for programming to solve her own problems.
I, however, do, and when I learned of the state of the payroll process last month I quickly banged out a spreadsheet template which I believe supports all our policies and can be printed as a clear and concise description of what is being payed how and why.
And we're not using it. Because it is untested.
Can I write a little suite of unit tests for a spreadsheet? If so, how?5
Can I, at least, create a few example cases that exercise all the parts? I suspect that this is my best bet.
Other options?
1 Though they mostly turned to their peers for this advice. Especially after the first night. Fine with me, of course, gave me more time for other things and once they start learning from one another the idea spreads.
2 There is a lawyer in my family, and they are as pedantic a bunch as physical scientists and computer nerds, so I've had it pounded into my little brain that the power of attorney is a document and a person exercising such a document is, contrary to popular usage, an attorney in fact. You didn't need to know that, but I needed to spread the pain.
3 She came to be living with us in the first place because we thought we were only hosting her through a period of rehab and recovery after a hospitalization and before we got the scary diagnosis.
4 Because Grandma can afford it and to hold good staff we offer higher wages and better benefits than are normal in this woefully underpaid line of work. More than half our carers are CNAs and the rest are well on their way to being skilled up to it. They are all tasked with skilled, often unpleasant, occassional physically demanding, and very responsible work. Now in a private care situation like at our house they get a fair amount of downtime, but they also have to do without partners for moving the patient and the like. Yet somehow PTO isn't a thing in the industry.
5 It's a Google doc, on the off chance that someone knows the answer for select programs.
