IF(AND function

[conchaz]

Im trying to use an IF(AND function for a set of data that ive attached a sample of below, basically what i need the function to reflect is:

The expected revenue account codes (4 digits - highlighted top left) that agree with the predetermined acceptable journal entry numbers (5 digits - highlighted green top right) to return "accept" and any other combinations of either the acceptable revenue code and an unexpected journal account, or an expected journal account and an unacceptable account code to return "investigate". The resulting fields should reveal possible fraudulent revenue postings in a companies manual journal entries.

I need to request posting attachment permissions before i can attach my data set! Any help with that is also appreciated

 

[conchaz]

Re: Need assistance with an IF(AND function

	Accepted Revenue accounts
	Account Code					Reasonable					Accepted	Journal Entries
	1611					5273	Trust bank account - WBC					66772	Yes
	1641											66925	Yes
	1643											67075	Yes
	1644											67180	Yes	<-- Journal entry listing of all manual JE's with a revenue entry
	1656											67330	Yes
	1677											67430	Yes
	1811						Revenue lines posted manually					67582	Yes
	1812						Debit side of revenue entry					67703	Yes
	1813											67873	Yes
	1814						Conclusion: No manual revenue entry booked to an account other than cash.					68031	Yes
	1815											68151	Yes
	1816											68337	Yes
	1991
	1993
			Period	Account Code	Base Amount	Description	Transaction Date	Filter for colored rows	Journal No.	Journal No.
			2017/001	1331	(20,151)	PI470 mgt fee	2/08/2016		66705	Investigate
			2017/001	1331	25,000.00	Prior RE Mgt Fee adj	2/08/2016		66713	Investigate
			2017/001	1331	(22,000)	July 16 RE Mgt Fee	2/08/2016		66713	Investigate
			2017/001	1611	-3098.35	Misc bank interest	2/08/2016		66705	Investigate
			2017/001	1611	(1,888)	July16 interest inc	2/08/2016		66705	Investigate
			2017/001	1655	(777,367)	Revalue OPF	2/08/2016		66713	Investigate
			2017/001	1666	(28,065)	Revalue RPT	4/08/2016		66748	Investigate
			2017/001	1678	(4,399)	Swap MTM movement	4/08/2016		66748	Investigate
			2017/001	1811	(26,712)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1811	(120,006)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1811	(72,542)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1811	(2,108)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1811	(2,500)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1811	(45,000)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1811	(98,020)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1811	(2,299)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1811	(396,334)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1811	(66,595)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1811	(9,736)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1811	(255,073)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1811	(12,066)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1811	(225)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1811	(167,177)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1811	(48,285)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1811	(3,504)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1811	(10,496)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1811	(125,266)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1811	(85,932)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1811	(3,070)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1811	(245,531)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1811	(23,571)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1811	(1,949)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1811	(3,571)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1812	(48,420)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1812	(4,262)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1812	(1,496)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1813	(46,498)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1813	(967)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1813	(42,731)	Months Net Activity	1/08/2016		66694	Investigate
			2017/001	1813	(48,638)	Months Net Activity	1/08/2016		66694	Investigate

<colgroup><col><col span="2"><col span="2"><col><col><col><col><col><col span="9"></colgroup><tbody>
</tbody>