Excel is not calculating the correct prices

[ckmoied01]

Hi, Excel is not calculating the prices correctly.

Dont get if this is due to data formatting or issues with excel formulas.

Any suggestion to fix please ?

Example from Row 1, 1270 * 217,88 should be 276.707,60, but excel calculates 276.713,24

Overall the entire sheet has the same problem.

Thanks
ckmoied01

 

[rabsofty]

show us your formula's

 

[ckmoied01]

Just simple multiplacation of the two columns

Also, somehow my sheet uses Userdefined format.
But in any case the value is 99,04 and multiplied with 1270 should be 125.780,80, but excel calculates 125.778,74 instead.

 

[felixstraube]

I'm pretty sure there are more digits than 2 after the coma in column I or there are digits after the coma in column F, or both.
Show us a screenshot of both cells with General formatting (or Standard in german)

Here you can see all the digits in every cell:

Book1
	F	G	H	I	J
2	1,270.00			99.04	125,780.80
3	1,270.00			99.0383779527559	125,778.74
4	1,269.9792003231			99.04	125,778.74
Sheet1

Cell Formulas
Range	Formula
J2	J2	=F2*I2
I3	I3	=J3/F3
F4	F4	=J4/I4

 

[rabsofty]

I'm not sure how the formats work in your language.
When I put 1.270 * 99.04 in excel i get 125,780.80

 

[ckmoied01]

I am just using German Version of Microsoft........but that should not matter............format is just format in English or German.

in my sheets excel is calculating wrong prices with simple A*B formula .........and getting no clue why

 

[felixstraube]

Can you share your file? uploading it to a cloud storage? and we'll have a look.
Or try XL2BB

you can download it here

 

[felixstraube]

did you try formatting all the cells to Standard? What do you see? I'm sure there are more digits than you see because of the current formatting.

 

[ckmoied01]

OK, i have just created MiniSheet............and have copied the values of the cells.............yes.........there are 13 decimal places after comma
In original sheet Column "I" values are coming from another sheet, but due to the formatting in source it is picking 13 digits for each value.

How do i fix this ?

sample sheet with wrong calculations.xlsx
	D	E	F	G		I	J	K	L
20	03.02							Actual Should be	Difference
21	03.02.010		1.270	Stk		217,88	276.713,24	276.707,60	-6
22	03.02.020		1.270	Stk		99,04	125.778,74	125.780,80	2
23	03.02.030		3.809	m		176,11	670.819,97	670.802,99	-17
24	03.02.040		14	Stk		1.377,58	19.286,07	19.286,12	0
25	03.02.050		3.809	m		11,01	41.926,25	41.937,09	11
26	03.02.060		5	Stk		8.385,25	41.926,25	41.926,25	0
27	Summe 03.02						1.176.450,52	1.176.440,85	-10
28
Tabelle1

Cell Formulas
Range	Formula
J21:J26	J21	=F21*I21
K21	K21	=1270*217.88
L21:L27	L21	=K21-J21
K22	K22	=1270*99.04
K23	K23	=3809*176.11
K24	K24	=14*1377.58
K25	K25	=3809*11.01
K26	K26	=5*8385.25
J27:K27	J27	=SUM(J21:J26)

 

[MARK858]

See column J

Book1
	D	E	F	G	H	I	J	K	L
20	3.02							Actual Should be	Difference
21	03.02.010		1270	Stk		217.8844	276707.6	276707.6	0
22	03.02.020		1270	Stk		99.03838	125780.8	125780.8	0
23	03.02.030		3809	m		176.1145	670802.99	670803	0
24	03.02.040		14	Stk		1377.577	19286.12	19286.12	0
25	03.02.050		3809	m		11.00715	41937.09	41937.09	0
26	03.02.060		5	Stk		8385.25	41926.25	41926.25	0
27	Summe 03.02						1176440.85	1176441	0
28
Sheet5

Cell Formulas
Range	Formula
J21:J26	J21	=ROUND(F21,2)*ROUND(I21,2)
K21	K21	=1270*217.88
L21:L27	L21	=K21-J21
K22	K22	=1270*99.04
K23	K23	=3809*176.11
K24	K24	=14*1377.58
K25	K25	=3809*11.01
K26	K26	=5*8385.25
J27:K27	J27	=SUM(J21:J26)