Need some sort of circular reference, but without an error
Posted by Bob on July 10, 2001 1:35 PM
I am trying to set up a spreadsheet so that the user can enter a value in one of two columns (current billing or percent complete)and the other column will automatically calculate the correct value via a formula. The problem is that creates a circular reference. Following is a simplified spreadsheet showing what I mean. Any ideas?
A B C D E
Budget Previous Current Completed Percent
Billing
$50.00 $15.00 $22.00 =b2+c2 Need calc'd
$40.00 $10.00 Need =b3+c3 50%
calc'd
Posted by Bob on July 10, 2001 1:37 PM
(looks wacked because spaces are eliminated, but they show if you enter "Post Followup"
Posted by Joe Was on July 10, 2001 3:08 PM
Is this the logic?
Does (Previous + Current)/Budget = Percent?
As (B2+C2)/A2 in E2?
Does (Percent * Budget) - Previous = Current?
As (E2*A2)-B2 in C2?
Is this what you want or am I missing it? JSW
Posted by Bob on July 11, 2001 11:22 AM
Sort of
Yeah, those formulas both work, but my goal is to have both formulas entered before the user gets there. Then they will overwrite one of the formulas with a value. For example, some lines will be billed on a percent complete basis, the others will be billed on a dollar basis. If both your equations are entered it creates a circular reference, which is what I am trying to avoid. I know this is kind of weird, but I am stumped as to how to achieve my goal.
Posted by Joe Was on July 11, 2001 3:37 PM
Try, If(formula)
=IF(E2>0,(B2+C2)/A2,(E2*A2)-B2)
This tests to see if a percent was entered if it was then solve for "current" if it is not then solve for "percent" you can use the formula in any cell not containing the data. JSW
