Can't Figure Out Goal Seeking. Help!

[Piper]

The problem:

Figure 1
Figure 2
Figure 3
Figure 4
Total (of above four figures)

But Figure 2 MUST BE 10% of the total.

How do I do this in Excel 2003?

 

[joeu2004]

The problem: Figure 1
Figure 2
Figure 3
Figure 4
Total (of above four figures)
But Figure 2 MUST BE 10% of the total.
How do I do this in Excel 2003?

Suppose your "figures" 1 through 4 are in A1:A4 and A5 is =SUM(A1:A4).

In A6, put the formula =A5*10%-A2.

Then set up Goal Seek with A6 in Set Cell, zero in To Value, and A2 in By
Changing Cell.

By the way, you can compute A2 directly. You require:

A2 = (A1+A2+A3+A4)*10%

So algebraically:

A2 = A2*10% + (A1+A3+A4)*10%

A2*(1-10%) = (A1+A3+A4)*10%

Note that 1-10% = 90%. So the formula in A2 can be:

=(A1+A3+A4)*10%/90%

or more simply:

=(A1+A3+A4)/9

 

[Piper]

Suppose your "figures" 1 through 4 are in A1:A4 and A5 is =SUM(A1:A4).

You are correct.

In A6, put the formula =A5*10%-A2.

Then set up Goal Seek with A6 in Set Cell, zero in To Value, and A2 in
By Changing Cell.

By the way, you can compute A2 directly. You require:

A2 = (A1+A2+A3+A4)*10%

So algebraically:

A2 = A2*10% + (A1+A3+A4)*10%

A2*(1-10%) = (A1+A3+A4)*10%

Note that 1-10% = 90%. So the formula in A2 can be:

=(A1+A3+A4)*10%/90%

or more simply:

=(A1+A3+A4)/9

My head is spinning.

But I'll try it out.

Thanks.