EXCEL IF Formula Error for graded Supplier Performance Incentive Payments

There are logical errors in your formula.

• The result of the top-level IF expression is always divided by 100. Under certain conditions, this expression evaluates to "". This value cannot be used numerically like this. If you intend for the IF to actually return "" under certain conditions, then you will need to move the division inside this IF for each of the sub-expressions that are numeric.
• When G16 is 188, the first IF is false, the second IF is true, and the third IF is false with a final result of "". When testing multiple > conditions, you need to test the largest first and the rest in descending order.

It would simplify your formula if it looked like

IF(G16>=O16,do whatever,
   IF(G16>=P16, do whatever when already known to be < O16,
      IF(G16>=Q16, do whatever when already known to be < P16,
         do whatever when < Q16)))

This could be further simplified using IFS instead of IF.

Dear Rino Quirante,

Welcome to Microsoft Q&A. 

Your goal is a graded incentive: 150% at/under stretch, 100% at target, 0% at/over minimum, with linear steps in between. The clean, robust way to implement this in Excel is the piece‑wise IF below (returning a multiplier, then formatting the cell as Percentage):

=IF(G16<=Q14, 1.5,IF(G16<=P14, (100 + (P14 - G16)/(P14 - Q14)*50)/100,IF(G16<=O14, (100 - (G16 - P14)/(O14 - P14)*100)/100,0)))

Expected results

G16 = 112.5 → 150%

G16 = 150.0 → 100%

G16 = 187.5 → 0%

G16 > 187.5 (e.g., 188) → 0%

If you want to return a percentage (without %) formatting, you can use:

=IF(G16<=Q14, 150,IF(G16<=P14, 100 + (P14 - G16)/(P14 - Q14)*50,IF(G16<=O14, 100 - (G16 - P14)/(O14 - P14)*100,0)))

I hope this information helps point you in the right direction. If you run into any issues while trying the steps, or if something still doesn’t feel quite right, please don’t hesitate to reach out again. I’ll do my best to support you however I can.  

Looking forward to hearing back from you with any updates or additional details. 

Warm regards, 

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".    

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.