Formula errors when nested into larger formula but not when calculated alone?

[jhall_cb]

Please god someone give me some ideas because at the moment it looks like Excel is just screwing with me...

I haven't posted on this account before but no stranger to Excel, VBA or Formulas. Running Windows 10, Office 365. So, I have a biiig horrible formula with lots of things nested all over the place (See the horrendous code tag at the bottom of the post). Within said beast is the nested formula:

OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(F20,,1)),4),"1","")&MATCH(OFFSET(F20,,-1),E$1:E$372,0)),1,,COUNTIF(E$1:E$372,OFFSET(F20,,-1))-1)=0

which produces a #VALUE error. Evaluating the first section produces a single cell offset range with a value of 0:

{0}=0

. Evaluating this returns TRUE, happy days. So why does the whole section error? I have tried checking data types, checking for text, wrapping both in a NUMBERVALUE function, all still error as a group but evaluate separately as expected.

To compound my confusion even further if I copy the above formula from its enormous nested parent and run in a separate cell it returns TRUE as it should in the cell value but returns a #VALUE error on the formula bar evaluation??

Goal is to return a Boolean TRUE/FALSE for a complex conditional formatting rule. Minisheet below of the data in question below, based on a PivotTable. Any ideas would be greatly appreciated.

Parent Formula:

=IF(AND(F3<>0,OFFSET(F3,-1,)<>F3),IF(COUNTIFS(OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(F3,,1)),4),"1",0)&MATCH(OFFSET(F3,,-1),E$1:E$372,0)),1,,COUNTIF(E$1:E$372,OFFSET(F3,,-1))-1),0,OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(F3,,3)),4),"1",0)&MATCH(OFFSET(F3,,-1),E$1:E$372,0)),1,,COUNTIF(E$1:E$372,OFFSET(F3,,-1))-1),"<7")<=2,OR(OFFSET(F3,,COLUMN($K$2)-COLUMN(F3))=SMALL(IF(OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(F3,,1)),4),"1",0)&MATCH(OFFSET(F3,,-1),E$1:E$372,0)),1,,COUNTIF(E$1:E$372,OFFSET(F3,,-1))-1)=0,OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(F3,,COLUMN($K$2)-COLUMN(F3))),4),"1",0)&MATCH(OFFSET(F3,,-1),E$1:E$372,0)),1,,COUNTIF(E$1:E$372,OFFSET(F3,,-1))-1)),1),OFFSET(F3,,COLUMN($K$2)-COLUMN(F3))=SMALL(IF(OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(F3,,1)),4),"1",0)&MATCH(OFFSET(F3,,-1),E$1:E$372,0)),1,,COUNTIF(E$1:E$372,OFFSET(F3,,-1))-1)=0,OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(F3,,COLUMN($K$2)-COLUMN(F3))),4),"1",0)&MATCH(OFFSET(F3,,-1),E$1:E$372,0)),1,,COUNTIF(E$1:E$372,OFFSET(F3,,-1))-1)),2)),IF(COUNTIFS(OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(F3,,1)),4),"1",0)&MATCH(OFFSET(F3,,-1),E$1:E$372,0)),1,,COUNTIF(E$1:E$372,OFFSET(F3,,-1))-1),0,OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(F3,,3)),4),"1",0)&MATCH(OFFSET(F3,,-1),E$1:E$372,0)),1,,COUNTIF(E$1:E$372,OFFSET(F3,,-1))-1),"<7")<=3,OR(OFFSET(F3,,COLUMN($K$2)-COLUMN(F3))=SMALL(IF(OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(F3,,1)),4),"1",0)&MATCH(OFFSET(F3,,-1),E$1:E$372,0)),1,,COUNTIF(E$1:E$372,OFFSET(F3,,-1))-1)=0,OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(F3,,COLUMN($K$2)-COLUMN(F3))),4),"1",0)&MATCH(OFFSET(F3,,-1),E$1:E$372,0)),1,,COUNTIF(E$1:E$372,OFFSET(F3,,-1))-1)),1),OFFSET(F3,,COLUMN($K$2)-COLUMN(F3))=SMALL(IF(OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(F3,,1)),4),"1",0)&MATCH(OFFSET(F3,,-1),E$1:E$372,0)),1,,COUNTIF(E$1:E$372,OFFSET(F3,,-1))-1)=0,OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(F3,,COLUMN($K$2)-COLUMN(F3))),4),"1",0)&MATCH(OFFSET(F3,,-1),E$1:E$372,0)),1,,COUNTIF(E$1:E$372,OFFSET(F3,,-1))-1)),2),OFFSET(F3,,COLUMN($K$2)-COLUMN(F3))=SMALL(IF(OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(F3,,1)),4),"1",0)&MATCH(OFFSET(F3,,-1),E$1:E$372,0)),1,,COUNTIF(E$1:E$372,OFFSET(F3,,-1))-1)=0,OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(F3,,COLUMN($K$2)-COLUMN(F3))),4),"1",0)&MATCH(OFFSET(F3,,-1),E$1:E$372,0)),1,,COUNTIF(E$1:E$372,OFFSET(F3,,-1))-1)),3)),IF(COUNTIFS(OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(F3,,1)),4),"1",0)&MATCH(OFFSET(F3,,-1),E$1:E$372,0)),1,,COUNTIF(E$1:E$372,OFFSET(F3,,-1))-1),0,OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(F3,,3)),4),"1",0)&MATCH(OFFSET(F3,,-1),E$1:E$372,0)),1,,COUNTIF(E$1:E$372,OFFSET(F3,,-1))-1),"<7")<=4,OR(OFFSET(F3,,COLUMN($K$2)-COLUMN(F3))=SMALL(IF(OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(F3,,1)),4),"1",0)&MATCH(OFFSET(F3,,-1),E$1:E$372,0)),1,,COUNTIF(E$1:E$372,OFFSET(F3,,-1))-1)=0,OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(F3,,COLUMN($K$2)-COLUMN(F3))),4),"1",0)&MATCH(OFFSET(F3,,-1),E$1:E$372,0)),1,,COUNTIF(E$1:E$372,OFFSET(F3,,-1))-1)),1),OFFSET(F3,,COLUMN($K$2)-COLUMN(F3))=SMALL(IF(OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(F3,,1)),4),"1",0)&MATCH(OFFSET(F3,,-1),E$1:E$372,0)),1,,COUNTIF(E$1:E$372,OFFSET(F3,,-1))-1)=0,OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(F3,,COLUMN($K$2)-COLUMN(F3))),4),"1",0)&MATCH(OFFSET(F3,,-1),E$1:E$372,0)),1,,COUNTIF(E$1:E$372,OFFSET(F3,,-1))-1)),2),OFFSET(F3,,COLUMN($K$2)-COLUMN(F3))=SMALL(IF(OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(F3,,1)),4),"1",0)&MATCH(OFFSET(F3,,-1),E$1:E$372,0)),1,,COUNTIF(E$1:E$372,OFFSET(F3,,-1))-1)=0,OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(F3,,COLUMN($K$2)-COLUMN(F3))),4),"1",0)&MATCH(OFFSET(F3,,-1),E$1:E$372,0)),1,,COUNTIF(E$1:E$372,OFFSET(F3,,-1))-1)),3),OFFSET(F3,,COLUMN($K$2)-COLUMN(F3))=SMALL(IF(OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(F3,,1)),4),"1",0)&MATCH(OFFSET(F3,,-1),E$1:E$372,0)),1,,COUNTIF(E$1:E$372,OFFSET(F3,,-1))-1)=0,OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(F3,,COLUMN($K$2)-COLUMN(F3))),4),"1",0)&MATCH(OFFSET(F3,,-1),E$1:E$372,0)),1,,COUNTIF(E$1:E$372,OFFSET(F3,,-1))-1)),4)),FALSE))),FALSE)

