Calculation Problem

L

Larry

How can I structure the worksheet to solve this problem. The cell
value is part of the total but the cell calculation depends on the
total.

For example:

Cell B1= 3% of cell B4
Cell B2= 5% of cell B4
Cell B3=10
Cell B4=sum(B1:b4)

How can cells B1 & B2 be calculated when they're part of the total?
Thank you!
 
P

Phobos

Larry said:
How can I structure the worksheet to solve this problem. The cell
value is part of the total but the cell calculation depends on the
total.

For example:

Cell B1= 3% of cell B4
Cell B2= 5% of cell B4
Cell B3=10
Cell B4=sum(B1:b4)
Click to expand...

Dont you mean:

Cell B4=sum(B1:b3)?
 
J

JohnK

They can't. When your inputs and result are interrelated,
that's called a circular reference. Excel won't allow
it.

'm not sure what you're trying to accomplish. but usually
you can use the Solver feature to quickly arrive at your
target. For your example, make cell B4 simply a number.
Then set cell B5=sum(B1:B4). Then use the Solver feature
(its an add in) or Goal Seek feature to optimize B4 for
the number you're trying to get.

Good Luck.
 
R

Ron Rosenfeld

How can I structure the worksheet to solve this problem. The cell
value is part of the total but the cell calculation depends on the
total.

For example:

Cell B1= 3% of cell B4
Cell B2= 5% of cell B4
Cell B3=10
Cell B4=sum(B1:b4)

How can cells B1 & B2 be calculated when they're part of the total?
Thank you!
Click to expand...

Tools/Options/Calculations/Iteration


--ron
 
P

Phobos

Ron Rosenfeld said:
Tools/Options/Calculations/Iteration
Click to expand...

Ron,

That was my first thought, but Cell B4 is the sum of cells B1:B4 (including
itself) so each iteration just pushes the values up.

P
 
D

Dana DeLouis

I don't believe your equation is converging. It looks like your equation
grows without limit.
 
R

Ron Rosenfeld

Ron,

That was my first thought, but Cell B4 is the sum of cells B1:B4 (including
itself) so each iteration just pushes the values up.

P
Click to expand...

I thought that was probably a typo.


--ron
 
H

Harlan Grove

Larry said:
How can I structure the worksheet to solve this problem. The cell
value is part of the total but the cell calculation depends on the
total.

For example:

Cell B1= 3% of cell B4
Cell B2= 5% of cell B4
Cell B3=10
Cell B4=sum(B1:b4)

How can cells B1 & B2 be calculated when they're part of the total?
Click to expand...

Try this.

B1: =0.03*B4
B2: =0.05*B4
B3: 10
B4: =10/(1-0.03-0.05)
B5: =SUM(B1:B3)=B4
 
J

Jay

Larry said:
How can I structure the worksheet to solve this problem. The cell
value is part of the total but the cell calculation depends on the
total.

For example:

Cell B1= 3% of cell B4
Cell B2= 5% of cell B4
Cell B3=10
Cell B4=sum(B1:b4)

How can cells B1 & B2 be calculated when they're part of the total?
Click to expand...

They can be calculated because another way of stating the sum is to say
that B1+B2+B3=0. You can use this observation to restructure the worksheet
without a circular dependency.

Cell B1= = .03*B4
Cell B2= = .05*B4
Cell B3= 10
Cell B4= (blank for now)
Cell B5= =B1+B2+B3

Tools >> Goal seek >>
Set B5 to value 0 by changing B4

Or you can set Excel aside and just use algebra to solve the problem.
 
J

Jay

Harlan said:
The correct approach more often than may be supposed.
Click to expand...

Yes, but I admit being a little flippant.

Maybe this is one example, typical of many problems that need to be solved.
If so, here's a way to get the answer without having to use goal-seek for
each problem.

A1: 0.03
A2: 0.05
A3: 10
B1: =A1*B$4
B2: =A2*B$4
B3: =A3
B4: =-A3/(A1+A2)

The last formula comes from algebra.
 
Top