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

[TABLE="width: 576"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 3"]1 unit value
[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Total value required
[/TD]
[/TR]
[TR]
[TD]Item
[/TD]
[TD]a
[/TD]
[TD]b
[/TD]
[TD]c
[/TD]
[TD][/TD]
[TD][/TD]
[TD]a
[/TD]
[TD]b
[/TD]
[TD]c
[/TD]
[/TR]
[TR]
[TD]item 1
[/TD]
[TD]40
[/TD]
[TD]12
[/TD]
[TD]1
[/TD]
[TD]?
[/TD]
[TD][/TD]
[TD]55
[/TD]
[TD]29
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]item 2
[/TD]
[TD]20
[/TD]
[TD]8
[/TD]
[TD]2
[/TD]
[TD]?
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]item 3
[/TD]
[TD]6
[/TD]
[TD]3
[/TD]
[TD]24
[/TD]
[TD]?
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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?

[TABLE="width: 576"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="class: xl69, width: 192, colspan: 3"]1 unit value[/TD]
[TD="width: 64"][/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl70, width: 192, colspan: 3"]Total value required[/TD]
[/TR]
[TR]
[TD="class: xl65"]Item[/TD]
[TD="class: xl66"]a[/TD]
[TD="class: xl66"]b[/TD]
[TD="class: xl66"]c[/TD]
[TD][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68"]a[/TD]
[TD="class: xl68"]b[/TD]
[TD="class: xl68"]c[/TD]
[/TR]
[TR]
[TD]item 1[/TD]
[TD="class: xl67"]4[/TD]
[TD="class: xl67"]2[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl72"]7[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl71"]56[/TD]
[TD="class: xl71"]32[/TD]
[TD="class: xl71"]51[/TD]
[/TR]
[TR]
[TD]item 2[/TD]
[TD="class: xl67"]2[/TD]
[TD="class: xl67"]3[/TD]
[TD="class: xl67"]2[/TD]
[TD="class: xl72"]2[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD]item 3[/TD]
[TD="class: xl67"]6[/TD]
[TD="class: xl67"]3[/TD]
[TD="class: xl67"]10[/TD]
[TD="class: xl72"]4[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[/TR]
</tbody>[/TABLE]

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

 

[shg]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][/tr][tr][td]

1​

[/td][td="bgcolor:#F3F3F3"]

Item​

[/td][td="bgcolor:#F3F3F3"]

a​

[/td][td="bgcolor:#F3F3F3"]

b​

[/td][td="bgcolor:#F3F3F3"]

c​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td][/td][td="bgcolor:#F3F3F3"]

a​

[/td][td="bgcolor:#F3F3F3"]

b​

[/td][td="bgcolor:#F3F3F3"]

c​

[/td][td][/td][/tr]
[tr][td]

2​

[/td][td]item 1[/td][td]

4​

[/td][td]

2​

[/td][td]

1​

[/td][td]

7​

[/td][td][/td][td="bgcolor:#E5E5E5"]

56​

[/td][td="bgcolor:#E5E5E5"]

32​

[/td][td="bgcolor:#E5E5E5"]

51​

[/td][td]G2: =SUMPRODUCT(B2:B4, $E2:$E4)[/td][/tr]
[tr][td]

3​

[/td][td]item 2[/td][td]

2​

[/td][td]

3​

[/td][td]

2​

[/td][td]

2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td]item 3[/td][td]

6​

[/td][td]

3​

[/td][td]

10​

[/td][td]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[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

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][/tr][tr][td]

1​

[/td][td="bgcolor:#F3F3F3"]

Item​

[/td][td="bgcolor:#F3F3F3"]

a​

[/td][td="bgcolor:#F3F3F3"]

b​

[/td][td="bgcolor:#F3F3F3"]

c​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

a​

[/td][td="bgcolor:#F3F3F3"]

b​

[/td][td="bgcolor:#F3F3F3"]

c​

[/td][/tr]
[tr][td]

2​

[/td][td]item 1[/td][td]

4​

[/td][td]

2​

[/td][td]

1​

[/td][td="bgcolor:#E5E5E5"]

7​

[/td][td][/td][td]

56​

[/td][td]

32​

[/td][td]

51​

[/td][/tr]
[tr][td]

3​

[/td][td]item 2[/td][td]

2​

[/td][td]

3​

[/td][td]

2​

[/td][td="bgcolor:#E5E5E5"]

2​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td]item 3[/td][td]

6​

[/td][td]

3​

[/td][td]

10​

[/td][td="bgcolor:#E5E5E5"]

4​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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