## Formula and decimal places

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

• Post Options:

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.

• Post Options:

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)

• Post Options:

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

• #### ComponentOne

Forums for all current editions of the ComponentOne .NET UI control product line, including ComponentOne Studio and ComponentOne Studio for Xamarin.

• #### ActiveReports

Forums for all versions of ActiveReports Developer and Server