A
AJ Master
I am trying to calculate the commission on a 120 month contract with
commission schedule that can vary. One column has all the date
(month/year "mmm yyyy") the column next to it has the monthly contrac
charge. The full commission for the 120 month period is paid in
month determined by contract so I have conditions to meet as well. Th
commission is being calculated wrong. In my current project th
commission schedule is 4% for the 1st 5 years and then 2% for th
remaining 5 years. For some reason my array formula is calculatin
everything at 4%. Here's what I used:
={-SUM(IF(OR($L5<>CommencementDate,ISTEXT($P$5:$P$124)=TRUE),0,VLOOKUP(YEAR(contract_dates),CommissionSchedule,2,FALSE)*$P$5:$P$124))}
The commission schedule looks like this:
2004 4%
2005 4%
2006 4%
2007 4%
2008 4%
2009 2%
2010 2%
2011 2%
2012 2%
2013 2%
Anyone have any ideas
commission schedule that can vary. One column has all the date
(month/year "mmm yyyy") the column next to it has the monthly contrac
charge. The full commission for the 120 month period is paid in
month determined by contract so I have conditions to meet as well. Th
commission is being calculated wrong. In my current project th
commission schedule is 4% for the 1st 5 years and then 2% for th
remaining 5 years. For some reason my array formula is calculatin
everything at 4%. Here's what I used:
={-SUM(IF(OR($L5<>CommencementDate,ISTEXT($P$5:$P$124)=TRUE),0,VLOOKUP(YEAR(contract_dates),CommissionSchedule,2,FALSE)*$P$5:$P$124))}
The commission schedule looks like this:
2004 4%
2005 4%
2006 4%
2007 4%
2008 4%
2009 2%
2010 2%
2011 2%
2012 2%
2013 2%
Anyone have any ideas