multiple values dependent on their own sum (circular reference)

I have a set of values which always have to add up to a certain value, but if one of these values is change, all the other values should make up for it.
Is there a way of doing this without using VBA?

In a bit more detail:

I have a number of products with a set fixed cost (production, shipment and so on). On the other hand I have costs which are general and have to be distributed onto the products.
So far so good and not a problem.
However, after adding the indirect cost, some products end up being well above a certain pre set limit for this particular product, so I want to manually reduce it to be within this limit. When I do this, the part of indirect cost, which now is not covered by this particular product, has to be taken up by the remaining products.

Since the whole thing should be automated I cannot do it for every individual case separately.

Try this:
A1:A3 =

Formula:

Please Login or Register  to view this content.

A4 =

Formula:

Please Login or Register  to view this content.

File->Options->Formulas->Enable Iterative Calculation.

Well, unless you have additional detail or a sample workbook, VBA will be your best bet. You can make user-defined functions that act like normal excel functions but might not be restricted by circular references.

You can set macros to run at the click of a button, when someone opens the sheet, when a value is changed, etc. Or you can set them up as functions so they act like excel functions. I'm fairly sure the iterative calculation approach would work too, but if were me, I would make a macro, either as a user-defined function, or triggered whenever someone changes a value on the sheet.

For example, if you were making a macro to activate when the worksheet changed, you would add this code to the worksheet module. Change rng to match the cells you want to activate the macro.

Please Login or Register  to view this content.

As a mathematician, I tend to first make these math problems before making them programming problems.

As a math problem, this looks like it should be simple addition, so it should not need iterative algorithms. I see:

totalcost(i)=productspecificcosts(i)+generalcosts*fraction(i)<=maxtotalcost(i)

By solving the right inequality for fraction(i), we can easily compute the maximum fraction of general costs that can be attributed to product(i). Once I have a list of these maximum fraction(i)'s, I can then use that to inform how I should distribute the general costs across the products. You have not described anything about how you want to distribute these costs across the products (as evenly as possible? in proportion to total cost? some other criteria?). With a solid calculation of the maximum that each product can receive, and some idea of how you want to distribute these general costs, it should be possible to simply create a function that will decide the distribution up front, without the need for iterative calculations or VBA or other complications (most of the real work is in the algebra, though, so this may not be suitable for the mathophobic).

In the example, I assume that I want a strictly even distribution of the general costs. I also assume that any costs that need to be "pushed" get pushed into product i (without any checks to see if product i goes over). By pressing F9, you can update the random numbers and see what happens in different scenarios.

MrShorty,

I like your approach. I have a question about it though. Maybe I'm misunderstanding the original question, but I thought the numbers should add up to the same total. When I look at your sheet, the total changes. What am I missing?

Recognize that the constantly changing nature of my spreadsheet is due to the RAND() functions, which change with each calculate event. Each set of random numbers represents a completely new scenario. If needed, you can copy -> paste special -> as values to fix these random numbers and look at a single random scenario.

My understanding was that the general or indirect cost was a constant. In the algebraic formulation I envisioned, this is represented as sum(fraction(i))=1, which I force to be true in E13 and F13. If you add a column =$D$2*F5, you will see that the sum of this column will always equal $D$2 (with allowance for rounding error).

That makes sense. It seems like the totalcost for product i goes over its max cost, which isn't what I would expect.

As I noted, the way I set this up, all of the "extra" cost gets pushed into product i, and I did not go to the trouble to rectify that, because I was not sure exactly how the OP would want to distribute this. If this were the preferred method, I would probably sort the data by "max fraction" so that product a is the least likely to be able to accept its full allotment, and product i would almost certainly be able to absorb the excess. Then you would not see that.