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

    miguel78,


    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...


       (FC6*FC8)-((FC6*FC8)*(FC9/100))-(((FC6*FC8)-((FC6*FC8)*(FC9/100)))*(FC10/100))


    with...


       ROUND((FC6*FC8)-((FC6*FC8)*(FC9/100))-(((FC6*FC8)-((FC6*FC8)*(FC9/100)))*(FC10/100)),2)

  • 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