How do I round my data this way?

[himurah182]

Hey guys,
I have a problem: I made this table that accounts the costs that were incurred by each customer on every product. However, the results between this table and my invoice software differ in some cents.
I think this happens because when I calculate the cost on my table I only apply the discount and round the total to 2 decimal places in the end, which is different from my software that rounds the data to 2 decimal places before getting to the final result which is also rounded.
What do I need to change in my table to get the same result as my software?

On my software, the cost incurred for this customer was 464,57. On my table, the same invoice got 454,56.

Calc.xlsm
	A	B	C	D	E	F
1
2
3
4
5				Fatura		134
6				Where code		26253
7		Plu	Produto	Preço Und		Cacem-Av B Amigos
8	type 1	103581	RPBLACK2-BLACK ULTRA THIN 1,1/4	0,454		100
9	type 1	103582	RPBLACK3-BLACK KING SIZE	0,495
10	type 1	103583	BOLSA FILTROS REGULAR-8MM-30B-100F	0,482
11	type 1	103584	BOLSA FILTROS SLIM-6MM-34B-120F	0,433		34
12	type 1	103585	ISQUEIRO CLIPPER PEQ. CP22RH	0,607		96
13	type 1	103586	ISQUEIRO CLIPPER PEQ FUN	1,770
14	type 1	103587	ISQUERIRO CLIPPER GRD CP11RH	0,695		144
15	type 1	103902	ISQUEIRO CLIPPER CLASS.METAL LARG CAIXA	6,750
16	type 1	103903	BOLSA FILTROS LONG SLIM-6MM-30B-100F	0,557
17	type 1	103904	BOLSA FILTROS LONG REGULAR-8MM-20B-100F	0,557
18	type 1	105457	ISQUEIRO Clipper Classic POP CPR11H	1,755
19	type 1	106629	ISQUEIRO MINI CLIPPER TUBE	0,920
20	type 1	106630	PAPEL CLIPPER BLUE ULTRA THIN REGULAR	0,360
21	type 1	106631	PAPEL CLIPPER PURE ULTRA THIN REGULAR	0,360
22	type 1	106632	PAPEL CLIPPERPURE ULTRA THIN KSS	0,545
23	type 1	106633	PAPEL CLIPPER RED REGULAR CLASSIC	0,338
24	type 1	106634	PAPEL CLIPPER GREEN CUT CORNERS REGULAR	0,338
25	type 1	106636	MÁQUINA ENROLAR	1,697
26	type 1	106637	FILTROS POP UP EXTRA SLIM	0,685
27	type 1	106638	MEN-FILTROS POP UP EXTRA SLIM MENTOL	0,763
28	type 1	106639	FILTROS SLIM ORGANIC BOLSA	0,648
29	type 1	107670	SET KING SIZE SLIM+FILTROS DE PAPEL	0,860		300
30	type 1	116773	PAPEL RAW ORG HEMP KING SIZE SLIM	0,575
31	type 1	116775	PAPEL RAW CLASSIC KING SIZE SLIM	0,545
32	type 1	116774	PAPEL RAW ORG REGULAR	0,385
33	type 1	116776	FILTROS RAW ORGANIC SLIM	0,650
34	Type 2	103588	RELÓGIO Medium Price	17,200
35	Type 2	103589	RELÓGIO Low Price	13,750
36	Type 2	103905	RELOGIO ANALOGICO	27,550
37	Type 2	106640	SMARTWATCH SAMI	45,890
38	Type 3	106506	CIGARREIRA CAIXA PLASTICO	2,650
39	Type 3	106506	CIGARREIRA SILICONE	2,650
40	Type 3	106661	Cigarreira Cartão	2,650
41	Type 3	106506	CIGARREIRA CAIXA METAL	2,650
42	Type 3	103906	LINHA PORTUGAL PORTA MOEDAS	3,390
43	Type 3	103907	LINHA PORTUGAL ESTOJO	4,490
44	Type 3	103908	LINHA PORTUGAL CARTEIRA 2	5,690
45	Type 3	103909	LINHA PORTUGAL CARTEIRA	5,690
46	Type 3	103910	CARTEIRA PRIMAVERA C/FECHO 119 BAND	5,650
47	Type 3	103911	CARTEIRA PRIMAVERA GRANDE 110	4,490
48	Type 3	103912	CARTEIRA PRIMAVERA PEQUEÑA 111	4,050
49	Type 3	103913	MATRAFONA GIGANTE	11,450
50	Type 3	103914	BLOCO NOTAS	1,610
51	Type 3	103915	PULSERIRA PELE COM CAIXA	12,590
52	Type 3	103916	LICOREIRA CORTIÇA	6,850
53	Type 3	103917	PULSEIRAS CORTIÇA	6,850
54	Type 3	103918	COLARES CORTIÇA	7,990
55	Type 3	103919	CANETA CORTIÇA COM CAIXA	5,690
56	Type 3	106507	PELUCHE TAM 1	10,290
57	Type 3	106508	PELUCHE TAM 3	16,050
58	Type 3	106509	OCULOS SOL	15,690
59	Type 3	106641	CARTAS JOGAR PLÁSTICO	5,200
60	Type 3	106642	CARTEIRA GB SRA PELE	25,250
61	Type 3	106643	CARTEIRA GB HM PELE-PEQ	14,890
62	Type 3	106644	CARTEIRA GB HM PELE- GR	17,200
63	Type 3	106645	SACA ROLHAS CORTIÇA	2,850
64	Type 3	106654	CIGARREIRAS CORTIÇA	5,750
65	Type 3	106655	MINIATURA V. PORTO/LICOR BEIRÃO	3,450
66	Type 3	106656	CONJ 2 MINIATURAS PORTO+BEIRÃO	5,200
67	Type 3	106657	PORTA MOEDAS PEQ CORTIÇA-VÁRIOS MODELOS	4,550
68	Type 3	106658	PORTA MOEDAS DA AVÓ-CORTIÇA	7,900
69	Type 3	106659	PORTA MOEDAS HM -CORTIÇA	9,150
70	Type 3	106660	PORTA MOEDAS MOCHILA CORTIÇA	6,050
71	Type 3	107671	PUZZLES- SORTIDOS	3,450
72	Type 3	107672	JOGO TABULEIRO-SORTIDO	6,300
73	Type 3	107673	OVO DINOSSAURO	3,160
74	Type 3	107674	CONJ CORTIÇA	11,450
75	type 4	103804	BORDA D'ÁGUA	1,600
76	type 5	114755	TUBOS T&T 500 UND	2,020
77	type 5	115903	TUBOS KORONA 300 UND	1,122
78	type 5	115902	TUBOS KORONA 500 UND	2,020
79
80
81
82	2,5%	1%	Discount
83				unit price
84	type 1	103581	RPBLACK2-BLACK ULTRA THIN 1,1/4	0,454
85	type 1	103582	RPBLACK3-BLACK KING SIZE	0,495
86	type 1	103583	BOLSA FILTROS REGULAR-8MM-30B-100F	0,482
87	type 1	103584	BOLSA FILTROS SLIM-6MM-34B-120F	0,433
88	type 1	103585	ISQUEIRO CLIPPER PEQ. CP22RH	0,607
89	type 1	103586	ISQUEIRO CLIPPER PEQ FUN	1,77
90	type 1	103587	ISQUERIRO CLIPPER GRD CP11RH	0,695
91	type 1	103902	ISQUEIRO CLIPPER CLASS.METAL LARG CAIXA	6,75
92	type 1	103903	BOLSA FILTROS LONG SLIM-6MM-30B-100F	0,557
93	type 1	103904	BOLSA FILTROS LONG REGULAR-8MM-20B-100F	0,557
94	type 1	105457	ISQUEIRO Clipper Classic POP CPR11H	1,755
95	type 1	106629	ISQUEIRO MINI CLIPPER TUBE	0,92
96	type 1	106630	PAPEL CLIPPER BLUE ULTRA THIN REGULAR	0,36
97	type 1	106631	PAPEL CLIPPER PURE ULTRA THIN REGULAR	0,36
98	type 1	106632	PAPEL CLIPPERPURE ULTRA THIN KSS	0,545
99	type 1	106633	PAPEL CLIPPER RED REGULAR CLASSIC	0,338
100	type 1	106634	PAPEL CLIPPER GREEN CUT CORNERS REGULAR	0,338
101	type 1	106636	MÁQUINA ENROLAR	1,697
102	type 1	106637	FILTROS POP UP EXTRA SLIM	0,685
103	type 1	106638	MEN-FILTROS POP UP EXTRA SLIM MENTOL	0,763
104	type 1	106639	FILTROS SLIM ORGANIC BOLSA	0,648
105	type 1	107670	SET KING SIZE SLIM+FILTROS DE PAPEL	0,86
106	type 1	116773	PAPEL RAW ORG HEMP KING SIZE SLIM	0,575
107	type 1	116775	PAPEL RAW CLASSIC KING SIZE SLIM	0,545
108	type 1	116774	PAPEL RAW ORG REGULAR	0,385
109	type 1	116776	FILTROS RAW ORGANIC SLIM	0,65
110	type 2	103588	RELÓGIO Medium Price	17,2
111	type 2	103589	RELÓGIO Low Price	13,75
112	type 2	103905	RELOGIO ANALOGICO	27,55
113	type 2	106640	SMARTWATCH SAMI	45,89
114	type 3	106506	CIGARREIRA CAIXA PLASTICO	2,65
115	type 3	106506	CIGARREIRA SILICONE	2,65
116	type 3	106661	Cigarreira Cartão	2,65
117	type 3	106506	CIGARREIRA CAIXA METAL	2,65
118	type 3	103906	LINHA PORTUGAL PORTA MOEDAS	3,39
119	type 3	103907	LINHA PORTUGAL ESTOJO	4,49
120	type 3	103908	LINHA PORTUGAL CARTEIRA 2	5,69
121	type 3	103909	LINHA PORTUGAL CARTEIRA	5,69
122	type 3	103910	CARTEIRA PRIMAVERA C/FECHO 119 BAND	5,65
123	type 3	103911	CARTEIRA PRIMAVERA GRANDE 110	4,49
124	type 3	103912	CARTEIRA PRIMAVERA PEQUEÑA 111	4,05
125	type 3	103913	MATRAFONA GIGANTE	11,45
126	type 3	103914	BLOCO NOTAS	1,61
127	type 3	103915	PULSERIRA PELE COM CAIXA	12,59
128	type 3	103916	LICOREIRA CORTIÇA	6,85
129	type 3	103917	PULSEIRAS CORTIÇA	6,85
130	type 3	103918	COLARES CORTIÇA	7,99
131	type 3	103919	CANETA CORTIÇA COM CAIXA	5,69
132	type 3	106507	PELUCHE TAM 1	10,29
133	type 3	106508	PELUCHE TAM 3	16,05
134	type 3	106509	OCULOS SOL	15,69
135	type 3	106641	CARTAS JOGAR PLÁSTICO	5,2
136	type 3	106642	CARTEIRA GB SRA PELE	25,25
137	type 3	106643	CARTEIRA GB HM PELE-PEQ	14,89
138	type 3	106644	CARTEIRA GB HM PELE- GR	17,2
139	type 3	106645	SACA ROLHAS CORTIÇA	2,85
140	type 3	106654	CIGARREIRAS CORTIÇA	5,75
141	type 3	106655	MINIATURA V. PORTO/LICOR BEIRÃO	3,45
142	type 3	106656	CONJ 2 MINIATURAS PORTO+BEIRÃO	5,2
143	type 3	106657	PORTA MOEDAS PEQ CORTIÇA-VÁRIOS MODELOS	4,55
144	type 3	106658	PORTA MOEDAS DA AVÓ-CORTIÇA	7,9
145	type 3	106659	PORTA MOEDAS HM -CORTIÇA	9,15
146	type 3	106660	PORTA MOEDAS MOCHILA CORTIÇA	6,05
147	type 3	107671	PUZZLES- SORTIDOS	3,45
148	type 3	107672	JOGO TABULEIRO-SORTIDO	6,3
149	type 3	107673	OVO DINOSSAURO	3,16
150	type 3	107674	CONJ CORTIÇA	11,45
151	type 4	103804	BORDA D'ÁGUA	1,6
152	type 5	114755	TUBOS T&T 500 UND	2,02
153	type 5	115903	TUBOS KORONA 300 UND	1,122
154	type 5	115902	TUBOS KORONA 500 UND	2,02
155
Sheet1

