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]

Welcome to the Forum!

It looks like you're overcooking your formula. The bit you've posted:

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

can be written:

=INDEX(G:G,MATCH(E20,E$1:E$372,))

Can you describe what you're trying to do, and illustrate with a simple example? Almost certainly there will be a more succinct way to do this.

Also let us know whether the formula can use 365's functionality, or whether it needs to be compatible with older Excel versions?

 

[jhall_cb]

Hi Stephen, Thanks for the quick response.

Formula is slightly overcooked intentionally to be dynamic and save on editing across various ranges or rules but the issue I have in main is the odd behavior in the calculation being different when nested as I have never seen that. As you have mentioned, I'm sure there will be a more concise way to achieve what I need but I couldn't wrap my head around a vba loop to do it initially and somehow pieced the formula theory together as it will be for a conditional format anyway.

Ultimate goal is to simulate something called negative selection which we apply to survey reports. Reports are generated on a hierarchy basis and need a minimum of 7 responses to be generated. In addition to the rule of 7, negative selection will also remove the report with the second lowest value so that results on the level above are not transparent. Apologies as that is also not straight forward but demonstrated in the Minisheet below. Unfortunately I cannot restructure the data or visualize in a different way in the final solution, it has to be in this format and some kind of conditional format rule on the row. I've upgraded to 365 2 weeks ago so happy to use the features, something like LET may help but I haven't had a chance to play with new formulas yet.

The reason a standard index match would not work is that you are looking for the smallest value within each group of the hierarchy, not the whole range. Depending on how many reports are under 7 you may also need to look for the lowest 2/3/4 values. Also need to catch that parent nodes have child nodes in-between so the rule needs to skip.

Quick dirty example below - Team 3 & 5 have less than 7 responses so rule triggers. We also lose 2nd lowest value in the group so Team 2 triggers rule also. Moving to the next hierarchy level, the same applies, South Teams trigger the rule and we lose the 2nd lowest in the group which is 26 so West Teams need trigger also. The dataset I have contains 9 hierarchy levels (cols) total. Apologies for the essay - not simple to phrase without losing people.

OrgMapping Simulator 2.xlsm
	AB	AC	AD	AE	AF	AG	AH	AI
7	Node/Level 1	Node/Level 2	Node/Level 3	Node/Level 4	Type	Responses
8	Site A				Parent	41
9	Site A	Management			Parent	41
10	Site A	Management	West Teams		Parent	26
11	Site A	Management	West Teams	Team 1	Child	15
12	Site A	Management	West Teams	Team 2	Child	10		Conditional Rule Level 4 = True
13	Site A	Management	West Teams	Team 3	Child	1		Conditional Rule Level 3 = True
14	Site A	Management	North Teams		Parent	30
15	Site A	Management	North Teams	Team 4	Child	30
16	Site A	Management	South Teams		Parent	5
17	Site A	Management	South Teams	Team 5	Child	5
Piviot

Cell Formulas
Range	Formula
AB9,AB17:AD17,AB16:AC16,AB15:AD15,AB14:AC14,AB11:AD13,AB10:AC10	AB9	=AB8

 

[StephenCrump]

When you say: We also lose 2nd lowest value in the group, do you mean the first lowest value >=7?

If so, should you also lose Team 4 at Rule Level 4?

 

[jhall_cb]

No, group is offset -1 column OR first parent above - West Teams in this example.
Group values range AG10:AG13, however in this format AG10 is the parent and is essentially the subtotal so we drop down to AG11:AG13, array would be {15, 10, 1}..

 

[StephenCrump]

I'm not sure you've answered the question. I'm talking about North, not West, and trying to clarify what 2nd lowest means.

Suppose we have:

Node/Level 1	Node/Level 2	Node/Level 3	Node/Level 4	Type	Responses
Site A	Management	North Teams		Parent	31
Site A	Management	North Teams	Team 4	Child	30
Site A	Management	North Teams	Team 5	Child	1

Is it correct that by your rules, we would
- Lose Team 5 (<7), and
- Lose Team 4 (2nd lowest)?

