"

This afternoon I helped someone debug a financial spreadsheet. One of the reasons spreadsheets can be so frustrating to work with is that assumptions are hard to see. You have to click on cells one at a time to find formulas, then decode cell coordinates into their meanings.

The root problem turned out to be an assumption that sounds reasonable. Youre making two changes, one to a numerator and one to a denominator. The total change equals the sum of the results of each change separately. Except thats not so.

At this point, a mathematician would say Of course you cant split the effects like that. Its nonlinear. But its worth pursuing a little further. For one thing, it doesnt help a general audience to justsay its nonlinear. For another, its worth seeing when it is appropriate, at least approximately, to attribute the effects this way.

You start with a numerator and denominator, N / D , then change N to N + n and change D to D + d .The total change is then( N + n )/( D + d ) N / D .

The resultfrom only the change in the numerator is n / D . The resultfrom only the change in denominator is N /( D + d ) N / D .

The difference between the total change and the sum of the two partial changes is

nd / D ( D + d ).

The assumption that you can take the total change and attribute it to each change separately is wrong in general. But it is correct if n or d is zero, and it is approximately correct with nd is small. This can make the bug harder to find. It could also be useful when nd is indeed small and you dont need to be exact.

Also, if all the terms are positive, the discrepancy is negative, i.e. the total change is less than the sum of the partial changes. Said another way, allocating the change to each cause separatelyover-estimates the total change.

"



    9           5