How can I store an array with #DIV/0! as a name?

[chhib]

header
0
0
0
1

This yields an error
{ =(ROW($G$2:$G$5)-ROW($G$2)+1)/($G$2:G$5<>0) }
F9: {#DIV/0!;#DIV/0!;#DIV/0!;4}
In cell: #DIV/0

Whereas the inverted [1, 0, 0, 0] does not? The inverted can be stored as a name, whereas the original array formula can't.

Is there a way for me to store an array as a name, despite rendering #DIV/0?

 

[XOR LX]

Hi,

Actually both these can be stored as names.

And the reason that the first returns #DIV/0! and the second does not is that there is no formula acting on these arrays to coerce an array return; hence only the first element in each is processed.

If you store the first as Array1 and the second as Array2, then both of these will coerce the array and both will also return #DIV/0!:

=SUM(Array1)

=SUM(Array2)

What exactly are you trying to achieve here?

Regards

 

[chhib]

You're right - it works. Thanks.

I think my problem was that I tried to CMD+Enter (CSE on Mac) when saving the name, which resulted in an error. Silly me.

I was hoping that by referencing the same named array, my long list of extracting unique items with an AGGREGATE(15, 6, MyArray, ROWS($Q$7:$Q7)) would get a performance boost. It did not increase performance, but it did make the formulas more legible.

Thanks again.

 

[XOR LX]

Ah, the old Mac, eh?

And you're welcome!