Sales Bonus Formula

[jkharms]

I need a formula for calculating a year-end bonus payment based on the following plan:

The total in F8 will be increased by 10% if the totals in each of any of the three possible consecutive combinations of 4 cells (i.e. A8B8, B8C8, C8D8) are positive. Zero does not qualify as positive.

If the totals in either of the two possible consecutive combinations of three cells (i.e. A8B8C8, B8C8D8) are positive, F8 will be increased by 25%.

If the totals in all four cells are positive, F8 will be increased by 50%.

 

[mancemonster]

Quick question..

A8B8 do you mean A8+B8, or A8*B8?

Regards

 

[mancemonster]

I guess you would need something like this...

=IF(AND(A8>0,B8>0,C8>0,D8>0,E8>0),(F8*1.5),IF(OR(A8+B8+C8>0,B8+C8+D8>0),(F8*1.25),IF(OR(A8+B8>0,B8+C8>0,C8+D8>0),(F8*1.1),F8)))

 

[shg]

Try this:

=CHOOSE(MAX(FREQUENCY(IF(A8:D8>0, COLUMN(A8:D8)), IF(A8:D8<=0, COLUMN(A8:D8)))) + 1, 1, 1, 1.1, 1.25, 1.5) * F8

The formula MUST be confirmed with Ctrl+Shift+Enter

 

[jkharms]

Response to mancemonster onSales Bonus Formula

Each of the cells is qualified individually. A8 has to be positive AND B8...the same with the other combinations...for the 10% increase to be granted. Each of the columns (A,B,C,D) represent quarters of the year...and what's being judged is the salesperson's performance in each of those quarters. To earn the bonus increases, performance has to be positive for consecutive quarters, either two in row, three or four.
I hope that helps. I much appreciate your assistance.

Jack

 

[shg]

You might try my formula.

 

[jkharms]

Response and Question to shg4421

I will certainly try the formula. Not being all that familiar with the myriad of Excel options, I'm not certain what actions the formula is describing. Could you explain to the novice user what the various elements of the formula are defining?

Thanks.

 

[shg]

Frequency takes two arrays of numbers, data and bins. The output is an array one larger than the number of bins of the counts of data items in each bin. More specifically, the first value is the count of items less than or equal to the first bin value, the second bin is the count of items > the first bin and <= the second bin value, and so forth. The last value is the count of items larger than the largest bin value.

Here, the data is the column numbers of the cells with positive values, and the bins are the column numbers of cells with non-positive values. Each bin therefore counts the number of positive values that directly precede it.

 

[jkharms]

I inserted the formula per your direction as shown below. (I moved F8 to E8 in the test), but received a #Value error message.
Each of the columns A:D had a positive number inserted and E was the calculated sum of the four.

I'm using a MAC OSX.4, so I'm not certain if I'm accomplishing the Shift-Control-Enter command correctly.

=CHOOSE(MAX(FREQUENCY(IF(A8:D8>0,COLUMN(A8:D8)),IF(A8:D8<=0,COLUMN(A8:D8)))) + 1,1,1,1.1,1.25,1.5 * E8)

jkharms[/QUOTE][/QUOTE]

 

[shg]

Paste the formula in the formula bar, but don't press Enter. Instead, press and hold the Control and Shift keys, then press Enter.

The formula in F2 and copied down is

=CHOOSE(MAX(FREQUENCY(IF(A2:D2>0,COLUMN(A2:D2)),IF(A2:D2<=0,COLUMN(A2:D2)))) + 1,1,1,1.1,1.25,1.5) * E2

Note that the E2 is outside the parens.

       A- B- C- D- --E-- ---F----
   1   Q1 Q2 Q3 Q4  Amt  w/ Bonus
   2   0  0  0  0  $ 100    $ 100
   3   1  0  0  0  $ 100    $ 100
   4   0  1  0  0  $ 100    $ 100
   5   1  1  0  0  $ 100    $ 110
   6   0  0  1  0  $ 100    $ 100
   7   1  0  1  0  $ 100    $ 100
   8   0  1  1  0  $ 100    $ 110
   9   1  1  1  0  $ 100    $ 125
  10   0  0  0  1  $ 100    $ 100
  11   1  0  0  1  $ 100    $ 100
  12   0  1  0  1  $ 100    $ 100
  13   1  1  0  1  $ 100    $ 110
  14   0  0  1  1  $ 100    $ 110
  15   1  0  1  1  $ 100    $ 110
  16   0  1  1  1  $ 100    $ 125
  17   1  1  1  1  $ 100    $ 150