ADVCF

[Xlambda]

ADVCF ADVanced Conditional Formatting , for arrays, calls T_AFA , T_AFM. My approach on CF.
Having already ADVFLT , ADVCF is extremely easy, Same arguments, same functionality, hardcoding any conditions we want, bolean logic (and,or), capable. If filter syntax is FILTER(a,include), ADVCF is the include argument. Check ADVFLT post for more details. Other function on minisheet ACOUNTIFS
Task 1. For the given table highlight the records btwn 2 dates with sales over 50 units, for regions either East or West or for brand Apple whatever region. Out of this results , highlight the sales that took place on weekends. The formula we need:

=LAMBDA(a,clm,crm,cla,cra,
    LET(tm,IF(SUM(clm)=0,1,T_AFM(a,clm,crm,1)),ta,IF(SUM(cla)=0,1,T_AFA(a,cla,cra,0)),
       IF(tm*ta,1,0)
    )
)

LAMBDA 10.0.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	sample table														=ADVCF(S,{1,1,10},{">=1-08-20","<1-05-21",">=50"},{3,8,8},{"Apple","East","West"})
2	Date	Prod	Brand	Type	Cost	Price/u	Shop	Region	Manager	uSold	TCost	Rev	NetRev				=O3#+ACOUNTIFS(TEXT(S[Date],"ddd"),{"Sat";"Sun"})
3	09-07-20	Prod 3	Samsung	phone	230	240	Shop C	East	John S	84	19320	20160	840		0		0
4	14-07-20	Prod 1	Apple	tablet	280	350	Shop F	South	Steve J	85	23800	29750	5950		0		0		CF formulas
5	23-07-20	Prod 6	Samsung	tablet	170	185	Shop C	East	John S	11	1870	2035	165		0		0		AaBbCc	=$O3
6	30-07-20	Prod 8	Apple	laptop	420	510	Shop D	West	Mike B	77	32340	39270	6930		0		0		AaBbCc	=$Q3=2
7	03-08-20	Prod 4	Apple	phone	350	370	Shop D	West	Mike B	68	23800	25160	1360		1		1
8	08-08-20	Prod 2	HP	laptop	520	620	Shop C	East	John S	34	17680	21080	3400		0		1		AaBbCc	when both condition meet
9	22-08-20	Prod 8	Apple	laptop	420	510	Shop E	North	Bill T	41	17220	20910	3690		0		1
10	02-09-20	Prod 7	Samsung	laptop	650	780	Shop A	East	John S	90	58500	70200	11700		1		1
11	13-09-20	Prod 4	Apple	phone	350	370	Shop E	North	Bill T	86	30100	31820	1720		1		2		other formulas on minisheet
12	15-09-20	Prod 3	Samsung	phone	230	240	Shop B	West	David M	26	5980	6240	260		0		0		ACOUNTIFS
13	21-10-20	Prod 1	Apple	tablet	280	350	Shop C	East	John S	77	21560	26950	5390		1		1
14	28-10-20	Prod 8	Apple	laptop	420	510	Shop C	East	John S	78	32760	39780	7020		1		1		Obs:
15	29-10-20	Prod 1	Apple	tablet	280	350	Shop B	West	David M	80	22400	28000	5600		1		1		ACOUNIFS can handle array calculation in its arguments
16	14-11-20	Prod 5	HP	desktop	870	990	Shop D	West	Mike B	48	41760	47520	5760		0		1
17	18-11-20	Prod 7	Samsung	laptop	650	780	Shop D	West	Mike B	72	46800	56160	9360		1		1
18	25-11-20	Prod 3	Samsung	phone	230	240	Shop A	East	John S	71	16330	17040	710		1		1
19	17-12-20	Prod 5	HP	desktop	870	990	Shop F	South	Steve J	67	58290	66330	8040		0		0
20	28-12-20	Prod 5	HP	desktop	870	990	Shop E	North	Bill T	10	8700	9900	1200		0		0
21	03-01-21	Prod 4	Apple	phone	350	370	Shop B	West	David M	68	23800	25160	1360		1		2
22	14-01-21	Prod 3	Samsung	phone	230	240	Shop B	West	David M	52	11960	12480	520		1		1
23	20-01-21	Prod 1	Apple	tablet	280	350	Shop B	West	David M	60	16800	21000	4200		1		1
24	30-01-21	Prod 5	HP	desktop	870	990	Shop B	West	David M	85	73950	84150	10200		1		2
25	18-02-21	Prod 4	Apple	phone	350	370	Shop A	East	John S	46	16100	17020	920		0		0
26	20-02-21	Prod 2	HP	laptop	520	620	Shop A	East	John S	35	18200	21700	3500		0		1
27	28-02-21	Prod 5	HP	desktop	870	990	Shop A	East	John S	71	61770	70290	8520		1		2
28	01-03-21	Prod 6	Samsung	tablet	170	185	Shop E	North	Bill T	65	11050	12025	975		0		0
29	04-03-21	Prod 4	Apple	phone	350	370	Shop E	North	Bill T	42	14700	15540	840		0		0
30	06-03-21	Prod 3	Samsung	phone	230	240	Shop E	North	Bill T	81	18630	19440	810		0		1
31	17-03-21	Prod 4	Apple	phone	350	370	Shop B	West	David M	47	16450	17390	940		0		0
32	20-03-21	Prod 5	HP	desktop	870	990	Shop F	South	Steve J	70	60900	69300	8400		0		1
33	22-03-21	Prod 3	Samsung	phone	230	240	Shop D	West	Mike B	14	3220	3360	140		0		0
34	29-03-21	Prod 6	Samsung	tablet	170	185	Shop D	West	Mike B	63	10710	11655	945		1		1
35	09-04-21	Prod 8	Apple	laptop	420	510	Shop A	East	John S	50	21000	25500	4500		1		1
36	10-04-21	Prod 6	Samsung	tablet	170	185	Shop E	North	Bill T	31	5270	5735	465		0		1
37	10-04-21	Prod 5	HP	desktop	870	990	Shop A	East	John S	47	40890	46530	5640		0		1
38	10-04-21	Prod 7	Samsung	laptop	650	780	Shop F	South	Steve J	12	7800	9360	1560		0		1
39	12-04-21	Prod 1	Apple	tablet	280	350	Shop A	East	John S	51	14280	17850	3570		1		1
40	24-04-21	Prod 3	Samsung	phone	230	240	Shop F	South	Steve J	20	4600	4800	200		0		1
41	28-04-21	Prod 3	Samsung	phone	230	240	Shop E	North	Bill T	70	16100	16800	700		0		0
42	02-05-21	Prod 8	Apple	laptop	420	510	Shop B	West	David M	73	30660	37230	6570		0		1
43	04-05-21	Prod 3	Samsung	phone	230	240	Shop F	South	Steve J	19	4370	4560	190		0		0
44	06-05-21	Prod 5	HP	desktop	870	990	Shop D	West	Mike B	28	24360	27720	3360		0		0
45	10-05-21	Prod 3	Samsung	phone	230	240	Shop A	East	John S	38	8740	9120	380		0		0
46	11-05-21	Prod 2	HP	laptop	520	620	Shop D	West	Mike B	35	18200	21700	3500		0		0
47	12-05-21	Prod 3	Samsung	phone	230	240	Shop A	East	John S	43	9890	10320	430		0		0
48	13-05-21	Prod 5	HP	desktop	870	990	Shop E	North	Bill T	10	8700	9900	1200		0		0
49	16-05-21	Prod 3	Samsung	phone	230	240	Shop E	North	Bill T	29	6670	6960	290		0		1
50	12-06-21	Prod 2	HP	laptop	520	620	Shop F	South	Steve J	44	22880	27280	4400		0		1
51	26-06-21	Prod 8	Apple	laptop	420	510	Shop F	South	Steve J	50	21000	25500	4500		0		1
52	29-06-21	Prod 2	HP	laptop	520	620	Shop D	West	Mike B	37	19240	22940	3700		0		0
53
54	additional records																	-we can harcode any complex conditions we need
55	01-09-20	Prod 3	Samsung	phone	230	300	Shop F	South	Steve J	14	3220	4200	980					(as seen in ADVFLT with help of boolean logic)
56	01-09-20	Prod 3	Samsung	phone	230	300	Shop D	West	Mike B	89	20470	26700	6230					-if data is a table all CF behaviour updates
57	14-01-21	Prod 6	Samsung	tablet	170	240	Shop E	North	Bill T	83	14110	19920	5810					(add additional records to the bottom of the table and everything updates)
58	17-07-20	Prod 8	Apple	laptop	420	510	Shop C	East	John S	43	18060	21930	3870
59	09-04-21	Prod 3	Samsung	phone	230	300	Shop C	East	John S	80	18400	24000	5600
60	18-11-20	Prod 1	Apple	tablet	280	350	Shop B	West	David M	73	20440	25550	5110
61	16-07-20	Prod 8	Apple	laptop	420	510	Shop A	East	John S	29	12180	14790	2610
62	13-05-21	Prod 1	Apple	tablet	280	350	Shop B	West	David M	84	23520	29400	5880
63	18-01-21	Prod 3	Samsung	phone	230	300	Shop D	West	Mike B	32	7360	9600	2240
64	23-02-21	Prod 7	Samsung	laptop	650	780	Shop F	South	Steve J	75	48750	58500	9750
65
ADVCF post

