Need Help creating a split program formula.

[wayland3r]

Here is a challange for all the Excel Guru's out there. I work in the Real Estate Industry and need to do a revenue sheet for each agent that works on different split plans. An example of one of them is as follows:

On the first $30,000 the agent is given 50% of the revenue ($15,000)
On the next $35,000 the agent is given 60% of the revenue ($21,000)
On the next $20,000 the agent is given 70% of the revenue ($14,000)
On the next $15,000 the agent is given 75% of the revenue (($11,250)
On everything else after they have met the above targets they get %90.

So if an agent was on this split plan and did a gross sales of $200,000 they would take home $151,250.00

Is it possible for me to have one Cell that I just input the amount that the agent Made or is projected to make and have the formula give me his total share of the earnings?

Thanks in Advance for any help you can give me on this matter.

A Excel Wannabeeee from Canuck Land.

 

[onlyadrafter]

Hello and welcome to the board,

Long winded but how about

=IF(F12<30000,F12*50%,15000)+IF(F12>65000,21000,IF(F12<85000,0,(F12-65000)*60%))+IF(F12>85000,14000,IF(F12<85000,0,(F12-85000)*70%))+IF(F12>100000,11250,IF(F12<100000,0,(F12-100000)*75%))+IF(F12>100000,(F12-100000)*90%,0)

where F12 is the cell in question.

You may also want to look at

http://www.mcgimpsey.com/excel/variablerate.html

a similar question was posted today.

 

[tusharm]

See Progressive Pricing Formulas For Excel
http://www.cpearson.com/excel/pricing.htm

 

[wayland3r]

Thanks for the welcome to the board and the fast response to my question. The formula seems to work well for any Agent that makes over 100,000. But if an agent makes less it seems to be off.

Any idea's?

A thankful Canuck

 

[onlyadrafter]

Hello,



Try,

=IF(F12<30000,F12*50%,15000)+IF(F12>65000,21000,IF(F12>30000,(F12-30000)*60%,0))+IF(F12>85000,14000,IF(F12>65000,(F12-65000)*70%,0))+IF(F12>100000,11250,IF(F12>85000,(F12-85000)*75%,0))+IF(F12>100000,(F12-100000)*90%,0)

 

[wayland3r]

Thanks million that worked awesome. But this sends me to ask another question. Is it possible to link a cell that asks what type of split they are on to a Cell that shows their gross?

1 2 3 4
a Agent Plan id Sales Agents-Portion
b Bill Smith 002 100,000 61,250
c Fred Lee 001 100,000 90,000


Where what ever is selected in the Plan iD determines which of the 2 formulas it uses?

Confused Canuck......

 

[onlyadrafter]

Hello,

Do you mean something like (for Bill Smith) in B4

= if(B2="002",one formula,other formula)

 

[wayland3r]

What I mean is that if you select Plan id 001 for bill smith is would use Formula 1. And if you changed his plan to id 002 it would change his split to Forumla 2.

I hope that makes a bit more sense..I am confused now.

A lost Canuck.

 

[onlyadrafter]

Hello,

What are the two formulas?

 

[wayland3r]

Formula 1 is:

=IF(F12<30000,F12*50%,15000)+IF(F12>65000,21000,IF(F12>30000,(F12-30000)*60%,0))+IF(F12>85000,14000,IF(F12>65000,(F12-65000)*70%,0))+IF(F12>100000,11250,IF(F12>85000,(F12-85000)*75%,0))+IF(F12>100000,(F12-100000)*90%,0)

Formula 2 is:

=IF(F12<30000,F12*50%,15000)+IF(F12>65000,21000,IF(F12>30000,(F12-30000)*60%,0))+IF(F12>85000,14000,IF(F12>65000,(F12-65000)*70%,0))+IF(F12>100000,11250,IF(F12>85000,(F12-85000)*75%,0))+IF(F12>100000,(F12-100000)*95%,0)

Formula 3 is:

=IF(F12<30000,F12*50%,15000)+IF(F12>65000,21000,IF(F12>30000,(F12-30000)*60%,0))+IF(F12>85000,14000,IF(F12>65000,(F12-65000)*70%,0))+IF(F12>100000,11250,IF(F12>85000,(F12-85000)*75%,0))+IF(F12>100000,(F12-100000)*80%,0)


There could be 7 different formula's in total.

Thanks,