Formula to find exact division

[Beneindias]

Hi guys,

I'm trying to find a formula that will find the second biggest number that can be divided by another number, with remainder of 0.

Basically, I want to do an exact division with the second biggest number.
Is there any formula to find this number?

For example:

4 is divided by 1, 2 and 4
I need to get 2

6 is divided by 1, 2, 3 and 6
I need to get 3

8 is divided by 1, 2, 4 and 8
I need to get 4

9 is divided by 1, 3, 9
I need to get 3

I hope that I'm explaining this in a way that you can understand, because it's much easier in my language xD

Thank you all

 

[lrobbo314]

I wrote a LAMBDA function here that does that.

FACTORS

 

[Fluff]

How about

Fluff.xlsm
	L	M
1
2	6	3
3	8	4
4	15	5
5	7	1
Sheet5

Cell Formulas
Range	Formula
M2:M5	M2	=LET(s,SEQUENCE(L2-1),TAKE(SORT(FILTER(s,MOD(L2,s)=0),,-1),1))

 

[Joe4]

Here is a User Defined Function we can place in VBA, and then use as we would any native Excel function:

Function SecondDivisor(num As Long) As Long

    Dim x As Long
    
'   Make sure num is not 0
    If num = 0 Then
        SecondDivisor = 0
        Exit Function
    End If
    
'   Loop through numbers backwards
    For x = (num - 1) To 1 Step -1
'       See if no remainder when dividing by x
        If (num / x) = Int(num / x) Then
            SecondDivisor = x
            Exit Function
        End If
    Next x
    
'   If no values found, choose 1
    SecondDivisor = 1
    
End Function

So, if we have a number in cell J1, the formula would be:

=SecondDivisor(J1)

Here is a sample of results:

 

[Beneindias]

How about

Fluff.xlsm
	L	M
1
2	6	3
3	8	4
4	15	5
5	7	1
Sheet5

Cell Formulas
Range	Formula
M2:M5	M2	=LET(s,SEQUENCE(L2-1),TAKE(SORT(FILTER(s,MOD(L2,s)=0),,-1),1))

Thanks @Fluff.

Your solution worked.
I didn't tried the other ones, because your way seemed simpler and I was avoiding the use of VBA in this project.

Thank you three for the help in this.

 

[Beneindias]

I wrote a LAMBDA function here that does that.

FACTORS

Thanks for your solution, but I still don't have the skills to work with Lambdas.

 

[Beneindias]

Here is a User Defined Function we can place in VBA, and then use as we would any native Excel function:

Function SecondDivisor(num As Long) As Long

    Dim x As Long
   
'   Make sure num is not 0
    If num = 0 Then
        SecondDivisor = 0
        Exit Function
    End If
   
'   Loop through numbers backwards
    For x = (num - 1) To 1 Step -1
'       See if no remainder when dividing by x
        If (num / x) = Int(num / x) Then
            SecondDivisor = x
            Exit Function
        End If
    Next x
   
'   If no values found, choose 1
    SecondDivisor = 1
   
End Function

So, if we have a number in cell J1, the formula would be:

=SecondDivisor(J1)

Here is a sample of results:
View attachment 98989

Thank you.

I didn't used your solution, because i'm trying to avoid the usage of VBA in this project.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Joe4]

Thank you.

I didn't used your solution, because i'm trying to avoid the usage of VBA in this project.

No problem. Totally understand.

 

[lrobbo314]

Looking at @Fluff's solution made me want to update the LAMBDA. It handles splitting the factors out to rows or columns with the optional 'to_col' argument.

FACTORS
	A	B	C	D
1	4	6	8	9
2	2	3	4	3
3	1	2	2	1
4		1	1
5
6	4	2	1
7	6	3	2	1
8	8	4	2	1
9	9	3	1
Sheet1

Cell Formulas
Range	Formula
A2:A3,D2:D3,B2:C4	A2	=FACTORS(A1)
B6:C6,B9:C9,B7:D8	B6	=FACTORS(A6,1)
Dynamic array formulas.

=LAMBDA(number,[to_col],
    LET(
        s,SEQUENCE(
            (to_col-1)*(-number+2)+1,
            to_col*(number-2)+1
        ),
        SORT(FILTER(s,MOD(number,s)=0),,-1,to_col)
    )
)