Exclude 0's from arrayformula

Question

=ArrayFormula({"Test"; query(value(CTA!C2:C)*0.8)})

How would I exclude the rest of the 0's in empty cells?

Try something like `=INDEX({"Test";FILTER(CTA!C2:C,ISNUMBER(1/CTA!C2:C))*0,8})` — JvdV, Sep 17 '22 at 08:09

score 2 · Accepted Answer · answered Sep 16 '22 at 19:36

2

try:

=ARRAYFORMULA({"Test"; IFERROR(1/(1/(VALUE(CTA!C2:C)*0.8)))})

or:

=ARRAYFORMULA({"Test"; QUERY(VALUE(CTA!C2:C)*0.8; "where Col1 <> 0"; )})

answered Sep 16 '22 at 19:36

player0

They both worked. Is one more efficient then the other? – cpaone Sep 16 '22 at 19:43
@cpaone first hides it and the second one removes it – player0 Sep 16 '22 at 19:45
@player0 You actually had a break. ^_^ – Osm Sep 16 '22 at 19:52
1

Yes haha. Thanks for the help like always! – cpaone Sep 16 '22 at 19:54

score 1 · Answer 2 · answered Sep 16 '22 at 21:28

1

Use INDEX+COUNTA to constrain the input:

=ARRAYFORMULA({Test;CTA!C2:INDEX(CTA!C:C,COUNTA(CTA!C:C))*0.8})

answered Sep 16 '22 at 21:28

TheMaster

2 Answers2