Excel Same Number Different Value

varunwalla

New Member
Joined
Aug 13, 2020
Messages
32
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
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
 

Some videos you may like

Excel Facts

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top