Circular References

Thanks for reading


I'm trying to make a circular reference work for me. I'm creating a unit cost sheet for construction estimating. In column D, I have the total number of units. In column F, G, and H, I have low, mid, and high unit cost prices, respectively. My goal here would to be track down my unit costs, plug those numbers in, and calculate my low, mid, and high totals in columns I, J and K. To do this, in cell I5, my low total cost, I used: =$F5*D5. This calculates my total units multiplied by the unit cost.

What I would like this function to do is if I am not able to get a unit price, but I get a bid for a total cost and I know the total number of units, To have cell F5, a unit cost column, to equal: $I5/D5.

When originally trying this formula, I got the circular reference notification so I went into my excel options and checked the box "enable iterative calculation"

It looks like it works, however, I have one complaint.

If I enter the unit cost and it calculates the total, I cannot go back and change the total and have it calculate the new unit cost. Is there any way I get can the formulas to update based on the most recent data entered? I have attached a screen shot for clarification.

Thanks!


spread sheet.jpg