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.

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]COMPOUND
[/TD]
[TD]VOLUME
[/TD]
[TD]FLOW
[/TD]
[TD]MASK
[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

A​

[/TD]
[TD]

40​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

B​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

C​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

D​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

E​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

F​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

A​

[/TD]
[TD]

135​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

B​

[/TD]
[TD]

135​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

C​

[/TD]
[TD]

135​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

D​

[/TD]
[TD]

135​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

E​

[/TD]
[TD]

135​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]

G​

[/TD]
[TD]

135​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]

A​

[/TD]
[TD]

100​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]

H​

[/TD]
[TD]

100​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]

D​

[/TD]
[TD]

100​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]

E​

[/TD]
[TD]

100​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[/TR]
</tbody>[/TABLE]

 

[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)