Circular referance problem

[kanuvas]

I am having problems solving a seemingly complex issue with circular
referances.

Name wgt %
aaaa 100 0.25
bbbb 100 0.25
cccc 100 0.25
dddd 100 0.25

Total 400

The user inputs the weight and this works out the percent based of the
total.

Now say i want to add a new name called "eeee". But i want to specify
the % and it works out the weight.
I would have to refer the % to the toal weight which would change when
it works out the actual weight creating a circular referance.

Anyone got any ideas how to resolve this problem?

 

[damorrison]

Assuming the columns are A B C
in cell C1 enter formula =B1/Total
copy that formula down the column
now if you name the cell that has the total you can then use the word
Total
select the cell that has the total then
open the window beside the = sign by the formula box and type in the
word Total then enter
now any time you write the word Total in a formula in the workbook it
will reffer to that cell range

 

[MrShorty]

My approach to this kind of problem is to use Solver/Goal seek. Set up
spreadsheet like you have, add the row, put a number in for wgt of eeee
and the formula to calculate its wgt%, then run Solver, set target cell
(wgt% eeee) to value of x%, by changing the appropriate wgt of eeee
cell, and let Solver solve the problem.