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,

As most of the formula is the same, what I would suggest is to create a table, with two columns, PLAN and %AGE then use

=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)*vlookup(E12,table_range,2,0),0)

where E12 is the PLAN ID.

 

[wayland3r]

Some of the plans are alot different. But I see how that would work if only the last % is changed. But alot of them are different all the way through the sheet. I have 13 plans in total.


Thank for taking the time to help me!

A thankful Canuck,

 

[wayland3r]

Hello,

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

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

Yes this is what i mean Where there is 7 different plans that have many different levels.