How to solve this problem? is it array?

[DoubleScope]

I have to find how many x and y.
Example :
365.000 = (40.000 * x) + (55.000 * y), the answer is x =5, y = 3.
But formula in Excel?

365.000 = (40.000 * x) + (55.000 * y), the answer is x =5, y = 3.
200.000 = (40.000 * x) + (55.000 * y), the answer is x = 4, y =0.

Sometimes, i need another variable other than 40k and 55k, 75k, 50k.
Help ... I really appreciate it.

 

[Saurabhj]

Hi, can resolve through VBA.

Check below macro:

Sub solveEquation()
Dim eq As String, LHS As Long
Dim equalsignPos As Integer, xQty As Long, yQty As Long
Dim plussignPos As Integer
Dim xval As Integer, yval As Integer, result As Long

eq = WorksheetFunction.Substitute(Range("A1"), " ", "")  'Equation to solve

equalsignPos = InStr(1, eq, "=")    'position of equal = sign (9)
plussignPos = InStr(1, eq, "+")     'position of plus + sign (24)

LHS = Trim(Left(eq, equalsignPos - 1)) 

xQty = Trim(Mid(eq, InStr(1, eq, "(") + 1, InStr(1, eq, "*") - InStr(1, eq, "(") - 1))

yQty = Trim(Mid(eq, InStr(1, eq, "+(") + 2, InStr(InStr(1, eq, "*") + 1, eq, "*") - InStr(1, eq, "+(") - 2))

For xval = 0 To LHS
    For yval = 0 To LHS
        result = (xQty * xval) + (yQty * yval)
        If result = LHS Then
            MsgBox "x=" & xval & vbNewLine & "y=" & yval
            Exit For
        End If
    Next
Next

End Sub

 

[DoubleScope]

Hi, can resolve through VBA.

Check below macro:

Sub solveEquation()
Dim eq As String, LHS As Long
Dim equalsignPos As Integer, xQty As Long, yQty As Long
Dim plussignPos As Integer
Dim xval As Integer, yval As Integer, result As Long

eq = WorksheetFunction.Substitute(Range("A1"), " ", "")  'Equation to solve

equalsignPos = InStr(1, eq, "=")    'position of equal = sign (9)
plussignPos = InStr(1, eq, "+")     'position of plus + sign (24)

LHS = Trim(Left(eq, equalsignPos - 1))

xQty = Trim(Mid(eq, InStr(1, eq, "(") + 1, InStr(1, eq, "*") - InStr(1, eq, "(") - 1))

yQty = Trim(Mid(eq, InStr(1, eq, "+(") + 2, InStr(InStr(1, eq, "*") + 1, eq, "*") - InStr(1, eq, "+(") - 2))

For xval = 0 To LHS
    For yval = 0 To LHS
        result = (xQty * xval) + (yQty * yval)
        If result = LHS Then
            MsgBox "x=" & xval & vbNewLine & "y=" & yval
            Exit For
        End If
    Next
Next

End Sub

Thanks i'll try.
But Im still curious formula code in excel (non-VBA).

 

[StephenCrump]

But Im still curious formula code in excel (non-VBA).

Welcome to the Forum!

Like this perhaps:

	A	B	C	D	E
1
2		365	40	55
3			5	3
4
5		200	40	55
6			5	0
7
8		80	20	40
9			0	2
10			2	1
11			4	0
12
Sheet2

Cell Formulas
Range	Formula
C3,C9:C11,C6	C3	=LET(s,SEQUENCE(1+B2/C2,,0),FILTER(s,MOD(B2-s*C2,D2)=0))
D3,D9:D11,D6	D3	=(B2-C2*C3#)/D2

 

[DoubleScope]

Sorry, i dont understand. I just copied your formula. And it didnt work.

 

[DoubleScope]

Welcome to the Forum!

Like this perhaps:

	A	B	C	D	E
1
2		365	40	55
3			5	3
4
5		200	40	55
6			5	0
7
8		80	20	40
9			0	2
10			2	1
11			4	0
12
Sheet2

Cell Formulas
Range	Formula
C3,C9:C11,C6	C3	=LET(s,SEQUENCE(1+B2/C2,,0),FILTER(s,MOD(B2-s*C2,D2)=0))
D3,D9:D11,D6	D3	=(B2-C2*C3#)/D2

Sorry, i dont understand. I just copied your formula. And it didnt work.
Look my post before.

 

[StephenCrump]

It looks like you don't have the LET function. Does this work:

	A	B	C	D
1
2		365	40	55
3			5	3
4
5		200	40	55
6			5	0
7
8		80	20	40
9			0	2
10			2	1
11			4	0
12
Sheet1

Cell Formulas
Range	Formula
C3,C9:C11,C6	C3	=FILTER(SEQUENCE(1+B2/C2,,0),MOD(B2-SEQUENCE(1+B2/C2,,0)*C2,D2)=0)
D3,D9:D11,D6	D3	=(B2-C2*C3#)/D2

 

[Peter_SSs]

It looks like you don't have the LET function.

From the image, to me it looks like the FILTER function is identified as a problem.

Just guessing: Possibly a language function translation issue?
Or perhaps even a formula separator issue and it could be this?

=LET(s;SEQUENCE(1+B2/C2;;0);FILTER(s;MOD(B2-s*C2;D2)=0))

 

[DoubleScope]

From the image, to me it looks like the FILTER function is identified as a problem.

Just guessing: Possibly a language function translation issue?
Or perhaps even a formula separator issue and it could be this?

=LET(s;SEQUENCE(1+B2/C2;;0);FILTER(s;MOD(B2-s*C2;D2)=0))

Yes from image, you can see FILTER Function is the main problem.

And im using , as separator.

 

[DoubleScope]

It looks like you don't have the LET function. Does this work:

	A	B	C	D
1
2		365	40	55
3			5	3
4
5		200	40	55
6			5	0
7
8		80	20	40
9			0	2
10			2	1
11			4	0
12
Sheet1

Cell Formulas
Range	Formula
C3,C9:C11,C6	C3	=FILTER(SEQUENCE(1+B2/C2,,0),MOD(B2-SEQUENCE(1+B2/C2,,0)*C2,D2)=0)
D3,D9:D11,D6	D3	=(B2-C2*C3#)/D2

From 2nd image, you can see FILTER Function is the main problem.
Thanks.