Thursday, February 1, 2018

yogi_Compute commission (or Pay) Based On Tiered Brackets Of Profit Generated

Google Spreadsheet   Post  #2358

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Feb-01-2018
question by JenniferHowell
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/Q7DGCqhKJzM;context-place=forum/docs

How to formulate commission structure based on tiered brackets of profit generated?

ADD A REPLY
I am trying to create a calculated commission structure based on a tiered program. The tiers look like this:

Profit Per Employee
Pay %
Tier 1: $0-$10,000.00
6%
Tier 2: $10,000.01-15,000.00
8%
Tier 3: $15,000.01-$20,000
11%
Tier 4: $20,000.01-$25,000
14%
Tier 5: $25,000.01-$30,000
17%
Tier 6 : $30,000+
20%
Employees will receive pay based on each individual tier. For Example:

$18,000 TOTAL PROFIT TO BASE COMMISSION ON
6% x $10,000 = $600
8% x $5000 =  $400
11% x $3,000 = $330
$1230 USD TOTAL COMMISSION PAYOUT

I am sure it is probably a simple IF statement. I have used them in the past for simpler commission structures that are not broken up into segments. Can someone please assist me with the correct IF stament or formula?

Thank you!