True value vs displayed value

[Excelezz]

hello everyone
Hope all is doing great and i need your help please with this maddening matter
i have a cell contains a formula as follows noting j4=148,495,950.67
-IF (MOD (J4*0.00521)*10000,10)>=5, ROUND((INT ( (J4*0.00521)*1000)+1) / 1000,2), ROUND(J4*0.00521, ROUND(J4*0.00521,2))
The result is correct which is (773,663.90)
And another cell which is I10 that’s based on the above result which is as follows noting e9 is (773,663.90) and k16 is zero
-IFERROR (IF (MOD((IF (K16<>0,K16+E9,E9))*10000,10)>=5, ROUND((INT ((IF (K16<>0, K16+E9,E9)) *1000)+ 1)/1000, 2), ROUND (IF (K16<>0, K16+E9, E9), 2)),
"0")
The displayed value in I10 is 773,663.91 yet the true value in it is 773,663.90


Can someone help me with this i don't understand the reason

 

[MARK858]

Format the cell as Number with 15 decimal places. What do you see?

You might be experiencing the issue in the link below

Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps

Discusses that floating-point arithmetic may give inaccurate results in Excel.

learn.microsoft.com

 

[Excelezz]

B

Format the cell as Number with 15 decimal places. What do you see?

You might be experiencing the issue in the link below

Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps

Discusses that floating-point arithmetic may give inaccurate results in Excel.

learn.microsoft.com

I see zeros. the precision option i don’t quite understand it and seems to be dangerous to use isn’t there another solution!?

 

[MARK858]

and seems to be dangerous to use isn’t there another solution!?

What do you mean dangerous, it is a possible explanation not a solution, further down the article it recommends using Round if precision is the cause but as you are stating you are seeing zero's it is unlikely.

What exact values to 7 decimal places do you have in E9 and K16?

 

[Excelezz]

What do you mean dangerous, it is a possible explanation not a solution, further down the article it recommends using Round if precision is the cause but as you are stating you are seeing zero's it is unlikely.

What exact values to 7 decimal places do you have in E9 and K16?

Oh i see i thought you are refereeing to it as a solution. I am already using round the values for e9 and k16 is zeros as well but the result of j4*0.0521 is 773,663.9029907

 

[MARK858]

but the result of j4*0.0521 is 773,663.9029907

I asked for the values of E9 and K16 to 7 decimal spaces so that I can test the results in the formula that isn't working

 

[Excelezz]

I asked for the values of E9 and K16 to 7 decimal spaces so that I can test the results in the formula that isn't working

E9 is 773,663.900000000
K16 value is 0.000000000

 

[MARK858]

There is something strange with your result (or input) as I get the below in my Excel.
P.S. unrelated but why are you putting quotes around your last zero?

Book1
	E				I		K
9	773663.9000000
10					773663.9000000
11
12
13
14
15
16							0.0000000
Sheet2

Cell Formulas
Range	Formula
I10	I10	=IFERROR(IF(MOD((IF(K16<>0,K16+E9,E9))*10000,10)>=5,ROUND((INT((IF(K16<>0,K16+E9,E9))*1000)+1)/1000,2),ROUND(IF(K16<>0,K16+E9,E9),2)),"0")

 

[Excelezz]

There is something strange with your result (or input) as I get the below in my Excel.
P.S. unrelated but why are you putting quotes around your last zero?

Book1
	E				I		K
9	773663.9000000
10					773663.9000000
11
12
13
14
15
16							0.0000000
Sheet2

Cell Formulas
Range	Formula
I10	I10	=IFERROR(IF(MOD((IF(K16<>0,K16+E9,E9))*10000,10)>=5,ROUND((INT((IF(K16<>0,K16+E9,E9))*1000)+1)/1000,2),ROUND(IF(K16<>0,K16+E9,E9),2)),"0")

Its an if error not really for a specific reason for the zero

So could you figure out why the inner value is 90 but the displayed value is 91 ?

 

[MARK858]

So could you figure out why the inner value is 90 but the displayed value is 91 ?

Not with the figures that you gave me, even without the last round it is still giving me .90. Are the values in K16 an E9 the results of formula? Have you stepped through with formula - evaluate?

Can you post your calculations with XL2BB (including any cells E9 and K16 are getting info from)?

When you state "true value" is that something you are seeing in Excel or by your calculations?