# Summing Column Without Circular Reference

I have a column of 5 rows that is used to calculate whether the item to its left (each row corresponds to an item on an invoice) qualifies for a discount. Each cell in the column applies the discount rule to that item that is in Column A, and if it qualifies for the discount, the cell value in Column B is "1".

So far, so good.

The problem is, that the customer is only entitled to 1 discount per order, regardless of whether the other items would otherwise qualify for the discount.

So, for example, if an item is more than \$100, then it qualifies for a discount. But I need to calculate whether another item has already been discounted, and in so doing, I am creating a circular reference.

``````B1 =IF(A1>100,IF(SUM(B2:B5)>0,1,0)
B2 =IF(A1>100,IF(B1+SUM(B3:B5)>0,1,0)
B3 =IF(A1>100,IF(SUM(B1:B2)+SUM(B4:B5)>0,1,0)
B4 =IF(A1>100,IF(SUM(B1:B3)+B5>0,1,0)
B5 =IF(A1>100,IF(SUM(B1:B4)>0,1,0)
``````

How can I achieve this without the circular reference?

• What's the criteria behind the discount selection? Commented Aug 16, 2013 at 3:54

In `B1` enter the formula
``````=1*(A1>100)
In `B2` enter the formula below and fill down.
``````=(A2>100)*(SUM(\$B\$1:B1)=0)