That formula is solving the wrong problem. The user who posted this knows exactly what they want, a percentage that falls as the actual score rises above a low target, but they're fighting their spreadsheet to get it. That's not a failure of math. It's a failure of design.
Traditional spreadsheets treat formulas like standalone logic puzzles. You build one, test it, realize it breaks on edge cases, then patch it with nested IF statements until the formula bar looks like a ransom note. The user's current approach, calculating a ratio and capping it at 100%, works fine when "higher is better." But the moment your target is a ceiling rather than a floor, that logic flips. You need to penalize overshoot, not reward it. And the spreadsheet doesn't make that easy.
What this user actually needs is a single conditional expression that captures the inverse relationship: when the actual is within the target range, score is 100%; when it exceeds the target, score drops proportionally. That's not a complex idea. It's a simple IF statement combined with a MIN or MAX clamp. Yet the current formula they shared suggests they're writing around the problem instead of through it. We see this constantly, users spending more time debugging formulas than they would building a whole new tool from scratch.
This is exactly where AI-native spreadsheets earn their keep. Instead of forcing you to translate intent into brittle syntax, a smarter tool lets you describe the rule in plain language: "Give 100% when the actual is under 10%, then reduce the score by the percentage the actual exceeds the target." The tool handles the edge cases, division by zero, negative results, rounding, so you can focus on whether the rule makes sense for your business. That shift from "how do I write this formula" to "what outcome do I want" is the real transformation.
The practical takeaway is straightforward. If you're spending more time debugging than analyzing, the tool is the bottleneck. A formula that takes fifteen minutes to write and breaks on the first outlier isn't a solution, it's a liability. Explore a spreadsheet that understands your intent, not just your keystrokes. Your data should work for you, not the other way around.