Hope you are well, I thank you so much for taking a looking at this problem.



I am looking to create a formula in excel that can calculate a commission by sales tier.

The user of the scale will entering a date range and total amount of sales to view the results of the commission to be paid.

The taxes for the retail sales are collected and will be in a different line item so the calculation will not include the taxes of the retail sale.

The user will be in control of the input information.

For example: They will enter the tier value then select either a dollar amount or percentage amount to calculate the $ commission amount to be paid.

Date Range April 1st 2017 thru April 30th 2017
Retail Sales $1000.00

Tier Value Amount Range
$0.01 to $100.00 Commission value either $ or %
$101.00 to $150.00 Commission value either $ or %
$151.00 to $250.00 Commission value either $ or %
$251.00 to $400.00 Commission value either $ or %
$401.00 to $450.00 Commission value either $ or %
$451.00 to $500.00 Commission value either $ or %
$501.00 to up Commission value either $ or %


Total Sale: $1000.00
Total Commission Pay out: $
Total blended commission rate as a %

Thank you for your help and support look forward to your response.