One for a formula wizard

Posted by Robb on December 13, 2001 3:21 AM

My problem is to break a circular reference and come up with an answer that is, if not exact, at least fairly close to the requirements.

To put an example in the context of a worksheet:

A1 = Person A B1 = Person B

A2 = 400 B2 = 341 (these are results of calculations and B1 will always be less than A1)

A3 = 15,000 B3 = 25,000

A4 = A2 * (B3/(A3 + B3))

A5 = IF(A4&LT;B5,A2-A4,A2-B5) B5 = B2 - A5

Values in A2, A3, B2 and B3 vary according to the persons they represent.

The calculations in A5 and B5 were established in isolation from eachother by different companies and are now non negotiable. In other
words, we're stuck with them. To the best of my abilities, the calculation is actually impossible. My task,however, is to come up with a solution
that will produce a result close enough to satisfy both formulae.

Any assistance or suggestions would be very much appreciated.

Thanks and regards

Robb

Posted by Robb on December 13, 2001 3:46 AM

Posted by Robb on December 13, 2001 3:47 AM

Posted by Robb on December 13, 2001 3:50 AM

For some reason the line with A5 etc keeps corrupting. It should read:

A5 = If(A4&LT;B5,A2-A4,A2-B5) B5 = B2 - A5

Posted by Robb on December 13, 2001 3:51 AM

For some reason the line with A5 etc keeps corrupting. It should read:

A5 = If(A4 &LT; B5, A2-A4, A2-B5) B5 = B2 - A5

Posted by Florizel on December 13, 2001 5:01 AM

You say that "B1 will always be less than A1".
I assume you mean "B2 will always be less than A2".

If my assumption is correct, I don't think your formula can work :-

1. Let's say that A4>B5.
2. Therefore A5=A2-B5.
3. Since B5=B2-A5, then A2-B5=B2-B5.
4. Therefore, A2=B2.
5. But you say that B2 will always be less than A2.

Posted by Aladin Akyurek on December 13, 2001 8:02 AM

Maybe...

you need to activate Tools|Options and check Iteration on the Calculation tab, which boils down "accepting" circular reference. This is what I got:

{"Person A","Person B";400,341;15000,25000;250,0;12141,-11800}

When Excel recals, you'll get another pair as 12141,-11800.

Aladin

========== For some reason the line with A5 etc keeps corrupting. It should read:

Posted by Robb on December 13, 2001 12:43 PM

Yes - I too think it is impossible, at least in its present form.

What I am trying to do is come up with a method to calculate results
for Person A and Person B that will, at least, come close to satisfying
the requirements of the formulae. In other words, coming up with results
that will manipulate the values to almost work.

Thanks and regards

Posted by Robb on December 13, 2001 12:48 PM

Re: Maybe...

Thanks Aladin.

I have tried doing that and the problem is that it results in huge positive and
negative values. What I am trying to do is come up with a different method of
obtaining values that almost meet the requirements of the formulae. In other
words, I have given up on trying to get it exact as I think it is impossible, but
perhaps there may be a way to manipulate values to come close.

Thanks again and regards

Robb