Minisheet:

OrgMapping Simulator 2.xlsm
	E	F	G	H	I	J	K
1
2	L4	L5	L6	L7	L8	L9	Sum of Headcount
3							0
4							0
5							0
6							0
7							0
8							2
9							0
10							0
11							0
12	H&G UK - HR - Employee Development BL - 227033917						0
13	H&G UK - HR - Employee Development Team - 227033915						0
14	H&G UK - HR - Employee Development Team - 227033915	H&G UK - HR - CIS & EOS Development - 227046280					0
15	H&G UK - HR - Employee Development Team - 227033915	H&G UK - HR - Employee Development Compliance - 228080540					0
16	H&G UK - HR - Employee Development Team - 227033915	H&G UK - HR - Employee Development Trainers - 228080538					0
17							5
18							0
19	H&G UK - HR - Lynne Oates - 228090143						0
20	H&G UK - HR - Lynne Oates - 228090143	LHR HR-BP - 227033914					2
21	H&G UK - HR - Roshnee Khandelwal - 228090139						0
22	H&G UK - HR - Roshnee Khandelwal - 228090139	EMA HR Services - 227033916					2
23	H&G UK - HR - Roshnee Khandelwal - 228090139	H&G UK - HR - EMA HR - 228080546					4
24	H&G UK - HR - Roshnee Khandelwal - 228090139	HR Admin & Passes - 228080547					5
25	H&G UK - HR - Roshnee Khandelwal - 228090139	HR Dept. Occ Health BL - 227033910					0
26	H&G UK - HR - Rowena Catling - 228090142						0
27	H&G UK - HR - Rowena Catling - 228090142	HR Recruitment - 228080660					4
28							0
Piviot

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:J502	Expression	=AND(MOD($A3+1,2)=0,B3=B2,ROW()<MATCH("Grand Total",$B:$B,0))	text	NO
B3:J502	Expression	=MOD($A3+1,2)=0	text	NO
K3:K373	Expression	=MOD($A3+1,2)=0	text	NO
B3:J502	Expression	=AND(MOD($A3,2)=0,B3=B2,ROW()<MATCH("Grand Total",$B:$B,0))	text	NO
B3:J502	Expression	=MOD($A3,2)=0	text	NO
K3:K373	Expression	=MOD($A3,2)=0	text	NO

 

