Running into "multi-cell array formulas are not allowed in tables" error - what now?

[keelaboosa]

I'm trying to build the MASK column such that a 1 is displayed for the first distinct COMPOUND that has VOL * FLOW > 0, but I can't get it to work in the table using SUM(IF(FREQUENCY constructs; I get a "multi-cell array formulas are not allowed in tables" error.

What other options would I have to build this? I'd like to keep it a formula-based solution rather than VBA so people can follow the logic. I keep the VBA locked and hidden.

	A	B	C	D
1	COMPOUND	VOLUME	FLOW	MASK
2​	A​	40​	1​	1​
3​	B​	0​	1​	0​
4​	C​	0​	1​	0​
5​	D​	0​	1​	0​
6​	E​	0​	1​	0​
7​	F​	0​	1​	0​
8​	A​	135​	1​	0​
9​	B​	135​	1​	1​
10​	C​	135​	1​	1​
11​	D​	135​	1​	1​
12​	E​	135​	1​	1​
13​	G​	135​	0​	0​
14​	A​	100​	1​	0​
15​	H​	100​	1​	1​
16​	D​	100​	1​	0​
17​	E​	100​	1​	0​

<tbody>
</tbody>

 

[StephenCrump]

I am curious what formula you're trying to use?

But another option perhaps:

{=--IFERROR(MATCH(Table1[@COMPOUND],IF(Table1[VOLUME]*Table1[FLOW],Table1[COMPOUND],""),)=ROW(Table1[@COMPOUND])-ROW(Table1[[#Headers],[COMPOUND]]),0)} array-entered

 

[keelaboosa]

I wasn't able to get your formula to work, but I'm also worried that it won't take it because tables won't accept a multi-cell array formula.

Any other ideas?

 

[RoryA]

Why do you need a multi-cell array formula, rather than separate array formulas in each row?

 

[keelaboosa]

I thought you had to highlight the range of cells you want to enter the array formula in before you hit CSE. CSE-ing an array formula into a cell then filling down doesn't give the same results.

 

[RoryA]

Hard to comment when you haven't said what your formula is.

 

[keelaboosa]

The formula I'm using is

{=IF(FREQUENCY(MATCH(IF(Table1[VOLUME]*Table1[FLOW],Table1[COMPOUND],""),Table1[COMPOUND],0),ROW(Table1[COMPOUND])-ROW(Table1[@COMPOUND])+1),1,0)}

This tanks when VOLUME * FLOW = 0, but I'll tackle that once I figure out how to get this array formula in the table.

 

[RoryA]

Assuming your table is in A:D starting in row 1:

=IF([@FLOW]*[@MASK],IF(COUNTIFS(A$1:A2,[@COMPOUND],B$1:B2,"<>0",C$1:C2,"<>0")=1,1,0),0)

entered normally.

 

[keelaboosa]

Excellent!

Now, to make it easier to write the formula into the cells via VBA I would like to replace the cell references A$1:A2, B$1:B2, C$1:C2 with a static formula that evaluates to the reference. Is there a way to use OFFSET and ROW to do this?

 

[keelaboosa]

I've answered my own question - you can replace the relative and absolute cell references with offset and row. Replaced range references are shown in bold font.

=IF([@FLOW]*[@VOLUME],IF(COUNTIFS(OFFSET(Table1[[#Headers],[COMPOUND]],SUM(ROW([@COMPOUND])-ROW(Table1[[#Headers],[COMPOUND]])),0,-SUM(ROW([@COMPOUND])-ROW(Table1[[#Headers],[COMPOUND]]))),[@COMPOUND],OFFSET(Table1[[#Headers],[VOLUME]],SUM(ROW([@VOLUME])-ROW(Table1[[#Headers],[VOLUME]])),0,-SUM(ROW([@VOLUME])-ROW(Table1[[#Headers],[VOLUME]]))),"<>0",OFFSET(Table1[[#Headers],[FLOW]],SUM(ROW([@FLOW])-ROW(Table1[[#Headers],[FLOW]])),0,-SUM(ROW([@FLOW])-ROW(Table1[[#Headers],[FLOW]]))),"<>0")=1,1,0),0)