Formula that transfers a simple algorithm into excel

[parisdoil123]

I am quite new to excel formula programming and i would like to have your help.
Actually what i want to do is to get any mathematically correct values that can be multiplied with 6 standard values and their sum would be less than an another stable.


so actually i have 6 stables put in cells a1,b1,c1,d1,e1,f1.those are 15,15,15,15,18,20
and i want them multiplied with every mathematically correct amount so their sum is less than g3.
so i want the values a2,b2,c2,d2,e2,f2 for these that a1*a2+b1*b2+c1*c2+d1*d2+e1*e2+f1*f2 <g3.
I hope i recieve your answers at your earliest convinient .thank you for your time

[Andrew-R]

<Dips toe in water>

What do you mean by a "mathematically correct amount"?

There will be an infinite number of values that could be put into cells A2:F2 - how do you know which ones you want?

[parisdoil123]

i actually dont,i was asked to make such a formula that does calculate all possible values for a2 to f2 so the sum is <g3,is there any valid formula that does that nonsense(no matter what) action/ ty for your time once again

[Andrew-R]

As I said, there are an infinite number of values for a2-f2 which will satisfy the equation you've given, so any formula will, by definition, take an infinite length of time to calculate.

If you place some constraints on the values (e.g. all numbers are positive integers) then you might, just about, bring it into the realms of the possible.

Perhaps it would be easiest if you took a step back and told us what you're trying to do.

[parisdoil123]

well let's say my employee thinks that this is a good way to check out the amount of the products he needs to buy so he spends properly the budget that was given to him so he can buy these products.well many things arent working properly in the place i work and that affects some decisions,i was just trying to find it throught a formula @ excel and not throught an algorithm cause my employee cant run it

[Andrew-R]

Ah, then what you've got is a variation of the knapsack problem which is an NP-complete problem (which, more or less, means that in order to find the optimum combination you must compute all possible combinations).

In your particular case the difficulty/practicality of solving the problem mainly depends on the size of G3 compared to the size of the sum of A1:F1. If G3 is close in size to SUM(A1:F1) then there may be very few solutions, but if G3 is large then there could be thousands or millions of possible solutions.

Do A2:F2 have a lower constraint (i.e. must you have at least 0 or 1, or some higher number, of each), do they have to be integers? If you give us that information, and numbers for G3 it should be fairly easy to calculate the number of combinations that have to be generated, which would at least be a start.

[Andrew-R]

Because I can't leave these things alone - here's a sheet showing the valid combinations for the values you gave, assuming a minimum purchase of 1 of each item and a maximum (G3) of 150.

The formula driving this have been copied down the 1st 10,000 rows, but if you change any of the values, even slightly you'll see the number of combinations it has to consider becomes vast and unmanageable.

[parisdoil123]

you have already been helpful enought and i have to admitt that i am quite grateful.
All i needed to do is to prove that there are unlimited correct combinations to compose such a sum throught random multiplications.
My numbers have to be inter,and the spesific number is item1=15,item2=15,item3=15,item4=15,item5=18,item6=20 and sum 700

[Andrew-R]

Well, with those values we can be sure that there are fewer than 4.5 billion valid combinations, so it's within the bounds of what can be computed in Excel, providing you have enough time on your hands

If all of the values are positive integers then there will never be an infinite number of solutions, irrespective of what values you choose, but in practical terms the number of combinations can easily exceed the number that can be computed and checked in a reasonable length of time.

[Andrew-R]

Sorry, I really can't leave this alone - fiddling with my spreadsheet a bit shows that there are actually fewer than 3 billion possible combinations.

In fact, armed with a bit of maths and a pen and paper I can find an optimal solution:

15 x item 1 + 8 each of items 2, 3 and 4 (actually any combination of the first 4 items which sums to 39 will work) + 3 each of items 5 and 6 = 699

Apart from trivial variations on those values I can be sure that other optimal configurations exist, although I do believe that all such configurations will involve 3, 13 or 23 units of item 5 (e.g. 12 x item 1, 1 each of items 2-4, 23 x item 5 and 3 x item 6).

Does that help at all?