So if we have:

Node/Level 1	Node/Level 2	Node/Level 3	Node/Level 4	Type	Responses
Site A	Management	North Teams		Parent	30
Site A	Management	North Teams	Team 4	Child	30

shouldn't we also lose Team 4?

And what about:

Node/Level 1	Node/Level 2	Node/Level 3	Node/Level 4	Type	Responses
Site A	Management	North Teams		Parent	35
Site A	Management	North Teams	Team 4	Child	30
Site A	Management	North Teams	Team 5	Child	3
Site A	Management	North Teams	Team 6	Child	2

We lose Team 5 and Team 6 (both <7). Do we also lose Team 4 (lowest value>=7)?

 

[StephenCrump]

Please also let us know whether you have the BYROW function, i.e. can you replicate this?

	A	B	C	D
1	1	2	3	6
2	4	5	6	15
3	7	8	9	24
Sheet1

Cell Formulas
Range	Formula
D1:D3	D1	=BYROW(A1:C3,LAMBDA(r,SUM(r)))
Dynamic array formulas.

 

[jhall_cb]

Haha this is the nightmare of this rule... In my example Team 4 will not trigger as it is >7 - No action taken.

As mentioned before, each row indicates a report to generate based on survey data. In your first example, due to Team 5 being <7 the rule triggers. Team 4 also triggers in this example because If you get results for the Team 4 and subtract from results for parent you can work out results so this silly rule says you lose both child nodes. The parent is fine as there is no transparency in the result sets.

Node/Level 1	Node/Level 2	Node/Level 3	Node/Level 4	Type	Responses
Site A	Management	North Teams		Parent	31
Site A	Management	North Teams	Team 4	Child	30
Site A	Management	North Teams	Team 5	Child	1

In your other example, Team 5 & 6 are both <7 so they both trigger. No need for next lowest as there is no transparency when subtracting Team 4 results from the parent, it cannot narrow down to one team.

Node/Level 1	Node/Level 2	Node/Level 3	Node/Level 4	Type	Responses
Site A	Management	North Teams		Parent	35
Site A	Management	North Teams	Team 4	Child	30
Site A	Management	North Teams	Team 5	Child	3
Site A	Management	North Teams	Team 6	Child	2

Confirm I can replicate

=BYROW(A1:C3,LAMBDA(r,SUM(r)))

at a quick look at the formula descriptions I think these are the kind of things that may make life a whole lot easier.

 

[StephenCrump]

Thanks, that's clearer. But just to be sure ...

If you have:
Team 1: 50
Team 2: 40
Team 3: 2

you can report only on Team 1, because if you reported on the Group (92), Team 1 (50) and Team 2 (40), you'd be able to deduce Team 3's results by subtraction. (And with a small Team <7 people it would too easy to guess individual results).

If you have
Team 1: 50
Team 2: 40
Team 3: 2
Team 4: 1
Team 5: 1

then presumably you still need to eliminate Team 2 (as well as Teams 3, 4 and 5), because otherwise you could still deduce results for 4 people, albeit in different teams, by subtraction?

If I'm correct, the rule could be phrased: If you have any group < 7 people, you must eliminate the smallest groups in order until you have eliminated at least 7 people. Is this correct?

EDIT:

Sorry, I just realised you answered this question in the previous post. You're saying that although in the my 2nd example, there are only 4 people, they are across more than one team, therefore no need to eliminate Team 2.

That makes the rule: If there is exactly one group < 7 people, you must eliminate that group and the next smallest group.

 

[jhall_cb]

Yesss, that's the logic. 1st Example is exactly right.

With the second, it would just be Teams 3-5 as each are <7 but the other two are okay... while you know only 4 people have responded you could not deduce which of the three teams their results related to. Or which of those three teams had 1 response or 2.

Also consider that this is essentially just the first tier, after this column it would nudge back and work a column at a time right to left. In a group with one child <7 under the parent you would lose both. I think phrased it would be something like.... for each group, if count(val<7)=1 then eliminate row plus 2nd smallest , if count(val<7)>=2 then matching rows only