Formula and decimal places

Posted by: miguel78 on 8 September 2017, 2:19 pm EST

  • Posted 8 September 2017, 2:19 pm EST

    I have a problem with decimals in a cell formula.

    A column of the spread is calculated using the following formula: (FC6*FC8)-((FC6*FC8)*(FC9/100))-(((FC6*FC8)-((FC6*FC8)*(FC9/100)))*(FC10/100))
    If the cell values are as follows:
    c6 = 5
    C8 = 2.79
    c9 = 25
    C10 = 0

    The result of the formula is 10.4625
    In the column that displays only two decimal places so the value is 10.46
    I have eight rows where the value is always the same and in the ninth row do the sum by a new formula: SUM(R1C:R8C)
    I'm hoping that the result is 83.68 but the formula returns me 83.7
    How I can eliminate from the calculation that I am not showing decimal places?

    I am using version 4.0.3509.2008.

    Thanks for your help.

  • Replied 8 September 2017, 2:19 pm EST


    Formulas use the full precision of the values stored in the cells, regardless of whether or not the full precision is displayed.  In your example, each cell in R1C:R8C has the 10.4625 value.  Thus, the formula SUM(R1C:R8C) evaluates to the 83.7 value.  If you want SUM(R1C:R8C) to evaluare to 83.68 then you would need to calculate the values in R1C:R8C with the same precision as displayed.  You can use the ROUND function in formulas.  For example, replace the formula...




  • Replied 8 September 2017, 2:19 pm EST

    I changed the formula and now it worked as I want.
    Thanks for the help.
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels