Forum Discussion

Copper Contributor

Jul 04, 2023

Solved

Need help with a function/formula in Excel to determine the factors of a number

For example, the 6 factors of the number 2,023 are 1, 7, 17, 119, 289 and 2023 (1x2023=2023, 7x289=2023 and 17x119=2023).

Formulas and Functions

HansVogelaar
Jul 05, 2023
KennyK360

Let's say your number is in A2.

If you want the factors in cells below each other:

=LET(Num, A2, All, SEQUENCE(Num), Factors, FILTER(All, MOD(Num, All)=0), Factors)

If you want the factors in cells next to each other, from left to right:

=LET(Num, A2, All, SEQUENCE(Num), Factors, FILTER(All, MOD(Num, All)=0), TRANSPOSE(Factors))

If you want the factors in a single cell, separated by commas and spaces:

=LET(Num, A2, All, SEQUENCE(Num), Factors, FILTER(All, MOD(Num, All)=0), TEXTJOIN(", ", TRUE, Factors))

MVP

Jul 05, 2023

Let's say your number is in A2.

If you want the factors in cells below each other:

=LET(Num, A2, All, SEQUENCE(Num), Factors, FILTER(All, MOD(Num, All)=0), Factors)

If you want the factors in cells next to each other, from left to right:

=LET(Num, A2, All, SEQUENCE(Num), Factors, FILTER(All, MOD(Num, All)=0), TRANSPOSE(Factors))

If you want the factors in a single cell, separated by commas and spaces:

=LET(Num, A2, All, SEQUENCE(Num), Factors, FILTER(All, MOD(Num, All)=0), TEXTJOIN(", ", TRUE, Factors))

Copper Contributor

Jul 05, 2023

Hans, you're an absolute genius Sir! It worked perfectly...thank you for saving me countless hours!!

Resources