I feel like this is fairly difficult to explain, but isn't all that hard to figure out -- I'm just hitting a wall:

In the attached spreadsheet, I have four columns: Essentially, in the final ("Score") column, I have scores pretty easily based on the previous three columns. If the type is "A" or "C," I don't take into account its variation -- only if it's "B." If it's B, I take the previously determined value (column C) and subtract the variation from it first before I get the score. Otherwise, with "A" or "C," the 'Score' is just the 'Value' negative.

All of those formulas are easy and work fine. The part where I'm running into trouble is that I want any 'Value' that's greater than or equal to 0 (in this case Rows 2-29) to be 0.2 points lower than the lowest 'Score' if the 'Value' is negative. In the attached spreadsheet, I can accomplish this easily by seeing that the lowest 'Score' for a negative 'Value' is -2.4150841 (Cell D30), so I can just make the true part of IF statement "$D$30-0.2" and it works exactly as I'd like it to -- but the problem is that I need the formula to act on its own somehow. The easiest way, if it worked, would be to somehow say if the value is negative, return the minimum score and subtract 0.2 from it -- but that obviously doesn't work, because it's in the same column, so you get a circular reference.

I hope that makes some sort of sense! Can anybody help me get the same results as in my attached spreadsheet, but using something automatic instead of the manual "$D$30-0.2" in the =IF(C2>=0,$D$30-0.2,IF(A2="A",-C2,-C2+B2)) formula?

SampleExcelProblem.xlsx

Thank you!