PLEASE HELP Calculate condition and tier bonus

**sclasen0024** · 10-07-2015, 04:19 PM

I need to calculate a bonus paid to a salesperson based upon multiple factors.

IB Pay = hours per demo (f4) then it looks at the pay period (a2) depending on the pay period (due to seasonal volume) would dictate which bonus structure to use see tables below. Once you determine what the payout is for an IB in cell G4 you would take total IB (c4*the payout from tier)
Compensation.xlsx

**Mr_HiB** · 10-07-2015, 09:58 PM

Sclasen if you could show what the end result is supposed to be we can probably build a formula to get there.

Because the cell references you're giving is a bit confusing cell A2 is = 10 unsure what 10 is supposed to do.
Cell F4 is Agent 2's HPD which is 0.91... also unsure what to do with it....

Hilmy

**sclasen0024** · 10-08-2015, 08:35 AM

Mr HiB thank u so much for responding!!

Cell g2 = Based upon what the value is g3 (hpd) it would then go to the hours per demo table (the table in color). You can see there is one table that contains two separate tiers of 6 based upon the months of the year (seasonal volume) for example

Agent 1 result for IB pay g4, HPD = 1.31 I look at the table, since it is based upon results from the 10th month cell a2 that would be October so the ib demo for 1.31 hpd=tier 3 g4= the result from tiers $5.00. we would need to have both tables (seasonality built into the formula) does this make sense?

**Mr_HiB** · 10-08-2015, 11:15 PM

Hi Sclasen,

I think I know what you mean - but the references you've given don't all marry up especially the $5 reference... but I've attached a spreadsheet with functions that I think gives you the results you want. Below let me just break it down and see if we're on the same page... and if you can correct anything that I've written it might help other volunteers in case I get stuck or misunderstood you.

1) Ok - so you're making a reference to cell G4 (I assume it's a cell reference) but that has nothing to do with Agent 1, isn't G4 the IB Pay for Agent 2....???
2) For ease of discussion let me reference the two bonus tables - so the table that's in columns U-AA; let's call it Table A and the other one in Columns AC - AF Table B.

Table A gives us the Bonus Level based on the HPD (Hours Per Demo) for specific time periods. So for a pay period in Mar - Oct the bonus level is based on the HPD (in column F) that's within the range between column V and W, and for Nov - Jan the bonus levels are assigned if the HPD (in column F) is within the range between columns X and Y.

Example: So in the case of Agent 1, the pay period is 10 (October). The calculated HPD (Cell F3) is 1.31 so the Bonus level is 3 because the HPD is between 1.20 and 1.49; that means the IB Demo rate is $9, no? But in your explanation above you say it's $5 I don't understand that.

3) Lastly on Pay Periods - and just to make sure your data is correct. I noticed that February is not part of Table A's bonus structure.... so the formula for OB Pay and IB Pay will give you a 0 if the pay period is 2 (February). I guess/assume that February is not part of the bonus season on the IB and OB metric.

So please clarify anything that's incorrect with my spreadsheet attached; and also correct any errors in the explanation that I've given above for the benefit of anyone else reading this to either help or understand your problem.

My added Bonus to you --- I just can't help it, but I want to automate and make as much efficiency as possible with my spreadsheets. For the pay period I know you can just type in a number... now if that's what you prefer by all means go ahead... However, I copied your sheet and made a 2nd one (Sheet 1 (Bonus on Pay Period) where the ONLY difference is how you select the pay period. Instead of typing in a Pay Period in cell A2. You can Select the Pay Date in cell A1 (from a dropdown menu, based on the list that you have to the far right on the sheet) and a vlookup in cell A2 will give you the right Pay Period... I also don't like the fact that it's just a number... I prefer to have things explained for someone who picks up the sheet (if you're printing it) so I added the words "Pay Period" using custom format.

So here are the functions:
I used If, coupled with OR and AND functions, Sumproduct (very important) - the Or is just simply to catch the problem if the Pay Period is blank ""
For the bonus on the dropdown and payperiod I used
Offset, Vlookup, iferror in case A1 is blank.

IB Pay (column G)
=IF(OR($A$2=2,$A$2=""),0,IF(AND($A$2<11,$A$2>2),SUMPRODUCT(($V$4:$V$9<=$F3)*($W$4:$W$9>$F3),$AA$4:$AA$9),SUMPRODUCT(($X$4:$X$9<=$F3)*($Y$4:$Y$9>$F3),$AA$4:$AA$9)))

OB Pay (column H)
=IF(OR($A$2=2,$A$2=""),0,IF(AND($A$2<11,$A$2>2),SUMPRODUCT(($V$4:$V$9<=$F3)*($W$4:$W$9>$F3),$Z$4:$Z$9),SUMPRODUCT(($X$4:$X$9<=$F3)*($Y$4:$Y$9>$F3),$Z$4:$Z$9)))

Site Rate incentive (column P)
=SUMPRODUCT(($AD$4:$AD$7<=$O3)*($AE$4:$AE$7>O3),$AF$4:$AF$7)

The added bonus I made on "Sheet 1 (bonus on pay period)"
For the dropdown (cell A1) - you can find it Data - Data Validation - List
=OFFSET($AJ$1,1,,COUNTA($AJ:$AJ)-1,1)

For the Pay Period Month (cell A2)
=IFERROR(VLOOKUP($A$1,$AJ:$AK,2,FALSE),"")

Sincerely,
Hilmy