-A
Systematic Approach to Computer Problem-Solving:
Spreadsheets and
"Low-Nickel Rounding"
by Roy W. Johnson
Published in St. Louis
Computing
I am a teacher who specializes in computers, rather than a computer specialist who teaches. A lifetime of teaching develops patience, and as a result training institutions have asked me to teach a wide variety of courses to computer novices --"Introduction To (WordPerfect, Word, Ami Pro, Lotus, Quattro Pro, Excel, Pagemaker, Appleworks, DOS, Macintosh)" -- just fill in the blank. To do this, I was forced to develop a systematic approach to help me quickly solve an assortment of computer problems. This approach may be helpful to others, and I would like to share it with you in this column. I'll illustrate it with a specific problem a client asked me to solve.
Most often, I taught and did private consulting on electronic spreadsheets. My client asked me if Lotus 1-2-3 had a function to do "Low Nickel Rounding", in which the total charged to a customers would be rounded down to the next lowest nickel. $12.48, for example, would become $12.45. (I'll use small numbers for my example. In my client's spreadsheet, the total would have been much larger.) "Lotus doesn't have such a function," I replied, "but I believe I can write a formula that will do it." At that point I had no idea how. But I sat down at my computer and went to work.
First, to work out the logic. It occurred to me that if I could divide 12.48 by five, there would be a remainder of three cents. If I could find some way to knock off this remainder, then multiply by five, the result would be 12.45--the number I wanted. This is using the pre-computer math that I learned in school years ago. Computers, of course, do not recognize "remainders". Divide 12.45 by five and you get 2.496. Find some way to get rid of the 6, multiply by five, and you have 12.45, rounded to the next lowest nickel. This would work as well for any other number.
The @ROUND function would not work; it would round this number UP, and I needed to round all numbers DOWN. The @INT function knocks off all trailing decimals, so that wouldn't work either.
But wait! If I could divide by five then move the decimal two places
to the right, I would have 249.6. I could then use @INT to remove the decimal,
leaving 249. Then I could move the decimal to the left two places and
I would have 2.49. Multiply that by five and I would have 12.45--the result
I wanted! Step by step, here's the process:
Obviously, steps 1 and 2 can be combined. Why divide by five then multiply by 100, when you get the same result if you multiply by 20? The same is true in reverse of steps 4 and 5--just divide by 20. Let's assume cell B15 contains the sum of B3..B14 and cell C15 contains the formula to round that number (in our example, 12.48) to the next lowest nickel. Here is the formula in C15:
@INT(B15/20)*20
Viola! Any number that appears in B15 will instantly be rounded to the next lowest nickel in C15! My client had a large spreadsheet. All I had to do was to copy this formula to the cell to the right of each column total, and all totals would be rounded to the low nickel. Had he wanted the column totals themselves to round to the low nickel and not show the true total, I could have combined the formulas: @INT(@SUM(B3..B14)/20)*20. The same formula would work on any spreadsheet. Quattro Pro uses @INT() like Lotus 1-2-3. For Excel, the formula would be =INT(). The principle is the same.
A few simple rules can make this kind of spreadsheet logic work for
you.
Although this problem was specifically related to spreadsheets, the same approach of breaking a complex problem into simple steps can work for other kinds of programs as well.
Try it!