Revised formula isn't calculating properly

[jimsena96]

Attached is a extremely abridged version of a massive project in Excel that I have been duly working on for some weeks now. What I'm dealing with is of no real concern, but what does matter is trying to get this one formula functioning correctly so that I may continue in my endeavor. You will notice that three numbers in the L column are highlighted in red. This is to say that the quality of the product it belongs to is so low that underlying problems do not matter, and as far as I'm concerned the entire thing is a problem. Previously this was entered manually by using a simple recursive statement, =(Kx), though this was time consuming and wouldn't be immediately obvious to an outside observer, seeming almost to be a mistake.

Below is a formula that I have tried to apply but with no real luck:

=IF(AND(B2<25, C2+D2/2<10, E2<10, F2<50, J2<25), 0, ROUND(100 - L2/K2 * 100, 0))

What this means is in plain English is if the five scores are below the given ranges (Cx and Dx are connected, hence adding them together and dividing them by two), then Mx should be set to zero regardless of what number Lx mentions.
Should you replace the Time/Problem ratio equation in the attached spreadsheet with the above formula, you will notice that it reports a circular reference and doesn't do what it's supposed to, though for the life of me I can't tell where it goes back on itself at all. As far as I can see, each cell is referenced exactly once.

[TMS]

Not everyone will access file sharing sites. Please upload your workbook to the forum.



Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Raphaelp]

You'll need to attach the workbook to your post instead of using an external link. To attach a workbook, edit your post, click "Go advanced" and scroll down to "Manage attachments". This will open the upload window.

Edit: He beat me by a split second.

[daddylonglegs]

Your formula in J2 already references M2, so if you try to put the quoted formula in M2 you get a circular reference because that formula refers to J2

[jimsena96]

Edited OP.

Ok, so because J2 references M2 does that render my initial plans from the first post impossible, or just difficult? I'd like to know if there's a fix for the formula without having to add additional rows that further complicate the worksheet.

[daddylonglegs]

There are ways to allow Excel to work with circular references....you can turn on "iterative calculation" and let Excel try to get a valid result, see here

I'd check the logic first - if you really need it to work that way then you can try the method in the link