Problem
hint: how to round off bid costs in MacNail construction
estimating software. Rounding to the nearest ten or hundred
dollars in an estimate
from newsletter #14
Diagnosis
User wants to round costs to the nearest $10 or $100.
Solution
Follow these steps to round to the nearest hundred dollars:
1. Choose Unprotect Document from the Options menu. Click in the labor column for the first cost row. Notice the formula
in the formula bar.
2. Click in the formula bar just after the equal sign, and type ROUND( from the keyboard, then click after the end of the
formula and type ,-2) from the keyboard. Type the Enter key.
3. Make a similar change to each formula in the Labor column. If you’re an Excel pro, copy and paste some of the text
to cut down on typing!
4. Once you’ve finished the labor column, copy and paste the new formulas to the material and subcontractor columns.
First select all the labor column cells in the Summation, then choose Copy from the Edit menu.
6. Choose Paste Special from the Edit menu. Choose ‘formulas’, then click OK. You use the Paste Special command
to avoid scrambling the borders and other formatting in the Summation.
7. Choose Protect Document from the Options menu, so you won’t accidentally damage any of the other formulas in the
estimating worksheet.
To round to the nearest ten dollars, use -1 instead of -2 at step 2 (this rounds off by one
digit to the left of the decimal
point instead of two).
To round to the nearest 25 dollars, you’ll have to use a slightly fancier formula at step 2,
since Excel can only round to whole digits with the ROUND() function. =ROUND((E161*fudge)*4,-2)/4 will round to the nearest
$25, while =ROUND((E161*fudge)*2,-2)/2
will round to the nearest $50.
Comments
Entered 1/10/96 by Dennis. Updated 11/18/2010.
|
