Calculate Value Based On Combination Of Other Numbers

[mrskyline]

Good afternoon whizzy Excel people, I wonder if anyone can help me solve a problem that I'm trying to come up with a formula for, I'm usually pretty good at this sort of thing but this one is eluding me, no doubt someone out there has a simple answer using a function I haven't come across yet, well one would hope it will be that simple.

So what I am trying to calculate is the number of units required of each of the three items in the table below to hit the total value required of a/b/c in the second table, does that make sense to anybody? The number doesn't have to be bang on exactly, just there or there abouts #HELP

	1 unit value			Total value required
Item	a	b	c			a	b	c
item 1	40	12	1	?		55	29	12
item 2	20	8	2	?
item 3	6	3	24	?

<tbody>
</tbody>

This problem has had me pulling my hair out so thought I would ask the experts, all help appreciated #thanks

 

[Special-K99]

I dont understand the problem.
What would your answer be given the data you've supplied?

 

[mrskyline]

OK bamboozled myself at hurdle 1 ... OK updated the numbers so they work now, see below, does that make more sense?

	1 unit value			Total value required
Item	a	b	c			a	b	c
item 1	4	2	1	7		56	32	51
item 2	2	3	2	2
item 3	6	3	10	4

<tbody>
</tbody>

So 7 x item + 2 x item 2 + 3 x item 3 gives me 56 a | 32 b | 51 c

 

[shg]

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	Item​	a​	b​	c​	​		a​	b​	c​
2​	item 1	4​	2​	1​	7​		56​	32​	51​	G2: =SUMPRODUCT(B2:B4, $E2:$E4)
3​	item 2	2​	3​	2​	2​
4​	item 3	6​	3​	10​	4​

 

[Eric W]

If I understand your question properly, you're looking at a use of the Solver.

Each item has a certain number of units of a, b, and c. You're looking for the "best" combination of items that result in the "closest" values of a, b, and c in the required table. Consider the layout below:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		1 unit value			Total value required
2	Item	a	b	c			a	b	c
3	item 1	4	2	1	7		56	32	51		28	14	7
4	item 2	2	3	2	2						4	6	4
5	item 3	6	3	10	4						24	12	40
6
7											0	0	0	0
Sheet2

Cell Formulas
Range	Formula
K3	=$E3*B3
K4	=$E4*B4
K5	=$E5*B5
K7	=(SUM(K3:K5)-G3)^2
L3	=$E3*C3
L4	=$E4*C4
L5	=$E5*C5
L7	=(SUM(L3:L5)-H3)^2
M3	=$E3*D3
M4	=$E4*D4
M5	=$E5*D5
M7	=(SUM(M3:M5)-I3)^2
N7	=SUM(K7:M7)

The values in E3:E5 indicate how many of each item to use. The formulas in K3:M5 show how many units of a,b,c are used. K7:M7 show the variation from the desired values in the G3:i3 table. I used a squared function to handle negative values. Finally N7 shows the total variation from the table in G3:I3. We want a way to minimize the total variation. We can do that with the Solver.

1) Make sure you have the Solve installed. Go to File --> Options --> Add-ins --> Excel Add-ins --> Go --> Check Solver Add-in --> OK.
2) Make sure your sheet is set up as I showed above
3) Go to the Data tab and click Solver on the far right
4) Enter these parameters:

Objective: $N$7
To: Min
By Changing Variable Cells: $E$3:$E$5
Subject to Constraints: $E$3:$E$5 = integer, $E$3:$E$5 >= 0

Then click Solve. If you add a lot of items, you may want to change the Solving Method to Evolutionary.

Let me know if this helps.

 

[shg]

I misunderstood.

Your problem can be restated as solving the three simultaneous equations

4x + 2y + 6z = 56
2x + 3y + 3z = 32
1x + 2y + 10z = 51

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Item​	a​	b​	c​	​	​	a​	b​	c​
2​	item 1	4​	2​	1​	7​		56​	32​	51​
3​	item 2	2​	3​	2​	2​
4​	item 3	6​	3​	10​	4​

The array formula in E2:E4 is

{=MMULT(MINVERSE(TRANSPOSE(B2:D4)), TRANSPOSE(G2:I2))}

 

[mrskyline]

Thanks guys I knew someone could help me, I need to digest these and attempt to make them work in my own spreadsheet, I'll let you know how I get on and hopefully not have to bug you too much in the proccess