Forum Discussion

CraigCWIS's avatar
CraigCWIS
Copper Contributor
Jan 24, 2024

TRUNC function

I have created a payroll spreadsheet. However, there are rounding errors throughout the spreadsheet due to adding and/or subtracting within columns that have been rounded past two decimal places. This happens despite limiting all cells to two decimal places. Does anyone know if there is a way to apply the TRUNC function to the entire sheet (instead of creating additional cells to accomplish the TRUNC function)?

2 Replies

  • JoeUser2004's avatar
    JoeUser2004
    Bronze Contributor

    CraigCWIS  wrote:  ``is [there] a way to apply the TRUNC function to the entire sheet``

     

    First, if you want to match what you see when you format to display 2 decimal places, you should use ROUND, not TRUNC.

     

    If you agree that ROUND is what you mean, you might set the option "Precision as displayed" (PAD).

     

    Caveat:  Be sure to make a copy of the file before setting that option.  When you set PAD, it might change some constants irreversibly.

     

    Normally, I do not recommend (in fact, I discourage) the use of PAD.  The caveat above is just one reason.  Another reason is:  PAD applies to all cells in all worksheets indiscriminately.

     

    Even though you say that is what you want,  you might discover it really isn't.

     

    Moreover, PAD applies only to the final cell value.  It does not apply to expressions within formulas. 

     

    Consequently, you might stumble over differences; for example, between =A1+A2 in one cell, and =IF(A1+A2 = A3,...) in another cell.

     

    (The latter must be changed to =IF(ROUND(A1+A2, 2) = A3,...).)

  • mathetes's avatar
    mathetes
    Silver Contributor

    CraigCWIS 

    I have created a payroll spreadsheet. However, there are rounding errors throughout the spreadsheet due to adding and/or subtracting within columns that have been rounded past two decimal places. This happens despite limiting all cells to two decimal places. Does anyone know if there is a way to apply the TRUNC function to the entire sheet (instead of creating additional cells to accomplish the TRUNC function)?

     

    Craig -- it's not altogether clear what's happening. Because you refer to the TRUNC function, asking whether it can be applied to the entire sheet (presumably some kind of "blanket" application, to which I think the answer is no, and it most likely would not be a good idea even if possible).....anyway, because of your reference to that function, it's not clear that you're actually rounding in limiting cells to two decimal places. TRUNC does not round; all it does is truncate.

     

    Could I ask that you post a copy of your spreadsheet without any actual employee data--use false names and also render anonymous any other details that might identify the company, etc. Post that copy on OneDrive or GoogleDrive and paste a link here that grants access. And point out the results that you are identifying as errors.

     

    For what it's worth, though I'm retired now, I did spend some years as the director of the HR/Payroll database for a major US corporation, so I have some experience in this area. 

     

     

     

Resources