circular reference

[hfuk]

Hi all,

I am hoping you guys can help me.

I am trying to build a spreadsheet to work out the unit and case cost easily at work.

a1= case quantity

b1= unit cost. =SUM(C1/A1)

b2= case cost. =SUM(A1*B1)

I want to be able to insert either the case cost or the unit cost. Now I come up with circular reference and do what to do?

Please help

Regards
Ash

[swatsp0p]

You need to supply a fixed number, either the unit cost OR the case cost. Excel can't calculate either without a fixed reference of some type.

A1=case qty. (fixed number)
B1=unit cost (fixed number)
C1=case cost (=A1*B1)


--OR--

A1=case qty. (fixed number)
B1=case cost (fixed number)
C1=unit cost (=B1/A1)

--OR--

A1= case qty. (mandatory fixed entry)
B1= unit cost (optional fixed entry or left blank)
C1= case cost (optional fixed entry or left blank)
D1= calculated case cost (formula: =IF(B1="","",A1*B1)
E1= calculated unit cost (formula: =IF(C1="","",C1/A1)

Do any of these work for you?