formula to find quantities

a = 72,000
b = 68,500
c = 83,000
d = 99,900

need to find quantity of a, b, c & d to be bought for x amount

get me an Excel formula, I will enter x amount

a, b, c & d are constant, only x value changes, I need to find the qty of a,b,c,d
please guide, if it cannot be solved by a formula, then suggest me a code.

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

What are the conditions for purchase of cars. All cars are to be purchased or single type or lowest price cars etc.

Type 5 into G2.

In B2 copied down:

=$G$2

Is this what you mean?

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B	C	D	E	F	G
1	Car	Quantity	Unit Price	Total Amount
2	a	5	72,000	360,000.00			5
3	b	5	68,500	342,500.00
4	c	5	83,000	415,000.00
5	d	5	99,900	499,500.00
6	Grand Total	20		1,617,000.00

Sheet: Sheet2

And you reply to post #4?

If that's not it, manually MOCK UP what you want in the workbook and attach it again.

I have - the worksheet does NOT show us what you want.

Do as I have asked in post #6. We are NOT clairvoyant!!!

OK - so it's still not clear. What is the starting point? You said a value of x - which is the x value?

What are the rules?

There are two options in your file, which give the same exact amount.

Which is preferred?
Why?
If an exact match is not possible, should it be the nearest possible solution ABOVE the budget, or below the budget?
Where do you tell Excel WHAT the budget actually is?

My interpretation is the budget is the amount in D6/D15 and what is required is a mix of cars to meet this figure. With such a "vague" specification,
the mix of cars could be considerable.

And as Glenn pointed out: is a nearest possible solution acceptable, and if so, within what boundaries (+/- 10%)?

Please Login or Register  to view this content.

An exact match will not always be possible.

Mathematically, this will NOT always be possible.

Run-time error '1004':

Application-defined or object-defined error

Not very helpful: code works fine for me, having done mutiple tests, including re-runs on the file I posted (re-aattached). There is nothing in the VBA that will not work in Excel 2010.

So what is different in your data?

Please post file giving error

... Then update your profile with your current Excel version.

And re-read request in post #20: you are not helping us to help you.

You need to do that.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

For completenees, added code to handle no solution found:

Please Login or Register  to view this content.