Is this possible with a formula? Or multiple formulas?

[easybpw]

Hello,

Thanks in advance for your help. I am wondering if this is possible to do.

Currently I have a master worksheet with many student names and information about the student. What I need to do is calculate the amount of commission we receive based off the tuition amount and other factors. The other factors would be when the student started and if they are a new or returnee student. For example student John Doe starts F18 (Fall 2018) we know based on today's date he's a new student therefore commission for this student could either be a percentage of the tuition or a flat amount based on when he started. Ultimately I'd like a worksheet within the workbook that pertains only to commission rates for each school (currently sits around 20-30 schools) and the mastersheet using formulas to calculate the commission based on information from the commission worksheet. I'm willing to add columns to the commissions worksheet but have little flexibility in adding columns to the mastersheet.

So for example:

Column A Column B Column C Column D (School Name) Column E (Tuition) Column F (commission)
John Doe NEW F18 EXCEL High School 50,000 5,000 (flat fee)
Jane Doe RET F17 ECEL High School 50,000 2,500 (flat fee)
Judy Doe NEW F18 WORD High School 50,000 500 (1% of tuition)


Thanks!

Bill

 

[Delfinus25]

It seems like it would be possible to complete with an embedded IF formula. HOWEVER - assuming your example is your Master Sheet, we would need to know the layout / details of the Commissions worksheets to wrote it properly.