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

 

[ckmoied01]

Thanks for the suggestion...........in the master summary sheet rounding off the numbers to 2 decimals in product fomula fixes the problem............however all the child sheets are still showing the wrong numbers...............how can i fix this issue for all the sheets in the workbook ?

This brings me the risk of calculating wring prices all over the sheets. Any suggestion to fix the problem overall the workbook?

Like in the first row of summary sheet 276.707,60 still showing 276.712,92 in underlying child sheet.......Finance does not accept the mismatch

Angebot V1 051223.xlsx
	I	J	K	L	M	N	O	P		R	S	T	U	V	W
22			-												Actual should be
23			-		3,30	20%	3,96			3,30	3,96		230.594,10	276.712,92	276.707,60
Tabelle2

Cell Formulas
Range	Formula
K22:K23,O23	K22	=I22+I22*J22
R23	R23	=I23+M23
S23	S23	=K23+O23
U23	U23	=(I23+M23)*69877
V23	V23	=(K23+O23)*69877
W23	W23	=Tabelle1!J21

 

[ckmoied01]

is this problem coming from user defined format ???

 

[felixstraube]

The numbers in column I are the resulta of a formula right?
If that is true then try this: Instead of rounding the numbers in Column J (or you can leave it, but it is not necessary anymore) wrap the formula in column I with the round function.

so in column I you'll have

=ROUND(<replace with your formula>,2)

where logically you'll have to replace <replace with your formula> with the formula you had.

So you'll have something like this.
Do this in all child sheets. And you summary should be ok (if it pulls data from columna F and I)

 

[ckmoied01]

Tried rounding off all the child cells and child numbers...............does not fix

Angebot V1 051223.xlsx
	I	J	K	L	M	N	O	P		R	S	T	U	V	W
22			-												Actual should be
23			-		3,30	20%	3,96			3,30	3,96		230.594,10	276.712,92	276.707,60
Tabelle2

Cell Formulas
Range	Formula
K22	K22	=I22+I22*J22
K23,O23	K23	=ROUND(I23+I23*J23,2)
R23	R23	=ROUND((I23+M23),2)
S23	S23	=ROUND((K23+O23),2)
U23	U23	=(I23+M23)*69877
V23	V23	=ROUND((K23+O23)*69877,2)
W23	W23	=Tabelle1!J21

 

[Joe4]

Note that instead of rounding every value and every formula, you may be able to use Excel's "set precision as displayed" setting.
See: Set rounding precision - Microsoft Support.

 

[felixstraube]

You'll have to explain a little more about your process/calculation. Why it should be the result shown in W23. How does Finance come to this number?