Cell Formulas
Range	Formula
O1	O1	=FORMULATEXT(O3)
Q2	Q2	=FORMULATEXT(Q3)
O3:O52	O3	=ADVCF(S,{1,1,10},{">=1-08-20","<1-05-21",">=50"},{3,8,8},{"Apple","East","West"})
Q3:Q52	Q3	=O3#+ACOUNTIFS(TEXT(S[Date],"ddd"),{"Sat";"Sun"})
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:M52	Expression	=$O3	text	NO
A3:M52	Expression	=$Q3=2	text	NO

 

[Xlambda]

Task 2. For the same array, highlight the pairs Shop/Region records that contribute to the following goals: units sold over 490 units, OR revenue is over 260000 , OR net revenue over 36000.
In other words, we are happy with either of these results. If we sold over 490 units we are happy that we sold a lot of units, no matter tot rev or net rev, and so on.
We need only ASUMIFS (shop/region , shop/region , all 4 last columns with values) and a simple ADVCF with OR criteria for 3 of its columns.
Othe function on minisheet ASUMIFS

LAMBDA 10.0.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1															=ASUMIFS(NR[[Shop]:[Region]],NR[[Shop]:[Region]],NR[[uSold]:[NetRev]])
2	Date	Prod	Brand	Type	Cost	Price/u	Shop	Region	Manager	uSold	TCost	Rev	NetRev		↘	uSold	TCost	Rev	NetRev		=ADVCF(P3#,,,{1,3,4},{">490",">260000","36000"})
3	09-07-20	Prod 3	Samsung	phone	230	240	Shop C	East	John S	84	19320	20160	840			284	93190	110005	16815		0
4	14-07-20	Prod 1	Apple	tablet	280	350	Shop F	South	Steve J	85	23800	29750	5950			367	203640	236880	33240		0
5	23-07-20	Prod 6	Samsung	tablet	170	185	Shop C	East	John S	11	1870	2035	165			284	93190	110005	16815		0
6	30-07-20	Prod 8	Apple	laptop	420	510	Shop D	West	Mike B	77	32340	39270	6930			442	220430	255485	35055		0		CF formula
7	03-08-20	Prod 4	Apple	phone	350	370	Shop D	West	Mike B	68	23800	25160	1360			442	220430	255485	35055		0		AaBbCc	=$T3
8	08-08-20	Prod 2	HP	laptop	520	620	Shop C	East	John S	34	17680	21080	3400			284	93190	110005	16815		0
9	22-08-20	Prod 8	Apple	laptop	420	510	Shop E	North	Bill T	41	17220	20910	3690			465	137140	149030	11890		0
10	02-09-20	Prod 7	Samsung	laptop	650	780	Shop A	East	John S	90	58500	70200	11700			542	265700	305570	39870		1		other formulas on minisheet
11	13-09-20	Prod 4	Apple	phone	350	370	Shop E	North	Bill T	86	30100	31820	1720			465	137140	149030	11890		0		ASUMIFS
12	15-09-20	Prod 3	Samsung	phone	230	240	Shop B	West	David M	26	5980	6240	260			491	202000	231650	29650		1
13	21-10-20	Prod 1	Apple	tablet	280	350	Shop C	East	John S	77	21560	26950	5390			284	93190	110005	16815		0		Obs. A single cell formula is possible
14	28-10-20	Prod 8	Apple	laptop	420	510	Shop C	East	John S	78	32760	39780	7020			284	93190	110005	16815		0		but I left it expanded to visualize the values
15	29-10-20	Prod 1	Apple	tablet	280	350	Shop B	West	David M	80	22400	28000	5600			491	202000	231650	29650		1
16	14-11-20	Prod 5	HP	desktop	870	990	Shop D	West	Mike B	48	41760	47520	5760			442	220430	255485	35055		0
17	18-11-20	Prod 7	Samsung	laptop	650	780	Shop D	West	Mike B	72	46800	56160	9360			442	220430	255485	35055		0
18	25-11-20	Prod 3	Samsung	phone	230	240	Shop A	East	John S	71	16330	17040	710			542	265700	305570	39870		1
19	17-12-20	Prod 5	HP	desktop	870	990	Shop F	South	Steve J	67	58290	66330	8040			367	203640	236880	33240		0
20	28-12-20	Prod 5	HP	desktop	870	990	Shop E	North	Bill T	10	8700	9900	1200			465	137140	149030	11890		0
21	03-01-21	Prod 4	Apple	phone	350	370	Shop B	West	David M	68	23800	25160	1360			491	202000	231650	29650		1
22	14-01-21	Prod 3	Samsung	phone	230	240	Shop B	West	David M	52	11960	12480	520			491	202000	231650	29650		1
23	20-01-21	Prod 1	Apple	tablet	280	350	Shop B	West	David M	60	16800	21000	4200			491	202000	231650	29650		1
24	30-01-21	Prod 5	HP	desktop	870	990	Shop B	West	David M	85	73950	84150	10200			491	202000	231650	29650		1
25	18-02-21	Prod 4	Apple	phone	350	370	Shop A	East	John S	46	16100	17020	920			542	265700	305570	39870		1
26	20-02-21	Prod 2	HP	laptop	520	620	Shop A	East	John S	35	18200	21700	3500			542	265700	305570	39870		1
27	28-02-21	Prod 5	HP	desktop	870	990	Shop A	East	John S	71	61770	70290	8520			542	265700	305570	39870		1
28	01-03-21	Prod 6	Samsung	tablet	170	185	Shop E	North	Bill T	65	11050	12025	975			465	137140	149030	11890		0
29	04-03-21	Prod 4	Apple	phone	350	370	Shop E	North	Bill T	42	14700	15540	840			465	137140	149030	11890		0
30	06-03-21	Prod 3	Samsung	phone	230	240	Shop E	North	Bill T	81	18630	19440	810			465	137140	149030	11890		0
31	17-03-21	Prod 4	Apple	phone	350	370	Shop B	West	David M	47	16450	17390	940			491	202000	231650	29650		1
32	20-03-21	Prod 5	HP	desktop	870	990	Shop F	South	Steve J	70	60900	69300	8400			367	203640	236880	33240		0
33	22-03-21	Prod 3	Samsung	phone	230	240	Shop D	West	Mike B	14	3220	3360	140			442	220430	255485	35055		0
34	29-03-21	Prod 6	Samsung	tablet	170	185	Shop D	West	Mike B	63	10710	11655	945			442	220430	255485	35055		0
35	09-04-21	Prod 8	Apple	laptop	420	510	Shop A	East	John S	50	21000	25500	4500			542	265700	305570	39870		1
36	10-04-21	Prod 6	Samsung	tablet	170	185	Shop E	North	Bill T	31	5270	5735	465			465	137140	149030	11890		0
37	10-04-21	Prod 5	HP	desktop	870	990	Shop A	East	John S	47	40890	46530	5640			542	265700	305570	39870		1
38	10-04-21	Prod 7	Samsung	laptop	650	780	Shop F	South	Steve J	12	7800	9360	1560			367	203640	236880	33240		0
39	12-04-21	Prod 1	Apple	tablet	280	350	Shop A	East	John S	51	14280	17850	3570			542	265700	305570	39870		1
40	24-04-21	Prod 3	Samsung	phone	230	240	Shop F	South	Steve J	20	4600	4800	200			367	203640	236880	33240		0
41	28-04-21	Prod 3	Samsung	phone	230	240	Shop E	North	Bill T	70	16100	16800	700			465	137140	149030	11890		0
42	02-05-21	Prod 8	Apple	laptop	420	510	Shop B	West	David M	73	30660	37230	6570			491	202000	231650	29650		1
43	04-05-21	Prod 3	Samsung	phone	230	240	Shop F	South	Steve J	19	4370	4560	190			367	203640	236880	33240		0
44	06-05-21	Prod 5	HP	desktop	870	990	Shop D	West	Mike B	28	24360	27720	3360			442	220430	255485	35055		0
45	10-05-21	Prod 3	Samsung	phone	230	240	Shop A	East	John S	38	8740	9120	380			542	265700	305570	39870		1
46	11-05-21	Prod 2	HP	laptop	520	620	Shop D	West	Mike B	35	18200	21700	3500			442	220430	255485	35055		0
47	12-05-21	Prod 3	Samsung	phone	230	240	Shop A	East	John S	43	9890	10320	430			542	265700	305570	39870		1
48	13-05-21	Prod 5	HP	desktop	870	990	Shop E	North	Bill T	10	8700	9900	1200			465	137140	149030	11890		0
49	16-05-21	Prod 3	Samsung	phone	230	240	Shop E	North	Bill T	29	6670	6960	290			465	137140	149030	11890		0
50	12-06-21	Prod 2	HP	laptop	520	620	Shop F	South	Steve J	44	22880	27280	4400			367	203640	236880	33240		0
51	26-06-21	Prod 8	Apple	laptop	420	510	Shop F	South	Steve J	50	21000	25500	4500			367	203640	236880	33240		0
52	29-06-21	Prod 2	HP	laptop	520	620	Shop D	West	Mike B	37	19240	22940	3700			442	220430	255485	35055		0
53
ADVCF post 2

Cell Formulas
Range	Formula
O1	O1	=FORMULATEXT(P3)
U2	U2	=FORMULATEXT(U3)
P3:S52	P3	=ASUMIFS(NR[[Shop]:[Region]],NR[[Shop]:[Region]],NR[[uSold]:[NetRev]])
U3:U52	U3	=ADVCF(P3#,,,{1,3,4},{">490",">260000","36000"})
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:M52	Expression	=$U3	text	YES

 

[Xlambda]

Array/Table "making of". Functions used : ARANDOM , ACLMJOIN , ACLMSPLIT

LAMBDA 10.0.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	create a random proper data array using this data structure									joining initial data			random data, consistent with initial data			splitting random data, and adding dates, calc.columns, for a proper consistent data set
2	Product	Brand	Type	Cost	Price/u	Shop	Region	Manager		=ACLMJOIN(ACLMJOIN(A3:H10,1,5,,-1,"|"),2,,,,"|")			=ARANDOM(J3#,50)			Date	Product	Brand	Type	Cost	Price/u	Shop	Region	Manager	uSold	TCost	Rev	NetRev
3	Prod 3	Samsung	phone	230	240	Shop C	East	John S		Prod 3|Samsung|phone|230|240	Shop C|East|John S		Prod 7|Samsung|laptop|650|780	Shop C|East|John S		13-09-20	Prod 7	Samsung	laptop	650	780	Shop C	East	John S	52	33800	40560	6760
4	Prod 6	Samsung	tablet	170	185	Shop B	West	David M		Prod 6|Samsung|tablet|170|185	Shop B|West|David M		Prod 4|Apple|phone|350|370	Shop C|East|John S		03-02-21	Prod 4	Apple	phone	350	370	Shop C	East	John S	43	15050	15910	860
5	Prod 4	Apple	phone	350	370	Shop A	East	John S		Prod 4|Apple|phone|350|370	Shop A|East|John S		Prod 1|Apple|tablet|280|350	Shop D|West|Mike B		01-07-21	Prod 1	Apple	tablet	280	350	Shop D	West	Mike B	34	9520	11900	2380
6	Prod 1	Apple	tablet	280	350	Shop D	West	Mike B		Prod 1|Apple|tablet|280|350	Shop D|West|Mike B		Prod 7|Samsung|laptop|650|780	Shop C|East|John S		30-06-21	Prod 7	Samsung	laptop	650	780	Shop C	East	John S	61	39650	47580	7930
7	Prod 7	Samsung	laptop	650	780	Shop E	North	Bill T		Prod 7|Samsung|laptop|650|780	Shop E|North|Bill T		Prod 6|Samsung|tablet|170|185	Shop C|East|John S		25-11-20	Prod 6	Samsung	tablet	170	185	Shop C	East	John S	16	2720	2960	240
8	Prod 8	Apple	laptop	420	510	Shop F	South	Steve J		Prod 8|Apple|laptop|420|510	Shop F|South|Steve J		Prod 7|Samsung|laptop|650|780	Shop E|North|Bill T		19-10-20	Prod 7	Samsung	laptop	650	780	Shop E	North	Bill T	49	31850	38220	6370
9	Prod 2	HP	laptop	520	620					Prod 2|HP|laptop|520|620			Prod 7|Samsung|laptop|650|780	Shop C|East|John S		01-07-20	Prod 7	Samsung	laptop	650	780	Shop C	East	John S	89	57850	69420	11570
10	Prod 5	HP	desktop	870	990					Prod 5|HP|desktop|870|990			Prod 2|HP|laptop|520|620	Shop C|East|John S		09-04-21	Prod 2	HP	laptop	520	620	Shop C	East	John S	19	9880	11780	1900
11													Prod 8|Apple|laptop|420|510	Shop D|West|Mike B		27-10-20	Prod 8	Apple	laptop	420	510	Shop D	West	Mike B	80	33600	40800	7200
12													Prod 5|HP|desktop|870|990	Shop C|East|John S		20-03-21	Prod 5	HP	desktop	870	990	Shop C	East	John S	51	44370	50490	6120
13													Prod 1|Apple|tablet|280|350	Shop A|East|John S		07-05-21	Prod 1	Apple	tablet	280	350	Shop A	East	John S	53	14840	18550	3710
14						other formulas on minisheet							Prod 5|HP|desktop|870|990	Shop E|North|Bill T		04-01-21	Prod 5	HP	desktop	870	990	Shop E	North	Bill T	57	49590	56430	6840
15						ACLMJOIN							Prod 7|Samsung|laptop|650|780	Shop A|East|John S		19-11-20	Prod 7	Samsung	laptop	650	780	Shop A	East	John S	53	34450	41340	6890
16						ARANDOM							Prod 1|Apple|tablet|280|350	Shop D|West|Mike B		07-12-20	Prod 1	Apple	tablet	280	350	Shop D	West	Mike B	33	9240	11550	2310
17						ACMSPLIT							Prod 4|Apple|phone|350|370	Shop E|North|Bill T		07-12-20	Prod 4	Apple	phone	350	370	Shop E	North	Bill T	54	18900	19980	1080
18													Prod 4|Apple|phone|350|370	Shop E|North|Bill T		23-04-21	Prod 4	Apple	phone	350	370	Shop E	North	Bill T	28	9800	10360	560
19													Prod 8|Apple|laptop|420|510	Shop B|West|David M		30-06-21	Prod 8	Apple	laptop	420	510	Shop B	West	David M	55	23100	28050	4950
20													Prod 7|Samsung|laptop|650|780	Shop F|South|Steve J		04-03-21	Prod 7	Samsung	laptop	650	780	Shop F	South	Steve J	54	35100	42120	7020
21													Prod 6|Samsung|tablet|170|185	Shop C|East|John S		24-12-20	Prod 6	Samsung	tablet	170	185	Shop C	East	John S	70	11900	12950	1050
22													Prod 3|Samsung|phone|230|240	Shop F|South|Steve J		07-07-20	Prod 3	Samsung	phone	230	240	Shop F	South	Steve J	24	5520	5760	240
23													Prod 4|Apple|phone|350|370	Shop D|West|Mike B		30-06-21	Prod 4	Apple	phone	350	370	Shop D	West	Mike B	65	22750	24050	1300
24													Prod 5|HP|desktop|870|990	Shop D|West|Mike B		13-01-21	Prod 5	HP	desktop	870	990	Shop D	West	Mike B	32	27840	31680	3840
25													Prod 5|HP|desktop|870|990	Shop E|North|Bill T		03-03-21	Prod 5	HP	desktop	870	990	Shop E	North	Bill T	77	66990	76230	9240
26													Prod 8|Apple|laptop|420|510	Shop C|East|John S		06-10-20	Prod 8	Apple	laptop	420	510	Shop C	East	John S	66	27720	33660	5940
27													Prod 2|HP|laptop|520|620	Shop A|East|John S		06-12-20	Prod 2	HP	laptop	520	620	Shop A	East	John S	89	46280	55180	8900
28													Prod 5|HP|desktop|870|990	Shop A|East|John S		27-01-21	Prod 5	HP	desktop	870	990	Shop A	East	John S	76	66120	75240	9120
29													Prod 1|Apple|tablet|280|350	Shop D|West|Mike B		03-02-21	Prod 1	Apple	tablet	280	350	Shop D	West	Mike B	42	11760	14700	2940
30													Prod 7|Samsung|laptop|650|780	Shop D|West|Mike B		24-08-20	Prod 7	Samsung	laptop	650	780	Shop D	West	Mike B	77	50050	60060	10010
31													Prod 2|HP|laptop|520|620	Shop A|East|John S		25-11-20	Prod 2	HP	laptop	520	620	Shop A	East	John S	83	43160	51460	8300
32													Prod 2|HP|laptop|520|620	Shop B|West|David M		27-02-21	Prod 2	HP	laptop	520	620	Shop B	West	David M	74	38480	45880	7400
33													Prod 8|Apple|laptop|420|510	Shop A|East|John S		17-02-21	Prod 8	Apple	laptop	420	510	Shop A	East	John S	60	25200	30600	5400
34													Prod 2|HP|laptop|520|620	Shop B|West|David M		04-05-21	Prod 2	HP	laptop	520	620	Shop B	West	David M	33	17160	20460	3300
35													Prod 6|Samsung|tablet|170|185	Shop C|East|John S		07-10-20	Prod 6	Samsung	tablet	170	185	Shop C	East	John S	82	13940	15170	1230
36													Prod 2|HP|laptop|520|620	Shop A|East|John S		02-09-20	Prod 2	HP	laptop	520	620	Shop A	East	John S	30	15600	18600	3000
37													Prod 7|Samsung|laptop|650|780	Shop A|East|John S		21-10-20	Prod 7	Samsung	laptop	650	780	Shop A	East	John S	87	56550	67860	11310
38													Prod 6|Samsung|tablet|170|185	Shop C|East|John S		08-05-21	Prod 6	Samsung	tablet	170	185	Shop C	East	John S	39	6630	7215	585
39													Prod 1|Apple|tablet|280|350	Shop F|South|Steve J		12-06-21	Prod 1	Apple	tablet	280	350	Shop F	South	Steve J	27	7560	9450	1890
40													Prod 8|Apple|laptop|420|510	Shop B|West|David M		21-10-20	Prod 8	Apple	laptop	420	510	Shop B	West	David M	28	11760	14280	2520
41													Prod 6|Samsung|tablet|170|185	Shop E|North|Bill T		05-11-20	Prod 6	Samsung	tablet	170	185	Shop E	North	Bill T	28	4760	5180	420
42													Prod 8|Apple|laptop|420|510	Shop B|West|David M		05-09-20	Prod 8	Apple	laptop	420	510	Shop B	West	David M	46	19320	23460	4140
43													Prod 2|HP|laptop|520|620	Shop D|West|Mike B		15-11-20	Prod 2	HP	laptop	520	620	Shop D	West	Mike B	22	11440	13640	2200
44													Prod 8|Apple|laptop|420|510	Shop F|South|Steve J		09-08-20	Prod 8	Apple	laptop	420	510	Shop F	South	Steve J	54	22680	27540	4860
45													Prod 8|Apple|laptop|420|510	Shop B|West|David M		13-09-20	Prod 8	Apple	laptop	420	510	Shop B	West	David M	89	37380	45390	8010
46													Prod 1|Apple|tablet|280|350	Shop C|East|John S		01-02-21	Prod 1	Apple	tablet	280	350	Shop C	East	John S	51	14280	17850	3570
47													Prod 8|Apple|laptop|420|510	Shop B|West|David M		12-09-20	Prod 8	Apple	laptop	420	510	Shop B	West	David M	14	5880	7140	1260
48													Prod 8|Apple|laptop|420|510	Shop C|East|John S		26-12-20	Prod 8	Apple	laptop	420	510	Shop C	East	John S	21	8820	10710	1890
49													Prod 5|HP|desktop|870|990	Shop E|North|Bill T		06-12-20	Prod 5	HP	desktop	870	990	Shop E	North	Bill T	62	53940	61380	7440
50													Prod 7|Samsung|laptop|650|780	Shop B|West|David M		09-12-20	Prod 7	Samsung	laptop	650	780	Shop B	West	David M	32	20800	24960	4160
51													Prod 7|Samsung|laptop|650|780	Shop F|South|Steve J		17-01-21	Prod 7	Samsung	laptop	650	780	Shop F	South	Steve J	85	55250	66300	11050
52													Prod 2|HP|laptop|520|620	Shop D|West|Mike B		28-04-21	Prod 2	HP	laptop	520	620	Shop D	West	Mike B	89	46280	55180	8900
53																↑	↑								↑	↑	↑	↑
54																↑	=ACLMSPLIT(ACLMSPLIT(M3#,1,"|"),,"|")					=RANDARRAY(50,,10,90,1)				↑	↑	↑
55																=RANDARRAY(50,,"1-07-20","1-07-21",1)									=T3:T52*Y3#		↑	↑
56																										=Y3#*U3:U52		↑
57																											=AA3#-Z3#
58
ADVCF data tbl

Cell Formulas
Range	Formula
J2,M2	J2	=FORMULATEXT(J3)
J3:K10	J3	=ACLMJOIN(ACLMJOIN(A3:H10,1,5,,-1,"|"),2,,,,"|")
M3:N52	M3	=ARANDOM(J3#,50)
P3:P52	P3	=RANDARRAY(50,,"1-07-20","1-07-21",1)
Q3:X52	Q3	=ACLMSPLIT(ACLMSPLIT(M3#,1,"|"),,"|")
Y3:Y52	Y3	=RANDARRAY(50,,10,90,1)
Z3:Z52	Z3	=T3:T52*Y3#
AA3:AA52	AA3	=Y3#*U3:U52
AB3:AB52	AB3	=AA3#-Z3#
Q54	Q54	=FORMULATEXT(Q3)
V54	V54	=FORMULATEXT(Y3)
P55	P55	=FORMULATEXT(P3)
Y55	Y55	=FORMULATEXT(Z3)
Z56	Z56	=FORMULATEXT(AA3)
AA57	AA57	=FORMULATEXT(AB3)
Dynamic array formulas.