SOLVED

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

Copper Contributor

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

7 Replies
best response confirmed by KennyK360 (Copper Contributor)
Solution

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

HansVogelaar_5-1688587831675.png

 

Hans, you're an absolute genius Sir! It worked perfectly...thank you for saving me countless hours!!
Hans, one more question, can we edit the formula to:
1) exclude answers less than 13
2) exclude answers with the number 1 and the number in question (e.g. 2,023 (1x2023)
3) exclude answers larger than 1,500
4) delete/exclude rows with answers not meeting the above 3 rules
Thank you again!

@KennyK360 

That would be

 

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

Hans,
Awesome! We're getting there. Is there any edits we can make to exclude answers > 1500?

@KennyK360 

Sorry, forgot about that.

 

=LET(Num,A2,All,SEQUENCE(MIN(Num-13,1487), , 13),Factors,FILTER(All,MOD(Num,All)=0),Factors)

Absolutely brilliant! Thank you so much for all the help! I appreciate your time.
1 best response

Accepted Solutions
best response confirmed by KennyK360 (Copper Contributor)
Solution

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

HansVogelaar_5-1688587831675.png

 

View solution in original post