Permanently Turning On Set Precision As Displayed

Actually, some things, like payroll, NEED to be PAD, universally.

Bear in mind when I say that that I have fought overrounding since I was in school in the seventies. It was standard since the calculation devices were slide rules and paper and pen. Slide rules cannot have more than some depth, and got darned expensive (I just missed that era, by a year or two, but its practices still ruled the day), and if you think today's kids are feckless and lazy, I offer you the 1972 student when told he could use interim rounding rather than use longer interim values with pen and paper. Sadly, the teachers used it too and I had to argue a lot of answers when their WRONG answers varied from mine.

That said, the moment a result exists in payroll, it exists as such forever. There is absolutely no carrying through longer decimals because they might add a penny later. Or subtract one. The moment a calculation is done, it is fixed permanently in dollars and cents, two decimal places, no more, ever.

I still use ROUND in its several guises, for every step along the way. I used to use the GET.CELL function (option 53) from the Excel 4 macros, but when VBA came about and those function became "the old Excel 4 macros" I converted. Didn't know they'd last. It was made into a chore anyway, so I would've bitten that bullet anyway.

But once a payroll value is computed, it is no longer "an interim result": it is set in stone, and under NO circumstance I've ever seen (35 years) would you want to carry the extra digits forward.

Most anything else though, that I have worked with, not a problem. Earnings for an article from many discrete transactions, well, that'd be one like payroll, but USUALLY, that data comes permanently rounded to begin with. Usually. However, when it comes with discounting of various sorts, especially if one applies across several items for an exact money amount, applied, it can depend a lot on the situation, but usually needs permanently rounded after application.

Some items, like sales tax calculations, are required by some (all? definitely mine, rounding up from the fourth decimal place) states to be rounded up, no matter what. Then have to be kept that way because you pay the higher of "collected" or "calculated from applicable sales" and the former is always higher. Wouldn't match up if kept rounded as it'd just equal the latter. Given how Ohio stresses this, it seems likely they'd be chary about explanations that mentioned "you don't want to permanently round things" preferring to think you were trying to beat them out of some pennies. Then you'd be a nasty thief, stealing from, say, the WIC program, and that'd color your whole audit. So many things involving money seem to fit the "permanently round any defined result no matter how "interim" it will eventually be" criteria.

I agree that universally enabling "Set Precision As Displayed" is not a good idea. If it must be done, the Tip provides a good way as a template for NEW workbooks or on demand via a macro. But if you really want to do it for each workbook (including an OLD workbook) as it is opened, here is a procedure:

1. Open a new workbook, then press Alt+F11 to open Visual Basic Editor.
2. Select ThisWorkbook and press F7 to View Code.
3. Enter the following code (see http://www.cpearson.com/Excel/AppEvent.aspx):
Private WithEvents MyAppEvents As Application
Private Sub Workbook_Open()
Set MyAppEvents = Application
End Sub
Private Sub MyAppEvents_WorkbookOpen(ByVal Wb As Workbook)
If Not (Wb Is ThisWorkbook) Then Wb.PrecisionAsDisplayed = True
End Sub
4. Select VBAProject and press F4, then change the Name property from VBAProject to MyAppEvents.
5. Close the Visual Basic Editor.
6. Press F12 and pick Save as type: Excel Add-in (*.xlam), then File name: MyAppEvents. Before clicking Save, copy the folder location (default is like C:\Users\[Name]\AppData\Roaming\Microsoft\AddIns).
7. Close Excel, then reopen Excel.
8. Pick Options > Trust Center > Trust Center Settings… > Trusted Locations > Add new location…, then add the folder location copied from step #6.
9. Pick Options > Add-ins > Manage: Excel Add-ins > Go… > Browse…, then select MyAppEvents.xlam.

As long as the MyAppEvents add-in is enabled, every workbook that is opened will have PrecisionAsDisplayed set True. This will persist after the workbook is saved, even if the MyAppEvents add-in is subsequently disabled.