How to solve this problem? is it array?

DoubleScope

New Member
Joined
Feb 22, 2022
Messages
10
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Question is in image.
Thanks.
 

Attachments

  • image_2022-02-24_191534.png
    image_2022-02-24_191534.png
    7.9 KB · Views: 10
Upvote 0
I'm using Microsoft Office Home and Student 2019 Original.
Your signature says 365. If you're not using 365 then you won't have LET, FILTER or SEQUENCE. But then I would have expected a #NAME error rather than "Function is not valid".

Does this work for you?

ABCDEFGHIJKLM
1
236540552004055802040
3535002
4----21
540
6--
7
Sheet1
Cell Formulas
RangeFormula
C3:C4,K3:K6,G3:G4C3=IFERROR(SMALL(IF(MOD(B$2-C$2*(ROW(INDIRECT("1:"& 1+INT(B$2/C$2)))-1),D$2)=0,ROW(INDIRECT("1:"& 1+INT(B$2/C$2)))-1),ROWS(C$3:C3)),"-")
D3:D4,L3:L6,H3:H4D3=IFERROR((B$2-C$2*C3)/D$2,"-")
 
Upvote 0
Solution
Why do I get different results?

Book1
ABCDEFGHIJKLM
1
236540552004055802040
306.6363603.6363602
4------
5--
6--
7
Sheet2
Cell Formulas
RangeFormula
K3:K6,G3:G4,C3:C4C3=IFERROR(SMALL(IF(MOD(B$2-C$2*(ROW(INDIRECT("1:"& 1+INT(B$2/C$2)))-1),D$2)=0,ROW(INDIRECT("1:"& 1+INT(B$2/C$2)))-1),ROWS(C$3:C3)),"-")
L3:L6,H3:H4,D3:D4D3=IFERROR((B$2-C$2*C3)/D$2,"-")
 
Upvote 0
@Peter_SSs Thank you. That worked!

I had copied and pasted the xl2BB and the formula section didn't show that CSE was required, so I didn't try that. :(

Working now though. Thank you!

Book1
ABCDEFGHIJKLM
1
236540552004055802040
3535002
4----21
540
6--
7
Sheet2
Cell Formulas
RangeFormula
K3:K6,G3:G4,C3:C4C3=IFERROR(SMALL(IF(MOD(B$2-C$2*(ROW(INDIRECT("1:"& 1+INT(B$2/C$2)))-1),D$2)=0,ROW(INDIRECT("1:"& 1+INT(B$2/C$2)))-1),ROWS(C$3:C3)),"-")
L3:L6,H3:H4,D3:D4D3=IFERROR((B$2-C$2*C3)/D$2,"-")
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
the formula section didn't show that CSE was required
365 does not require the CSE confirmation so it is easy to forget to include it or mention it for users of versions that do require it.
 
Upvote 0
Ahhhh, "I see" said the blind man to his deaf wife as he picked up the hammer and then saw.

Thank you again @Peter_SSs. I had forgot about that.
 
Upvote 0
Your signature says 365. If you're not using 365 then you won't have LET, FILTER or SEQUENCE. But then I would have expected a #NAME error rather than "Function is not valid".

Does this work for you?

ABCDEFGHIJKLM
1
236540552004055802040
3535002
4----21
540
6--
7
Sheet1
Cell Formulas
RangeFormula
C3:C4,K3:K6,G3:G4C3=IFERROR(SMALL(IF(MOD(B$2-C$2*(ROW(INDIRECT("1:"& 1+INT(B$2/C$2)))-1),D$2)=0,ROW(INDIRECT("1:"& 1+INT(B$2/C$2)))-1),ROWS(C$3:C3)),"-")
D3:D4,L3:L6,H3:H4D3=IFERROR((B$2-C$2*C3)/D$2,"-")
WOW... Thanks so much.
Its worked.

Yes, im wrong to select my office version.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top