I am looking for advise to best calculate and return a total result value based on progressive step values.

The attached excel file shows the scenario.

Input:

Total Unit Sales

Min Units Required

Step Award Amount

Based on Total Unit Sales, an award amount is given. Minimum required Unit Sales is 2

For achieving 2 Unit Sales the Award is 200

For each additional Unit Sale >2 the award on the unit is progressive by 100. So the third unit achieves award of 300. 4th unit achieves award of 400 and so on.

What i need help on is returning the cumulative award value based on given total unit sales. The green highlighted area in the attached excel file. So based on Unit Sales of 4 units then the result would be:

Award for 2 unit sales of 200 + Award to 3 unit sales of 300 + Award for 4 unit sales of 400 =900

Perhaps a custom function?

Thanks !

hi Alan,

Attached is a file with custom function that should do what you are after. It allows you to define all of your parameters:

• Minimum Units

• Actual Units Sold

• Starting Value

• Increment Value