Circular Reference Help

[lawre2000]

Hello All,

I was hoping that I could get some ideas on how to avoid a circular reference given the following scenario:

A1 = 2
B1 = 4
+ C1 = 5
D1 = 11

If the total above in cell D1 is greater than 10, than I would like to exclude the value in cell C1 so that the total in cell D1 only reflect A1+B1. Essentially making the value of C1 equal to zero. The answer would be 6 in this example. If the total is less than 10, I would like to keep the total of all 3 variables in cell D1. This is obviously a circular reference but is there a way to get around this?

Thanks!

 

[West Man]

No circular reference needed. Try: =IF(SUM(A1:C1)>10,A1+B1,SUM(A1:C1))

 

[lawre2000]

For presentation purposes, I will need the value of C1 to reflect zero or its original value based on the value of D1. Thoughts?

 

[West Man]

Ah, now I understand. The closest I can think of is to use a conditional format on C1 so that when you would like it to be zero, the font color is changed to the background color making it appear to be an empty cell. It is not what you are asking for but the the nearest I can get.