# Excel Same Number Different Value

#### varunwalla

##### New Member
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
ABCDEFGHIJKLMN
1NoNameQtyBPSPTOBRKSTEXCGTSEBCSDTot
21Arun20103.30103.554137.001.241.000.130.250.000.062.69
3
4BRK1.24
5ST1.00
6EXC0.13
7GT0.25
8SEBC0
9SD0.062.68
Sheet1
Cell Formulas
RangeFormula
F2F2=((C2*D2)+(C2*E2))
G2G2=MIN((F2*0.0003),40)
I2I2=F2*0.0000325
J2J2=0.18*(G2+I2)
K2K2=(F2*5)/10000000
L2L2=0.00003*(C2*D2)
M2M2=SUM(G2:L2)
C9C9=SUM(B4:B9)

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

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

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

##### Well-known Member
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
ABCDEFGHIJKLM
1NoNameQtyBPSPTOBRKSTEXCGTSEBCSDTot
21Arun20103.3103.5541371.241110.1344530.2475990.0020690.061982.6872
3
4BRK1.24
5ST1
6EXC0.13
7GT0.25
8SEBC0
9SD0.062.68
Spare2
Cell Formulas
RangeFormula
F2F2=((C2*D2)+(C2*E2))
G2G2=MIN((F2*0.0003),40)
I2I2=F2*0.0000325
J2J2=0.18*(G2+I2)
K2K2=(F2*5)/10000000
L2L2=0.00003*(C2*D2)
M2M2=SUM(G2:L2)
C9C9=SUM(B4:B9)

Replies
11
Views
160
Replies
5
Views
204
Replies
3
Views
153
Replies
3
Views
176
Replies
0
Views
182

1,126,939
Messages
5,621,720
Members
415,853
Latest member
Newlife72

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back