The way I shortened the formula is by taking advantage of repeated sub-structures in your original formula. Since I did not realize that row 5 was intended to have a descending set of values, the formula assumes that everything is ascending. If that is not the case, then the formula will not work. To fix it, it would require more than doubling the size of it. If that were the case, you'd be better off with your original formula. But even then, you would have the same formula in G3:G4, and G5 would have to be different.
But the relationship on row 5 still seems to be linear, just in the other direction. It's possible to remap those values into an ascending order with a simple trick. Just subtract all the values in B5:F5 from 100, which I assume is the maximum value. This would change these values
95, 85, 80, 75, 79 (79 being the result) to
5, 15, 20, 25, 21. Then your sheet would look like:
| Book2 |
|---|
|
|---|
| A | B | C | D | E | F | G | H |
|---|
| 1 | | | | | | | | |
|---|
| 2 | Measure | Threshold | Target | Max for 1.5x | Max for 2x | Input Results | Result | |
|---|
| 3 | Total Sales | 25 | 75 | 100 | 125 | 115 | 1.8 | 1.8 |
|---|
| 4 | Department Sales | 10 | 15 | 20 | 25 | 15.4 | 1.04 | 1.04 |
|---|
| 5 | Expense Management | 5 | 15 | 20 | 25 | 21 | 1.6 | 1.6 |
|---|
| 6 | Payout Opportunity | 0 | 1 | 1.5 | 2 | | | |
|---|
| 7 | | | | | | | | |
|---|
| 8 | | | | | | Total Multiplier | 4.44 | |
|---|
|
|---|
You'd probably want to put a note on the sheet explaining the process on row 5.
If this doesn't work for you, I don't know what else to suggest, other than using your original formula(s).
