Rounding in a IF AND statement

[EmmaTM]

Hi,
I have the following if statement:
=IF(AND(AQ19="0",H19=0),"Ok",IF(AND(AQ19="V",H19=0),"Ok",IF(AND(AQ19="1",H19=G19*20%),"Ok",IF(AND(AQ19="S",H19=0),"Ok",IF(AND(C19="Receipt",H19=0),"Ok")))))

IF(AND(AQ19="1",H19=G19*20%) - this bit is resulting in FALSE, I think I need to round this down to 2DP. I think I need to add 'ROUND' into the statement but where?

Many thanks as always
Emma

 

[6StringJazzer]

It is very hard to answer this with no idea of what your data looks like. Normally is it risky to compare two real numbers for equality. I am inclined to round both numbers before comparing and do this:

IF(AND(AQ19="1",ROUND(H19,2)=ROUND(G19*20%,2))

 

[EmmaTM]

Thank you that worked on some of the items where VAT is code 1 = 20% but not all, do you think this could be a case of needing to round up and down? These ones below did not work

 

[EmmaTM]

Type	Net	VAT	Gross	VAT	Vat Check
Invoice	50,500.64	0.00	50,500.64	V	Ok
Invoice	61.47	0.00	61.47	V	Ok
Invoice	8,680.50	0.00	8,680.50	V	Ok
Receipt	-17,030.48	0.00	-17,030.48	1	Ok
Receipt	-570,192.90	0.00	-570,192.90	1	Ok
Receipt	-16,220.52	0.00	-16,220.52	1	Ok
Invoice	60.50	12.10	72.60	1	Ok
Invoice	59.50	11.90	71.40	1	Ok
Invoice	66.00	13.20	79.20	1	Ok
Invoice	33.00	6.60	39.60	1	Ok
Invoice	51.50	10.30	61.80	1	Ok
Invoice	356.00	71.20	427.20	1	Ok
Invoice	211.50	42.30	253.80	1	Ok
Invoice	162.00	32.40	194.40	1	Ok
Receipt	-112,415.96	0.00	-112,415.96	1	Ok
Receipt	-13,200.00	0.00	-13,200.00	1	Ok
Receipt	-104,639.65	0.00	-104,639.65	V	Ok
Invoice	2,890.27	578.05	3,468.32	1	Ok
Receipt	-6,567.60	0.00	-6,567.60	1	Ok
Receipt	-13,559.87	0.00	-13,559.87	1	Ok
Receipt	-13,993.57	0.00	-13,993.57	1	Ok
Receipt	-3,210.00	0.00	-3,210.00	1	Ok
Receipt	-4,094.89	0.00	-4,094.89	1	Ok
Invoice	2,195.00	439.00	2,634.00	1	Ok
Invoice	100.00	20.00	120.00	1	Ok
Receipt	-1,311.00	0.00	-1,311.00	1	Ok
Receipt	-5,697.60	0.00	-5,697.60	1	Ok
Invoice	27,331.29	0.00	27,331.29	0	Ok
Invoice	469.25	0.00	469.25	0	Ok
Invoice	35.19	0.00	35.19	0	Ok
Invoice	-2,375.00	0.00	-2,375.00	V	Ok
Invoice	11,783.80	0.00	11,783.80	V	Ok
Invoice	1,900.00	0.00	1,900.00	V	Ok
Receipt	-9,397.08	0.00	-9,397.08	1	Ok
Receipt	-44,346.47	0.00	-44,346.47	1	Ok
Invoice	5,856.00	0.00	5,856.00	V	Ok
Invoice	5,580.00	0.00	5,580.00	V	Ok
Invoice	3,248.00	0.00	3,248.00	V	Ok
Invoice	2,240.00	0.00	2,240.00	V	Ok
Invoice	160.00	32.00	192.00	1	Ok
Receipt	-192.00	0.00	-192.00	1	Ok
Receipt	-1,173.00	0.00	-1,173.00	V	Ok
Receipt	-3,250.12	0.00	-3,250.12	1	Ok
Receipt	-375,731.04	0.00	-375,731.04	1	Ok
Receipt	-54,178.59	0.00	-54,178.59	1	Ok
Receipt	-511.13	0.00	-511.13	1	Ok
Receipt	-8,370.57	0.00	-8,370.57	1	Ok
Receipt	-928.20	0.00	-928.20	1	Ok
Receipt	-67,039.27	0.00	-67,039.27	1	Ok
Receipt	-22,497.65	0.00	-22,497.65	V	Ok
Invoice	304.50	60.90	365.40	1	Ok
Receipt	-365.40	0.00	-365.40	1	Ok
Invoice	1,989.25	0.00	1,989.25	V	Ok
Invoice	1,989.25	0.00	1,989.25	V	Ok
Invoice	11,130.00	0.00	11,130.00	V	Ok
Invoice	372.86	0.00	372.86	V	Ok
Receipt	-1,269.50	0.00	-1,269.50	1	Ok
Invoice	2,224.41	444.88	2,669.29	1	Ok
Invoice	22.24	4.45	26.69	1	Ok
CreditNote	-2,887.60	-577.52	-3,465.12	1	Ok
Invoice	5,649.60	1,129.92	6,779.52	1	Ok
Invoice	2,675.00	535.00	3,210.00	1	Ok
Invoice	405.72	81.14	486.86	1	Ok
Invoice	518.00	103.60	621.60	1	Ok
Invoice	1,064.85	212.97	1,277.82	1	Ok
Invoice	2,739.63	547.93	3,287.56	1	Ok
Invoice	2,332.53	466.50	2,799.03	1	FALSE
Invoice	3,960.00	792.00	4,752.00	1	Ok
Invoice	1,518.00	303.60	1,821.60	1	Ok
Invoice	248.00	49.60	297.60	1	Ok
Invoice	1,267.50	253.50	1,521.00	1	Ok
Invoice	440.00	88.00	528.00	1	Ok
Invoice	3,674.80	734.96	4,409.76	1	Ok
Invoice	5,686.56	1,137.31	6,823.87	1	Ok
Invoice	12,005.14	2,401.03	14,406.17	1	Ok
Invoice	273.39	54.68	328.07	1	Ok
Invoice	2,280.00	456.00	2,736.00	1	Ok
Invoice	1,230.26	246.05	1,476.31	1	Ok
Invoice	1,077.20	215.44	1,292.64	1	Ok
Invoice	1,077.20	215.44	1,292.64	1	Ok
Invoice	1,077.20	215.44	1,292.64	1	Ok
Invoice	1,077.20	215.44	1,292.64	1	Ok
Invoice	1,077.20	215.44	1,292.64	1	Ok
Invoice	2,851.20	570.24	3,421.44	1	Ok
Invoice	2,441.80	0.00	2,441.80	V	Ok
Invoice	28,939.60	0.00	28,939.60	V	Ok
Invoice	1,600.20	320.04	1,920.24	1	Ok
Invoice	4,536.95	907.39	5,444.34	1	Ok
Invoice	8,576.70	1,715.34	10,292.04	1	Ok
Invoice	1,864.50	372.90	2,237.40	1	Ok
Invoice	4,039.75	807.95	4,847.70	1	Ok
Invoice	20,631.74	0.00	20,631.74	V	Ok
Invoice	23,342.50	0.00	23,342.50	V	Ok
Invoice	1,998.00	0.00	1,998.00	V	Ok
Invoice	59,430.00	0.00	59,430.00	V	Ok
Invoice	1,907.50	381.50	2,289.00	1	Ok
Invoice	368.71	73.74	442.45	1	Ok
Invoice	1,493.76	298.75	1,792.51	1	Ok
Invoice	1,209.10	241.82	1,450.92	1	Ok
Invoice	9,194.11	1,838.82	11,032.93	1	Ok
Invoice	1,071.00	214.20	1,285.20	1	Ok
Invoice	2,493.49	498.70	2,992.19	1	Ok
Invoice	2,268.00	453.60	2,721.60	1	Ok
Invoice	2,750.00	550.00	3,300.00	1	Ok
Invoice	1,848.00	369.60	2,217.60	1	Ok
Invoice	3,250.00	650.00	3,900.00	1	Ok
Invoice	5,808.40	1,161.68	6,970.08	1	Ok
Invoice	2,730.60	546.12	3,276.72	1	Ok
Invoice	430.49	86.10	516.59	1	Ok
Invoice	1,288.00	257.60	1,545.60	1	Ok
Invoice	4,257.00	851.40	5,108.40	1	Ok
Invoice	108,088.39	21,617.63	129,706.02	1	FALSE
Invoice	257,552.00	51,510.39	309,062.39	1	FALSE
Invoice	1,743.96	348.85	2,092.81	1	FALSE
Invoice	23.99	4.80	28.79	1	Ok
Invoice	74,802.16	14,960.45	89,762.61	1	FALSE
Invoice	84,932.41	16,986.50	101,918.91	1	FALSE
Invoice	2,675.09	534.98	3,210.07	1	FALSE
Invoice	13,965.00	2,793.00	16,758.00	1	Ok
Invoice	1,650.00	330.00	1,980.00	1	Ok
Invoice	6,864.00	1,372.80	8,236.80	1	Ok
Invoice	6,500.00	1,300.00	7,800.00	1	Ok
Invoice	16,576.49	3,315.30	19,891.79	1	Ok

 

[6StringJazzer]

First, I am assuming the 0 and 1 data in column AQ is numeric. If so, do not put quotes around 0 and 1 in the formula.

I'm not seeing a problem. Here is your data with the formula I recommended. Here are the rows where VAT Check comes back FALSE, and it should be FALSE as far as I can tell. I added the last three columns as a check.

$scratch.xlsm
	C				G	H	I																																		AQ	AR	AS	AT	AU
1	Type				Net	VAT	Gross																																		VAT	VAT Check	Calculated VAT	Actual VAT	Match
2	Invoice				2332.53	466.5	2799.03																																		1	FALSE	466.51	466.50	FALSE
3	Invoice				108088.39	21617.63	129706.02																																		1	FALSE	21617.68	21617.63	FALSE
4	Invoice				257552	51510.39	309062.39																																		1	FALSE	51510.40	51510.39	FALSE
5	Invoice				1743.96	348.85	2092.81																																		1	FALSE	348.79	348.85	FALSE
6	Invoice				74802.16	14960.45	89762.61																																		1	FALSE	14960.43	14960.45	FALSE
7	Invoice				84932.41	16986.5	101918.91																																		1	FALSE	16986.48	16986.50	FALSE
8	Invoice				2675.09	534.98	3210.07																																		1	FALSE	535.02	534.98	FALSE
VAT (2)

Cell Formulas
Range	Formula
AR2:AR8	AR2	=IF(AND(AQ2=0,H2=0),"Ok",IF(AND(AQ2="V",H2=0),"Ok",IF(AND(AQ2=1,ROUND(H2,2)=ROUND(G2*20%,2)),"Ok",IF(AND(AQ2="S",H2=0),"Ok",IF(AND(C2="Receipt",H2=0),"Ok")))))
AS2:AS8	AS2	=IF(OR(AQ2<>1,C2="Receipt"),"",ROUND(G2*20%,2))
AT2:AT8	AT2	=IF(OR(AQ2<>1,C2="Receipt"),"",ROUND(H2,2))
AU2:AU8	AU2	=AND(AQ2=1,ROUND(H2,2)=ROUND(G2*20%,2))