Cell Formulas
Range	Formula
E6	E6	=IFNA(INDEX(Postos!B3:B215,MATCH(Sheet1!E7,Postos!C3:C215,0)),"")
D8:D78	D8	=D84

Cells with Data Validation
Cell	Allow	Criteria
E7:F7	List	=Postos!$D$3#

Calc.xlsm
	A	B	C	D	E	F	G
1	Where code	BP MAIO 2022	type 1	type 2	type 3	type 4	type 5
2		Postos/vendas	Clipper	Relogios	Restantes artigos	Borda D'Água	Tubos
3	26253	Cacem-Av B Amigos	476,474 €	0,000 €	0,000 €	0,000 €	0,000 €
4	26507	Santarem/ nascente	0,000 €	0,000 €	0,000 €	0,000 €	0,000 €
5	26800	Santarem/poente	0,000 €	0,000 €	0,000 €	0,000 €	0,000 €
6	27036	Almada V.R.-Norte	0,000 €	0,000 €	0,000 €	0,000 €	0,000 €
7	27037	Almada V.R.-Sul	0,000 €	0,000 €	0,000 €	0,000 €	0,000 €
8	27004	Pêro Pinheiro	0,000 €	0,000 €	0,000 €	0,000 €	0,000 €
9	27008	Ic 19-Poente	0,000 €	0,000 €	0,000 €	0,000 €	0,000 €
10	27078	Ramalhão	0,000 €	0,000 €	0,000 €	0,000 €	0,000 €
BP_MAIO_2022

