if function formula in a accounting format sheet

[RAJESH1960]

Hello Guys, Wishing all of you a very Happy Happy New Year 2021.
I have 2 workings in this sheet. One is a accounting format sheet where I want the right formula and the other is a general sheet where the formula works. I am not able to get the right answer in the accounting format when I have applied the same formula in both the workings. Does the formula work in a general format sheet only, if not, please share me the solution for the above.

Calculated Duties and taxes.xlsx
	A	B	C	D	E	F	G	H
1	Accounting Format				General Format
2		304.87			1
3		29,211.67				2
4		32,817.08			3		-3
5		450.00				4	-2
6		304.87					-4
7		29,211.67					-1
8		32,817.08			4	6	-10
9	59.54		-245.33			10	Matched	=IF(G9+H8=0,"Matched","Not Matched")
10	4,111.34		-25,100.33
11	10,145.81		-22,671.27
12	24,428.01		24,428.01
13	30,030.10		29,580.10
14	436.48		436.48
15	59.54		-245.33
16	4,111.34		-25,100.33
17	10,145.81		-22,671.27
18	83,527.97	1,25,117.24	-41,589.27
19		41,589.27	Not Matched	=IF(C19+D18=0,"Matched","Not Matched")
duties & TAxes (2)

Cell Formulas
Range	Formula
G4	G4	=-E4
G5	G5	=-F3
G6	G6	=-F5
G7	G7	=-E2
E8:G8	E8	=SUM(E2:E7)
F9	F9	=F8+E8
G9,C19	G9	=IF(F9+G8=0,"Matched","Not Matched")
C9:C11	C9	=A9-B2
C12,C14	C12	=A12
C13	C13	=A13-B5
C15:C17	C15	=A15-B6
A18:C18	A18	=SUM(A2:A17)
B19	B19	=B18-A18

 

[Fluff]

It's got nothing to do with the cell formats, it's down to floating point arithmetic Floating-point arithmetic may give inaccurate result in Excel - Office

If you put this formula in a cell

=(C18+B19)*10^10

you can see that the two numbers are not exactly the same.
One way to get round this is

=IF(ROUND(B19+C18,0)=0,"Matched","Not Matched")

 

[Dave Patton]

try =IF(Round(B19,2)+(Round(C18,2)=0,"Matched","Not Matched")

It would have been helpful if your had posted your data with XL2BB.

 

[Fluff]

It would have been helpful if your had posted your data with XL2BB.

That's exactly what the OP did do.

 

[RAJESH1960]

It's got nothing to do with the cell formats, it's down to floating point arithmetic Floating-point arithmetic may give inaccurate result in Excel - Office

If you put this formula in a cell

=(C18+B19)*10^10

you can see that the two numbers are not exactly the same.
One way to get round this is

=IF(ROUND(B19+C18,0)=0,"Matched","Not Matched")

Sorry, Excuse Me till I check again

 

[Fluff]

Sorry but I don't understand what you mean.

 

[RAJESH1960]

Sorry but I don't understand what you mean.

There are blank cells below the values, that is why it is not calculating correctly. I have given the range including the blank cells to sum. Even the general format is not working, if it has blank cells above the sum amount. Please check this. If I add a value in the blank cells above the sum amount, then too it is showing matched.

Accounting Format			General Format
	304.87			1​
	29,211.67				2​
	32,817.08			3​		-3​
	450.00				4​	-2​
	304.87					-4​
	29,211.67					-1​
	32,817.08
59.54		-245.33
4,111.34		-25,100.33		4​	6​	-10​
10,145.81		-22,671.27			10​	Matched	=IF(G9+H8=0,"Matched","Not Matched")
24,428.01		24,428.01
30,030.10		29,580.10
436.48		436.48
59.54		-245.33
4,111.34		-25,100.33
10,145.81		-22,671.27
83,527.97	1,25,117.24	-41,589.27
	41,589.27	Matched	=IF(C19+D18=0,"Matched","Not Matched")

 

[Fluff]

Please post your data again using the XL2BB add-in

 

[RAJESH1960]

Please post your data again using the XL2BB add-in

Calculated Duties and taxes.xlsx
	A	B	C
1
2		304.87
3		29,211.67
4		32,817.08
5		450.00
6		304.87
7		29,211.67
8		32,817.08
9	59.54		-245.33
10	4,111.34		-25,100.33
11	10,145.81		-22,671.27
12	24,428.01		24,428.01
13	30,030.10		29,580.10
14	436.48		436.48
15	59.54		-245.33
16	4,111.34		-25,100.33
17	10,145.81		-22,671.27
18			2
19
20
21
22
23	83,527.97	1,25,117.24	-41,587.27
24		41,589.27	Matched
New query

Cell Formulas
Range	Formula
C9:C11	C9	=A9-B2
C12,C14	C12	=A12
C13	C13	=A13-B5
C15:C17	C15	=A15-B6
A23:C23	A23	=SUM(A2:A22)
B24	B24	=B23-A23
C24	C24	=IF(B24<>C23,"Matched", "Not Matched")

 

[RAJESH1960]

Sorry Once again. If is working fine. Thanks Fluff.