Hey Guys,

I'm stuck and need some help.

I need a formula to calculate a sales Bonus

Here's the Pay plan & Qualifier:

<tbody>

</tbody>

=(VLOOKUP(K5,$W$4:$X$11,2,TRUE) + (IF(K5>25,(K5-25)*100,0))) * (IF(F5+G5>=5,115%,1)) * (IF(F5+G5>=8,130%,1)) * (IF(F5+G5<2,0,1))* (IF(F5+G5>=3,1,1))

<tbody>

</tbody>

Thanks guys for helping out. Please reach out if you need any additional info.

I'm stuck and need some help.

I need a formula to calculate a sales Bonus

Here's the Pay plan & Qualifier:

**14 UNITS = $500 BONUS*<o></o>****18 UNITS = $750 BONUS*<o></o>****20 UNITS = $1,000 BONUS*<o></o>****22 UNITS = $1,250 BONUS*<o></o>****25 UNITS = $1,500 BONUS*<o></o>***$100 BONUS PER CAR OVER 25 UNITS*

**QUALIFIERS**

**3 NEW VEHICLEDELIVERIES= 100% OF BONUS<o></o>****5 NEW VEHICLEDELIVERIES = 115% OF BONUS<o></o>****8 NEW VEHICLEDELIVERIES = 130% OF BONUS**

Column F has New Cars Sold

Column G has New Trucks Sold

Column K has Total Sold

Column M is where the formula will go

Keep in mind we need to add both Column F & G to come up with the qualifiers

Here is currently what I'm using but something in the formula is not right.

I'm using a VLOOKUP formula and putting this in columns W & X and I'm hiding the columns

Column F has New Cars Sold

Column G has New Trucks Sold

Column K has Total Sold

Column M is where the formula will go

Keep in mind we need to add both Column F & G to come up with the qualifiers

Here is currently what I'm using but something in the formula is not right.

I'm using a VLOOKUP formula and putting this in columns W & X and I'm hiding the columns

Cars | Bonus |

0 | 0 |

14 | 500 |

18 | 750 |

20 | 1000 |

22 | 1250 |

25 | 1500 |

99999 | 1500 |

<tbody>

</tbody>

=(VLOOKUP(K5,$W$4:$X$11,2,TRUE) + (IF(K5>25,(K5-25)*100,0))) * (IF(F5+G5>=5,115%,1)) * (IF(F5+G5>=8,130%,1)) * (IF(F5+G5<2,0,1))* (IF(F5+G5>=3,1,1))

NEW CAR | NEW TRUCK | USED CAR | USED TRUCK | SoldMA/HY | Total | Demo Bonus | UnitBonus |

1.5 | 4 | 3.5 | 6 | 0 | 15 | $300 | $575 |

1 | 9 | 3 | 7 | 0 | 20 | $300 | $800 |

3.5 | 4 | 2 | 2.5 | 0 | 12 | $200 | $- |

3 | 1 | 7 | 3 | 0 | 14 | $300 | $500 |

3 | 7 | 4 | 5 | 0 | 19 | $300 | $975 |

1 | 4 | 2 | 3.5 | 0 | 10.5 | $- | $- |

3 | 2.5 | 5 | 1 | 0 | 11.5 | $- | $- |

0 | 3 | 3 | 4 | 0 | 10 | $150 | $- |

1 | 9.5 | 4 | 8.5 | 0 | 23 | $300 | $1,625 |

4 | 4 | 2.5 | 9 | 0 | 19.5 | $300 | $975 |

0 | 0 | 0 | 0 | 0 | 0 | $- | $- |

0 | 2 | 3 | 2.5 | 0 | 7.5 | $- | $- |

2 | 3 | 4 | 3.5 | 0 | 12.5 | $300 | $- |

0 | 8.5 | 7 | 2 | 0 | 17.5 | $300 | $650 |

0 | 2 | 0 | 3 | 0 | 5 | $- | $- |

1 | 2 | 1 | 2.5 | 0 | 6.5 | $- | $- |

0 | 2.5 | 3 | 1 | 0 | 6.5 | $- | $- |

0 | 0 | 3 | 3 | 0 | 6 |

<tbody>

</tbody>

Thanks guys for helping out. Please reach out if you need any additional info.

Last edited: