Commissions Calculator

[jswillcox]

I would appreciate some help with automatically calculating commissions.

How can I have it reflect a specific number when I have numbers within a field. ex

0 - 88 = 0%
89 - 97 = 50%
98 - 106 = 75%
107 - 115 = 100%
116 - 124 = 125%
125 - 133 = 150%
134 - greater = 200%

This will only be dealing with whole numbers or units sold.

In addition I am working on making a pacing section.
I know I can add the value in for the remainder of days left in the quarter. Is there any way to automate that?
The equation I would use is (Lines Sold/Days in quarter to date)*total days in quarter
I could add it manually, I was just hoping excel could put the days in quarter and total days left in the quarter automatically.

Thank you for your help!

 

[shg]

=lookup(a1, {0,89,98,107,116,125,134}, {0,50,75,100,125,150,200}%)

 

[jswillcox]

That took care of the commissions portion. Thank you SHG for your help!

How can I solve for the day in the quarter?

 

[shg]

Because I don't know what you're trying to do.

Why don't you provide a clear example of inputs and desired results?

 

[jswillcox]

Today is 30 days into this quarter Jan, Feb, Mar. These months have 91 days total (Jan = 31) + (Feb = 29) + (Mar = 31)

Currently I have a cell where I add how many days into the quarter we are.
Ex Feb 15 is 46 days in the quarter (Jan + current day in Feb 15) = 46.
I don't know if excel can automatically do this for me.

I also have a cell where I add the days in a quarter. Currently 91, (Jan + Feb + Mar) = 91

I hope that clarified things. If not please let me know so I can specify further.

 

[jswillcox]

=lookup(a1, {0,89,98,107,116,125,134}, {0,50,75,100,125,150,200}%)

Can I use this equation but add the numbers from other cells?
Ex. =lookup(a1, {0,C12,C13,C14,C15,C16,C17}, {E11,E12,E13,E14,E15,E16,E17}%)

When I try it is giving me an error.
I just ask because I am trying to make this available to others and I don't want them messing up the equation.

 

[shg]

the day in the quarter?

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr][tr][td]

1​

[/td][td]

Date​

[/td][td]

Days In​

[/td][td]

​

[/td][/tr]
[tr][td]

2​

[/td][td]

15-Jan​

[/td][td]

15​

[/td][td]B2: =A2 - LOOKUP(A2, DATE(YEAR(A2), {1,4,7,10},1)) + 1[/td][/tr]
[tr][td]

3​

[/td][td]

30-Jan​

[/td][td]

30​

[/td][td][/td][/tr]
[tr][td]

4​

[/td][td]

15-Feb​

[/td][td]

46​

[/td][td][/td][/tr]
[tr][td]

5​

[/td][td]

31-Mar​

[/td][td]

91​

[/td][td][/td][/tr]
[tr][td]

6​

[/td][td]

1-Apr​

[/td][td]

1​

[/td][td][/td][/tr]
[tr][td]

7​

[/td][td]

30-Apr​

[/td][td]

30​

[/td][td][/td][/tr]
[/table]

Can I use this equation but add the numbers from other cells?

Put a zero in C11, then

=lookup(a1, C11:C17, E11:E17)%

 

[jswillcox]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr][tr][td]

1​

[/td][td]

Date​

[/td][td]

Days In​

[/td][td]

​

[/td][/tr]
[tr][td]

2​

[/td][td]

15-Jan​

[/td][td]

15​

[/td][td]B2: =A2 - LOOKUP(A2, DATE(YEAR(A2), {1,4,7,10},1)) + 1[/td][/tr]
[tr][td]

3​

[/td][td]

30-Jan​

[/td][td]

30​

[/td][td][/td][/tr]
[tr][td]

4​

[/td][td]

15-Feb​

[/td][td]

46​

[/td][td][/td][/tr]
[tr][td]

5​

[/td][td]

31-Mar​

[/td][td]

91​

[/td][td][/td][/tr]
[tr][td]

6​

[/td][td]

1-Apr​

[/td][td]

1​

[/td][td][/td][/tr]
[tr][td]

7​

[/td][td]

30-Apr​

[/td][td]

30​

[/td][td][/td][/tr]
[/table]



Put a zero in C11, then

=lookup(a1, C11:C17, E11:E17)%

This did everything that I wanted! Thank you so much!

 

[jswillcox]

Last Question.

How do I change that formula to auto calculate the total days in the quarter?

 

[shg]

=lookup(a2, date(year(a2), {1,4,7,10}, 1), date(year(a2), {4,7,10,13}, 1)) - lookup(a2, date(year(a2), {1,4,7,10}, 1))