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?

"Piper" wrote:
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 requi

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

joeu2004 wrote:
"Piper" wrote:
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).

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 requi

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.