CFMATH

[schardt679]

CFMATH is used for Conditional Formatting and returns TRUE or FALSE given a function number (1-16) related to math and a cell reference.

Excludes blanks but not 0's. Functions are 1☛Max, 2☛Min, 3☛Large, 4☛Small, 5☛Greater Than, 6☛Less Than, 7☛Greater/Equal, 8☛Less/Equal, 9☛Between (Exclusive), 10☛Between (Inclusive), 11☛Equal, 12☛Not Equal, 13☛Mode, 14☛Duplicates, 15☛Count_Greater/Equal, 16☛Count_Less/Equal
LARGE, SMALL, & EQUAL handle arrays of values. MODE handles multi-mode ranges.

'=LAMBDA(Func_Num,Cell_Ref,Range_Ref,Value1_k,Value2,
      LET(Func, Func_Num,    Ref, Cell_Ref,
             Range, Range_Ref,    Val_1, Value1_k,    Val_2, Value2,
             RangeUni, UNIQUE(Range),    RefCt, COUNTIF(Range, Ref),
             MX, Ref=MAX(Range),    MN, Ref=MIN(Range),
             LG, OR(Ref=LARGE(RangeUni, Val_1)),
             SM, OR(Ref=SMALL(RangeUni, Val_1)),
             GT, Ref>Val_1,    GEQ, Ref>=Val_1,
             LT_1, Ref<Val_1,    LEQ_1, Ref<=Val_1,
             LT_2, Ref<Val_2,    LEQ_2, Ref<=Val_2,
             BT_Ex, AND(GT, LT_2),    BT_In, AND(GEQ, LEQ_2),
             EQ, OR(Ref=Val_1),     EQ_N, NOT(EQ),
             MD, OR(IFERROR(Ref=MODE.MULT(Range), 0)),
             Dup, RefCt>1,    CT_GEQ, RefCt>=Val_1,    CT_LEQ, RefCt<=Val_1,
             F1_8, SWITCH(Func,  1, MX,  2, MN,  3, LG,  4, SM,  5, GT,  6, LT_1,  7, GEQ,  8, LEQ_1,  0),
             F9_16, SWITCH(Func,  9, BT_Ex,  10, BT_In,  11, EQ,  12, EQ_N,  13, MD,  14, Dup,  15, CT_GEQ,  16, CT_LEQ,  0),
             Result, IF(--(F1_8)=1, F1_8, F9_16),
             Return, IF(Ref="", 0, Result),
             Return
      )
  )

LAMBDA Examples.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1		CFMATH
2
3		Sample Data					Conditional Formatting Colors
4		Max	Min	Large	Small		Func_Num	Function	Value1_ k
5		91	91	91	91		1	Max (MX)
6		59	59	59	59		2	Min (MN)
7		72	72	72	72		3	Large (LG)	2	3
8		97	97	97	97		4	Small (SM)	2
9		80	80	80	80
10		60	60	60	60
11		82	82	82	82
12
13
14		Sample Data					Conditional Formatting Colors
15		Greater Than	Less Than	Between (Exclusive)			Func_Num	Function	Value1_k	Value2
16		91	91	91			5	Greater Than (GT)	85
17		85	85	85			6	Less Than (LT)	75
18		72	75	72			9	Between (Exclusive) (BTW_Ex)	80	89
19		97	97	97
20		80	80	80
21		60	60	60
22		82	82	82
23
24
25		Sample Data					Conditional Formatting Colors
26		Greater/Equal	Less/Equal	Between (Inclusive)			Func_Num	Function	Value1_k	Value2
27		91	91	91			7	Greater/Equal (GEQ)	85
28		85	85	85			8	Less/Equal (LEQ)	75
29		72	75	72			10	Between (Inclusive) (BTW_In)	80	89
30		97	97	97
31		80	80	80
32		60	60	60
33		82	82	82
34
35
36		Sample Data					Conditional Formatting Colors
37		Equal	Not Equal				Func_Num	Function	Value1_k
38		100	100				11	Equal (EQ)	90	100
39		78	78				12	Not Equal (EQ_N)	90
40		82	82
41		90	90
42		77	77
43		90	90
44		85	85
45
46
47		Sample Data					Conditional Formatting Colors
48		Mode	Duplicates				Func_Num	Function
49		80	80				13	Mode (MD)
50		91	91				14	Duplicates (DUP)
51		100	100
52		80	80
53		77	77
54		80	80
55		100	100
56
57
58		Sample Data					Conditional Formatting Colors
59		Count, Great/Equal	Count, Less/Equal				Func_Num	Function	Value1_k
60		80	80				15	Count, Great/Equal (CT_GEQ)	3
61		90	90				16	Count, Less/Equal (CT_LEQ)	1
62		100	100
63		80	80
64		100	100
65		80	80
66		70	70
67
CFMATH

Cell Formulas
Range	Formula
I7:J7	I7	={2,3}
I38:J38	I38	={90,100}
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:B11	Expression	=CFMATH(1, B5, B$5:B$11,,)	text	NO
C5:C11	Expression	=CFMATH(2, C5,C$5:C$11,,)	text	NO
D5:D11	Expression	=CFMATH(3, D5, D$5:D$11, $I$7#,)	text	NO
E5:E11	Expression	=CFMATH(4, E5, E$5:E$11, $I$8,)	text	NO
B16:B22	Expression	=CFMATH(5, B16, B$16:B$22, $I$16,)	text	NO
C16:C22	Expression	=CFMATH(6, C16,C$16:C$22, $I$17,)	text	NO
B27:B33	Expression	=CFMATH(7, B27, B$27:B$33, $I$27,)	text	NO
C27:C33	Expression	=CFMATH(8, C27,C$27:C$33, $I$28,)	text	NO
D16:D22	Expression	=CFMATH(9, D16, D$16:D$22, $I$18, $J$18)	text	NO
D27:D33	Expression	=CFMATH(10, D27, D$27:D$33, $I$29, $J$29)	text	NO
B38:B44	Expression	=CFMATH(11, B38,, $I$38#,)	text	NO
C38:C44	Expression	=CFMATH(12, C38,, $I$39,)	text	NO
B49:B55	Expression	=CFMATH(13, B49, B$49:B$55,,)	text	NO
C49:C55	Expression	=CFMATH(14, C49, C$49:C$55,,)	text	NO
B60:B66	Expression	=CFMATH(15, B60, B$60:B$66, $I$60,)	text	NO
C60:C66	Expression	=CFMATH(16, C60, C$60:C$66, $I$61,)	text	NO

 

[schardt679]

Value1_k and Value2 can be relative or mixed references too depending on the situation.