Excel Same Number Different Value

[varunwalla]

I am facing a strange issue the sum of value from G2 to L2 is 2.69 where as the sum of value from b4 to b9 is 2.68 but the correct value should be 2.68

refer the table below

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	No	Name	Qty	BP	SP	TO	BRK	ST	EXC	GT	SEBC	SD	Tot
2	1	Arun	20	103.30	103.55	4137.00	1.24	1.00	0.13	0.25	0.00	0.06	2.69
3
4	BRK	1.24
5	ST	1.00
6	EXC	0.13
7	GT	0.25
8	SEBC	0
9	SD	0.06	2.68
Sheet1

Cell Formulas
Range	Formula
F2	F2	=((C2*D2)+(C2*E2))
G2	G2	=MIN((F2*0.0003),40)
I2	I2	=F2*0.0000325
J2	J2	=0.18*(G2+I2)
K2	K2	=(F2*5)/10000000
L2	L2	=0.00003*(C2*D2)
M2	M2	=SUM(G2:L2)
C9	C9	=SUM(B4:B9)

you can try manual calculation of the number in the calculator app or online calc to verify

 

[Joe4]

Just because you only format your calculations to 2 decimals places doesn't mean that is what is stored in those columns. Regardless of however you choose to display them, Excel stores the full number (with all the decimals) and uses those in the calculations.

If you want it to add up just what is shown, use the ROUND function on each calculation to drop the remaining decimal portion.
For example, G2 should become:
=ROUND(MIN((F2*0.0003),40),2)

If you repeat this for all the others calculations, then your SUM will work the way you want it to.

 

[Joe4]

Alternatively, you can use a feature known as "Precision as Displayed", which tells Excel to only uses whatever values are being shown.
I seldom use this, for the reasons mentioned in the "Note" box here: Set rounding precision

 

[sykes]

I think it's a rounding issue - your cell formatting is rounding some of the figures.

If you see the ACTUAL figures in your cells, the correct sum for M2, is 2.6872 - so your formatting is rounding this to 2.69.

test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	No	Name	Qty	BP	SP	TO	BRK	ST	EXC	GT	SEBC	SD	Tot
2	1	Arun	20	103.3	103.55	4137	1.2411	1	0.134453	0.247599	0.002069	0.06198	2.6872
3
4	BRK	1.24
5	ST	1
6	EXC	0.13
7	GT	0.25
8	SEBC	0
9	SD	0.06	2.68
Spare2

Cell Formulas
Range	Formula
F2	F2	=((C2*D2)+(C2*E2))
G2	G2	=MIN((F2*0.0003),40)
I2	I2	=F2*0.0000325
J2	J2	=0.18*(G2+I2)
K2	K2	=(F2*5)/10000000
L2	L2	=0.00003*(C2*D2)
M2	M2	=SUM(G2:L2)
C9	C9	=SUM(B4:B9)