Can't Figure Out Goal Seeking. Help!

P

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?
 
J

joeu2004

Piper said:
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
 
P

Piper

joeu2004 said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top