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?

Row\Col	A​	B​	C​
1​	Date​	Days In​	​
2​	15-Jan​	15​	B2: =A2 - LOOKUP(A2, DATE(YEAR(A2), {1,4,7,10},1)) + 1
3​	30-Jan​	30​
4​	15-Feb​	46​
5​	31-Mar​	91​
6​	1-Apr​	1​
7​	30-Apr​	30​

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]

Row\Col	A​	B​	C​
1​	Date​	Days In​	​
2​	15-Jan​	15​	B2: =A2 - LOOKUP(A2, DATE(YEAR(A2), {1,4,7,10},1)) + 1
3​	30-Jan​	30​
4​	15-Feb​	46​
5​	31-Mar​	91​
6​	1-Apr​	1​
7​	30-Apr​	30​

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))