Circular Reference with Data Validation

[Guest]

I'm trying to get the following formula to work. I16 is a
data validation list (drop down list) and I keep getting
the circular reference error message, can anyone help me?

=IF(I16="Overages Allowed (incl. additional rev) Line Item
Budget Overages Disallowed",K16,IF(K15<=J15,0,IF
(K26<=J26,0,IF(K26+L30-J26<=MIN(0.2*(J15),200000),K26+L30-
J26,MIN(0.2*(J15),200000,IF(I16="Adjustments/Non-Allowable
Expenses",K17=-L15,IF(I16="Less: Greater of Line Item
Budget Overages OR Line 12 of CFR",K17=-L15)))))))

 

[Andy B]

Hi

I think you've put a couple of errors in there! You can't push values in
Excel, so K17=-L15 which appears twice in your monster may be the cause of
your problem.

 

[JulieD]

not sure about the circular reference but (as far as i can tell) you need a
")" after the last 200000
and then you have a problem

1st test: I16="Overages Allowed (incl. additional rev) Line Item Budget
Overages Disallowed",
1st true: K16
1st false - 2nd test : K15<=J15
2nd true: 0
2nd false - 3rd test: K26<=J26
3rd true: 0
3rd false - 4th test: K26+L30-J26<=MIN(0.2*(J15),200000)
4th true: K26+L30- J26
4th false: MIN(0.2*(J15),200000)

finished the IF statement but then you abut another IF statement against
this IF(I16="Adjustments/Non-Allowable Expenses"
rather than incorporating it in the false statement (like in 1st, 2nd & 3rd
situations)

hope this helps

Cheers
JulieD