Saturday, January 31, 2015

Intuition, Algebra, Inspection

I was asked to look at s spreadsheet showing product sales. There seemed to be something wrong: the discount on all products was 37%, the discount on list A was 38%, and the discount on everything else, list B, was 49%. Yet the individual calculations appeared to be correct.

Intuitively this seemed wrong, but I didn't want to trust to intuition, for I know that mine isn't always reliable. I took out a piece of paper and wrote out the inequalities
a/b > (a + c)/(b + d) < c/d
Multiplying through showed that this could be true only if the inequalities
ad > bc
bc > ad
were both true. So intuition was correct:, there was something wrong.After a recalculation, I spotted the problem: the list B calculation was off by a cell, dividing the amount of discount by the discounted price rather than the full price. Correcting that gave a list B discount of 33% and the calculations made sense. A couple of us had looked at this for quite a while without noticing the mistake.

I don't mind math, I use it regularly in my work, but this must be the first time in years that I have fallen back on algebra. With a better eye for the cell references in the discount formula, I would not have needed to do so. Still it is comforting to remember that the tool is available.

No comments:

Post a Comment