[StephenCrump]

Yep, got it, thanks.

I'll try to post something back later in the weekend.

 

[StephenCrump]

Only lightly tested, but I think it works:

	A	B	C	D	E	F	G	H	I
1					MinN	7			Level
2									5
3	Level 1	Level 2	Level 3	Level 4	Level 5	Type	Responses		CheckCell
4	TOP					Parent	99		FALSE
5	TOP	A				Parent	38		FALSE
6	TOP	A	A.1			Parent	21		FALSE
7	TOP	A	A.1	A.1.1		Parent	21		FALSE
8	TOP	A	A.1	A.1.1	A.1.1.1	Child	10		FALSE
9	TOP	A	A.1	A.1.1	A.1.1.2	Child	10		TRUE
10	TOP	A	A.1	A.1.1	A.1.1.3	Child	1		TRUE
11	TOP	A	A.2			Parent	17		FALSE
12	TOP	A	A.2	A.2.1		Parent	2		FALSE
13	TOP	A	A.2	A.2.1	A2.1.1	Child	2		TRUE
14	TOP	A	A.2	A2.2		Parent	15		FALSE
15	TOP	A	A.2	A2.2	A.2.1.2	Child	10		TRUE
16	TOP	A	A.2	A2.2	A,2.1.3	Child	5		TRUE
17	TOP	B				Parent	58		FALSE
18	TOP	B	B.1			Parent	42		FALSE
19	TOP	B	B.1	B1.1		Parent	30		FALSE
20	TOP	B	B.1	B1.1	B1.1.1	Child	10		FALSE
21	TOP	B	B.1	B1.1	B1.1.2	Child	10		FALSE
22	TOP	B	B.1	B1.1	B1.1.3	Child	10		FALSE
23	TOP	B	B.1	B1.2		Parent	12		FALSE
24	TOP	B	B.1	B1.2	B1.2.1	Child	12		FALSE
25	TOP	B	B.2			Parent	6		FALSE
26	TOP	B	B.2	B2.1		Parent	6		FALSE
27	TOP	B	B.2	B2.1	B2.1.1	Child	6		TRUE
28	TOP	B	B.3			Parent	10		FALSE
29	TOP	B	B.3	B3.1		Parent	10		FALSE
30	TOP	B	B.3	B3.1	B3.1.1	Child	10		FALSE
31	TOP	C				Parent	3		FALSE
32	TOP	C	C.1			Parent	3		FALSE
33	TOP	C	C.1	C1.1		Parent	3		FALSE
34	TOP	C	C.1	C1.1	C1.1.1	Child	3		TRUE
35
Sheet1

Cell Formulas
Range	Formula
I4:I34	I4	=CheckCell($A$4:$G$34,MinN,I$2,ROWS(I$4:I4))

Named Ranges
Name	Refers To	Cells
MinN	=Sheet5!$F$1	I4:I34

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I4:J34	Cell Value	=TRUE	text	NO
A4:J34	Expression	=COUNTBLANK($A4:$G4)=COLUMNS($A4:$G4)-2-$I$2	text	NO

CheckCell: =LAMBDA(data,MinN,Level,RowNo,LET(cols,COLUMNS(data),r,INDEX(data,RowNo,),No,IF((BYROW(data,LAMBDA(r,COUNTBLANK(r)))=cols-2-Level)*BYROW(INDEX(data,,1):INDEX(data,,Level-1)=INDEX(r,1):INDEX(r,Level-1),LAMBDA(rw,AND(rw))),(INDEX(data,,cols)-SEQUENCE(ROWS(data))/10000)),s,SUM((No>0)*(No<=MinN-1)),IF(s>0,INDEX(No,RowNo)<=IFERROR(SMALL(No,s+(s=1)),MIN(No)))))

I've conditionally formatted to show the level being tested, to make it easier to visualise.

With 365's functionality (e.g. using Lambda and Let) you simply need to point to your actual data range, and the formula will adjust. It's not as horrible a formula as the original, but you may still need to get your head around the new functions.

 

[jhall_cb]

Hi Stephen,

My apologies for the delayed response.

The above looks great and need to start getting into some of the newer functions like LAMBDA anyway so a good chance to do it. Thank you for taking the time to understand the problem and propose a solution - Marked as solution.

I'm still curious on the evaluation error as I had always assumed that calculation would be the same in a cell to the formula bar but obviously not. Thanks again, I'll post the full solution when I can get back to that job.

 

[StephenCrump]

I'm still curious on the evaluation error ....

Ha ha, yes, I dodged the actual question.

Based on your formula in Post #1, I suggest you have a have a look at E1:E372. Are there error values anywhere in this range?