Cell Formulas
Range	Formula
C3:G10	C3	=SUMPRODUCT((Sheet1!$D$8:$D$78)*(Sheet1!$E$8:$G$78)*(Sheet1!$A$8:$A$78=C$1)*(Sheet1!$E$6:$G$6=$A3))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F4:G217,C3:G215	Cell Value	<0	text	NO

Calc.xlsm
	A	B	C	D	E	F	G	H	I
217
218		Total	464,562 €	0,000 €	0,000 €	0,000 €	0,000 €	464,56 €
219
BP_MAIO_2022

Cell Formulas
Range	Formula
C218	C218	=SUM(C3:C217)*(1-Sheet1!$A$82)
D218:G218	D218	=ROUND(SUM(D3:D217),2)
H218	H218	=ROUND(SUM(C218:G218),2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F4:G217,C3:G215	Cell Value	<0	text	NO

 

[GraH]

I first was thinking to suggest using banker's rounding on your intermediate results.

ASTM E29 Rounding - 1046 - Learn Excel from MrExcel Podcast​

and here

However, going by this single example it looks like a simple roundup. The result is 464,5622 €.

Book1
	D	E
1
2	€ 464,5622	€ 464,57
Sheet1

Cell Formulas
Range	Formula
E2	E2	=ROUNDUP(D2,2)

 

[himurah182]

Hi there
It did not work properly when I added other invoices.
My software rounds each line of product (quantity bought x discounted product price) before summing all up.
This is what caused some difference when I added other data.
What should I do?

 

[GraH]

Then apply rounding to your intermediate results - i.e round is wrapped around your sumproduct - before summing, like your software does.