How can I filter zeros from a dynamically generated array in Excel?

Question

I'm trying to generate a list of all the factors of a number in MS Excel. I have the following formula:

=NOT(MOD(12,SEQUENCE(1,12)))*(SEQUENCE(1,12))

This gives me an array with factors and zeros, as below:

1 2 3 4 0 6 0 0 0 0 0 12

But I can't seem to find a formula to remove the zeros. I've tried different permutations of LET, LAMBDA, LARGE, FILTER, SORT, and others. Nothing works. Any tips on what I could be doing wrong? I want to do this without VBA.

you have two answers, so I will not answer, but building on theirs, use LET to do the formula once: =LET(x,NOT(MOD(12,SEQUENCE(1,12)))*(SEQUENCE(1,12)),FILTER(x,x<>0)) — Scott Craner
– Scott Craner, Commented Oct 15 at 17:54
@ScottCraner the last part could also be FILTER(x,x) because any value other than 0 is considered TRUE and 0 is FALSE — P.b
– P.b, Commented Oct 15 at 19:19

cgwoz · Accepted Answer · 2025-10-15 17:56:07Z

3

This lists numbers from 1 to 12, checks which divide 12 evenly, and filters out the rest. The formula repeats the expression to test which of those numbers are not zero; that’s how FILTER() knows which entries to include in the final list. Will work in your scenario.


=FILTER(NOT(MOD(12,SEQUENCE(1,12)))*SEQUENCE(1,12),(NOT(MOD(12,SEQUENCE(1,12)))*SEQUENCE(1,12))<>0)

edited Oct 15 at 17:56

answered Oct 15 at 17:49

cgwoz

1,1024 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

the-mad-statter · Accepted Answer · 2025-10-15 17:49:47Z

2

filter() will work, but you will have to enter your formula twice:

=FILTER(NOT(MOD(12,SEQUENCE(1,12)))*(SEQUENCE(1,12)),NOT(MOD(12,SEQUENCE(1,12)))*(SEQUENCE(1,12))<>0)

answered Oct 15 at 17:49

the-mad-statter

9,3911 gold badge16 silver badges31 bronze badges

1 Comment

Harun24hr Oct 16 at 2:03

=LET(x,NOT(MOD(12,SEQUENCE(1,12)))*(SEQUENCE(1,12)),FILTER(x,x>0))

Harun24hr · Accepted Answer · 2025-10-16 02:02:42Z

1

Use LET() function to avoid repetitive use of same formula. It will shorter your formula and improve performance. Try-

=LET(x,NOT(MOD(12,SEQUENCE(1,12)))*(SEQUENCE(1,12)),FILTER(x,x>0))

answered Oct 16 at 2:02

Harun24hr

38k6 gold badges24 silver badges47 bronze badges

2 Comments

DjC Oct 16 at 13:45

Hi Harun, since we're using LET to eliminate redundant calculations, a variable could also be defined for SEQUENCE. Having said that, NOT is already returning the Boolean values needed to filter the array, so the multiplication step can also be eliminated. I think something like this should do the trick: =LAMBDA(n,LET(j,SEQUENCE(,n),FILTER(j,NOT(MOD(n,j)))))(12) Kind regards. :)

Harun24hr Oct 18 at 2:05

Yes, this will improve performance of the formula. Thank you!

PaulS · Accepted Answer · 2025-10-21 15:41:29Z

0

Another possible solution:

=TRANSPOSE(LET(n, 12, s, SEQUENCE(n), FILTER(s, MOD(n, s)=0)))

This formula finds all the factors of 12 and returns them in a single row. First, SEQUENCE(n) creates the numbers from 1 to n (in this case from 1 to 12). Then FILTER(s, MOD(n,s)=0) keeps only the numbers that divide 12 exactly, which are its factors. Finally, TRANSPOSE changes the list from a vertical column into a horizontal row. The LET function is used just to make the formula clearer by naming n and s inside the calculation.

Or even shorter (thanks to @VBasic2008; see comments below):

=LET(n, 12, s, SEQUENCE(,n), FILTER(s, MOD(n, s)=0))

edited Oct 21 at 15:41

answered Oct 20 at 19:11

PaulS

27.1k3 gold badges18 silver badges40 bronze badges

4 Comments

VBasic2008 Oct 20 at 23:03

FILTER works vertically and horizontally. SEQUENCE(1,n) or SEQUENCE(,n) will spill a row, so you don't need TRANSPOSE. Try it, don't take my word for it so it'll better sink in.

PaulS Oct 21 at 9:08

Thanks for your comment. I have just tried without TRANSPOSE, and the result is a column (and not a row).

VBasic2008 Oct 21 at 13:52

You also have to change SEQUENCE(n) to either SEQUENCE(1,n) or SEQUENCE(,n).

PaulS Oct 21 at 15:39

You are right, VBasic2008: it works with SEQUENCE(1,n) or SEQUENCE(,n). Thanks!

Collectives™ on Stack Overflow

How can I filter zeros from a dynamically generated array in Excel?

4 Answers 4

Comments

1 Comment

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related