Tricky Commission Formula

Challenge for March/April 2007: Tricky Sales Commission Formula

This challenge was the most heavily entered and the most hotly contested. I will make some observations:

• Personally, I think that Brad's idea to replace two functions MIN(MAX with a single function, MEDIAN was excellent. Daniel Ferry may have been bluffing when he wrote, "It is easier to find the extreme than the middle so Min(MAX works faster". I actually pulled out some cool timer code this morning and ran this test.
• The MEDIAN approach runs 26% faster than the MIN(MAX approach. So, I am awarding Brad with an a signed copy of Learn Excel from MrExcel and an Excel Master pin for his entry. (By the way, download Brad's workbook from the link at the bottom of the page, he provides an excellent derivation of the formula.
• The IF approach finished second in the voting. Many of you complained that the nominated formula did not work in the bizarre case when the first month's sales exceeded 2000. Matt sent in a version in matt2entry.zip that handles this situation. Many others wrote in to challenge Daniel Ferry's rejection of all IF solutions to the problem. My take: the IF approach is definitely the easiest to understand. If some random Exceller were to pick up this spreadsheet a year from now, there is a better chance that IF will be understood. However, in the end, the speed and brevity of the other approaches won out.
• Laurie's array formula is advanced. I love the e-mail from people who said, "wow - it took me an hour to figure it out, but it really works!". For more about array formulas, search for any of Aladin's "as an array" postings at the MrExcel Message board. By the way, Daniel Ferry rewrote Laurie's entry so that it used the input cells. He says it might be easier to follow the formula. If you are interested, download lauriemodified.zip.
• The plan was to give away one power bundle to someone randomly chosen and Peter Stikker won the random drawing. A few days later, we realized that about 80 people were excluded from this drawing. To make it fair, another random drawing was held on June 1 from all correct entries. David Kohne of Phoenix AZ won that drawing.
• A HUGE THANK YOU to everyone who entered. As Daniel Ferry pointed out, it was an astonishingly hard problem, when compared to how simple it looks. Someone suggested that I should give all of the correct entries a free book. While that is a cool idea, the publisher and the mail room both voted no.

The complete text of the challenge and the various comments are below.

We had overwhelming response to this challenge! There were over 548 entries to the challenge. Of these, 407 sent in a formula that gave the correct result. Amazingly, there were 380 different formulas to produce those correct results! The most popular formula was sent in only four times. Before we analyze the answers, let's review the challenge:

Here is a challenge that many people reading this page can solve. In the figure below, row 8 contains monthly sales figures for a widget. The commission plan for this year is to pay $1.50 per unit for the first 2000 units sold this year. After 2000 units, the commission drops to $1.25. Create formulas to calculate the monthly commission in row 9. Feel free to use additional rows for intermediate calculations.

For 11 months of the year, the result will be either $1.50 or $1.25 times the units. However, in one month (it happens to be May in this example), some units will be paid at $1.50 and some units will be paid at $1.25. Your formulas should be able to handle any series of sales in row 8; the formula should be flexible enough to handle someone who reaches the 2000 level in any month. Make sure that your formula works by comparing it to these results:

There are many approaches to solving this formula. Feel free to use extra rows for intermediate calculations. The first prize - a Power Bundle from the MrExcel Online store will go to the best formula. However, everyone who sends in a working solution will be entered in a =RAND() drawing for a second Power Bundle. In the event that two people come up with the "best" solution, the earlier postmark will win.

The answer should use formulas and function in Excel or the Analysis Toolpak. Please - no custom VBA functions. Entries are due by April 15, 2007. Please e-mail your entry to bill-at-mrexcel.com. (We are trying to cut down on spam - you will have to replace the word "-at-" with the at symbol).

Download and unzip marchallenge.zip. Return your completed worksheet to bill at mrexcel.com.

Results

During Podcast #500 on May 3, 2007, I held a random drawing to choose from among the correct answers. Peter Stikker was the winner of an Excel Power Bundle. He chose the soon-to-be-released Excel 2007 Power Bundle. By the way, his formula for cell M10 was:

It is amazing to see the variety of formulas used. Here are the 423 formulas that were used in cell M10:

=$F$5 * M$9 + ($G$5-$F$5) * MEDIAN(0,M$9,SUM($B$9:M$9)-$E$5)
=$F$5*(M$9 - MIN(MAX(0,SUM($B$9:M$9)-$E$5),M$9)) + $G$5*(MIN(MAX(0,SUM($B$9:M$9)-$E$5),M$9))
=$F$5*(M9-IF(SUM($B$9:M9)<$E$5,0,MIN(M9,SUM($B$9:M9)-$E$5)))+$G$5*IF(SUM($B$9:M9)<$E$5,0,MIN(M9,SUM($B$9:M9)-$E$5))
=$F$5*(M9-M13)+$G$5*M13
=$F$5*(SUM($B9:M9))-MAX(0,(SUM($B9:M9)-$E$5))*($F$5-$G$5)-SUM($A10:L10)
=$F$5*IF(SUM($B$9:M9)<$E$5,M9,IF((SUM($B$9:M9)-M9)<$E$5,$E$5+M9-SUM($B$9:M9),0))+$G$5*IF(SUM($B$9:M9)<$E$5,0,IF(SUM($B$9:M9)-M9>2000,M9,SUM($B$9:M9)-2000))
=$F$5*IF(SUM($B$9:M9)<$E$5,M9,IF((SUM($B$9:M9)-M9)<$E$5,$E$5+M9-SUM($B$9:M9),0))+$G$5*IF(SUM($B$9:M9)<$E$5,0,IF(SUM($B$9:M9)-M9>2000,M9,SUM($B$9:M9)-2000))
=$F$5*M9-($F$5-$G$5)*MIN(M9,MAX(0,SUM($B9:M9)-$E$5))
=$F$5*M9-($F$5-$G$5)*MIN(MAX(SUM($B$9:M9)-$E$5,0),M9)
=$F$5*MAX(M9-MAX(SUM($B9:M9)-$E$5,0),0)+$G$5*MAX(MIN(SUM($B9:M9)-$E$5,M9),0)
=$F$5*MIN(M$9-MIN(SUM($B$9:M$9)-MIN(SUM($B$9:M$9),$E$5),M$9),M$9)+$G$5*MIN(SUM($B$9:M$9)-MIN(SUM($B$9:M$9),$E$5),M$9)
=$F5*M9*(SUM($B9:M9)<$E5)+$G5*M9*(SUM($B9:L9)>$E5)+((SUM($B9:M9)>$E5)+(SUM($B9:L9)<$E5)-1)*($F5*($E5-SUM($B9:L9))+$G5*(M9-($E5-SUM($B9:L9))))
=$F5*MIN(M9,MAX(0,$E5-SUM($A9:L9)))+$G5*MIN(M9,MAX(0,SUM($A9:M9)-$E5))
=$G$5*M9+($F$5-$G$5)*IF(SUM($B$9:M9)<$E$5,M9,IF(SUM($B$9:M9)-M9<$E$5,M9-SUM($B$9:M9)+$E$5,0))
=$G5*SUM($B9:M9)+MIN($E5,SUM($B9:M9))*($F5-$G5)-SUM($A10:L10)
=$G5*SUM($B9:M9)+MIN($E5,SUM($B9:M9))*($F5-$G5)-SUM($A10:L10)
=$G5*SUM($B9:M9)+MIN($E5,SUM($B9:M9))*($F5-$G5)-SUM($A10:L10)
=($F$5*M11)+($G$5*M12)
=(IF($E$5-SUM($B$9:M9)>0,M9,IF(M9+($E$5-SUM($B$9:M9))>0,M9+($E$5-SUM($B$9:M9)),0))*$F$5)+(IF(AND($E$5-SUM($B$9:M9)<0,(SUM($B$9:M9)-$E$5)=(IF((((SUM(Sales Jan:Dec))-Sales Dec)>E5),(Sales Dec*G5),(IF(SUM(Sales Jan:Dec)>E5,((((SUM(Sales Jan:Dec))-E5)*G5)+((E5-((SUM(Sales Jan:Dec))-Sales Dec))*F5)),(Sales Dec*F5)))))
=(IF((SUM($B$9:M9)-$E$5)<0,M9,IF(M9-(SUM($B$9:M9)-$E$5)>0,M9-(SUM($B$9:M9)-$E$5),0))*$F$5)+(M9-(IF((SUM($B$9:M9)-$E$5)<0,M9,IF(M9-(SUM($B$9:M9)-$E$5)>0,M9-(SUM($B$9:M9)-$E$5),0))))*$G$5
=(M12*$F$5)+(M14*$G$5)
=(M12*$G$5)+(M13*$F$5)
=(M12*$G$5)+(M13*$F$5)
=(M13*$F$5)+(M14*$G$5)
=(M13*$F5)+(M14*$G5)
=(M14*$F$5)+(M15*$G$5)
=(M22*Lower)+(M23*Upper)
=(M9*$G$5)+MAX(0,MIN($E$5-SUM($A9:L9),M9))*($F$5-$G$5)
=(M9*$G5)+(IF(SUM($B9:M9)>$E5,IF(M9-(SUM($B9:M9)-$E5)>0,M9-(SUM($B9:M9)-$E5),0),M9)*($F5-$G5))
=(M9*1.5)-(IF(SUM($B$9:M9)>2000,(IF(((2000+M9)>SUM($B$9:M9)),(SUM($B$9:M9)-2000)*0.25,M9*0.25))))
=(M9-M14)*$F$5+M14*$G$5
=(MIN(SUM($B$9:M9)-$E$5,0)+$E$5)*$F$5+MAX(SUM($B$9:M9)-$E$5,0)*$G$5-SUM($B$10:L10)
=(MIN(SUM($B$9:M9),$E$5)*$F$5+MAX(0,(SUM($B$9:M9)-$E$5))*$G$5)-(MIN((SUM($B$9:M9)-M9),$E$5)*$F$5+MAX(0,((SUM($B$9:M9)-M9)-$E$5))*$G$5)
=(MIN(SUM($B9:M9),$E$5)*$F$5)+(MAX(SUM($B9:M9)-$E$5,0)*$G$5)-SUM($B10:L10)
=(SUM($B$9:M$9)-2000)*IF((SUM($B$9:M$9))>=2000,TRUE)*IF(SUM($B$9:L9)<2000,TRUE)*1.25+((2000-SUM($B$9:L9))*1.5*IF(SUM($B$9:M9)>=2000,TRUE))*IF(SUM($B$9:L9)<2000,TRUE)+M$9*1.5*IF(SUM($B$9:M9)<2000,TRUE)+M9*1.25*IF(SUM($B$9:L9)>2000,TRUE)
=(SUM($B$9:M$9)-2000)*IF((SUM($B$9:M$9))>=2000,TRUE)*IF(SUM($B$9:L9)<2000,TRUE)*1.25+((2000-SUM($B$9:L9))*1.5*IF(SUM($B$9:M9)>=2000,TRUE))*IF(SUM($B$9:L9)<2000,TRUE)+M$9*1.5*IF(SUM($B$9:M9)<2000,TRUE)+M9*1.25*IF(SUM($B$9:L9)>2000,TRUE)
=(SUM($B9:M9)>$E$5)*MIN(M9,SUM($B9:M9)-$E$5)*$G$5+(M9-(SUM($B9:M9)>$E$5)*MIN(M9,SUM($B9:M9)-$E$5))*$F$5
=(SUM((SUM($B$9:Dec Sales)<=$Limit)*Sales*$Below))+(SUM($B$9:Dec Sales)>$Limit)*((SUM($B$9:Dec Sales)-Sales)<=$Limit)*(((SUM($B$9:Dec Sales)-$Limit)*$Above)+(((Sales-(SUM($B$9:Dec Sales)-$Limit)))*$Below))+(((SUM($B$9:Dec Sales)-Sales)>$Limit)*Sales*$Above)
=+(M13*1.5)+(M14*1.25)
=+IF(($E$5-($E$5-SUM($B$9:M9)))<=$E$5,($E$5-($E$5-M9))*$F$5,(($E$5*$F$5)+(($E$5-($E$5-SUM($B$9:M9)))-$E$5)*$G$5)-SUM($B$10:L10))
=+IF(AND(SUM($B9:M9)-M9<=$E$5,SUM($B9:M9)<=$E$5),$F$5*M9,IF(AND(SUM($B9:M9)-M9<=$E$5,SUM($B9:M9)>$E$5),$G$5*(SUM($B9:M9)-2000)+$F$5*(2000-SUM($B9:M9)+M9),M9*$G$5))
=+IF(AND(SUM($B9:M9)-M9<=$E$5,SUM($B9:M9)<=$E$5),$F$5*M9,IF(AND(SUM($B9:M9)-M9<=$E$5,SUM($B9:M9)>$E$5),$G$5*(SUM($B9:M9)-2000)+$F$5*(2000-SUM($B9:M9)+M9),M9*$G$5))
=+IF(M10<=$E$5,M9*$F$5,IF(M12=0,M9*$G$5,((M9-M11)*$F$5)+(M11*$G$5)))
=+IF(M9<0,"Data Mistake . Verify",IF($B$9>=E5,M9*G5,IF(SUM($B$9:M9)<=E5,M9*F5,IF(TEXT(AND((SUM($B$9:L9))E5),"")="Verdadero",(E5-(SUM($B$9:M9)-M9))*F5+(SUM($B$9:M9)-E5)*G5,M9*G5))))
=+IF(SUM($B$9:M9)>$E$5,M9*$G$5 + ($F$5-$G$5)*MAX(0,$E$5-SUM($B9:L9)),M9*$F$5)
=+M11-L11
=+M23
=+Widgets*Commission1+DifferenceInCommissions*MIN(+Widgets,MAX(0,+WidgetsCum-Threshold))
=980*1.25
=C_HI*(OFFSET($A$1,ROW()-2,COLUMN()-1)-IF(SUM(Start:OFFSET($A$1,ROW()-2,COLUMN()-1))=Commission
=ComputeCommission
=F5*B25+G5*C25
=F5*M12+G5*(M9-M12)
='formulas used'!J12
=IF( SUM(B9:M9)<=2000, M9*1.5, ( IF( (M9 - (SUM(B9:M9) - 2000))>=0, (M9-( SUM(B9:M9) - 2000)) * 1.5 + ( SUM(B9:M9) - 2000) * 1.25, M9 * 1.25 ) ) )
=IF( SUM(B9:M9)<=E5, M9*F5, ( IF( (M9 - (SUM(B9:M9) - E5))>=0, (M9-( SUM(B9:M9) - E)) * F5 + ( SUM(B9:M9) - E5) * G5, M9 * G5 ) ) )
=IF($E$5 $E$5, SUM($B$9:L$9) < $E$5), ($E$5 - SUM($B$9:L$9) )*$F$5 + $G$5 * (SUM($B$9:M$9) - $E$5), $G$5*M$9),M$9*$F$5)
=IF($E$5>SUM($B$9:M$9),(M$9*$F$5),IF(AND($E$5=IF($E$5-SUMPRODUCT($B9:M9)>0,M9*$F$5,IF(ABS($E$5-SUMPRODUCT($B9:M9))>M9,M9*$G$5,(-($E$5-SUMPRODUCT($B9:M9))*N5) +( (M9+$E$5-SUMPRODUCT($B9:M9))*$F$5)))
=IF($E5-SUM($B9:M9)>0,M9*$F5,IF(AND(SUM($B9:L9)>$E5,SUM($B9:M9)>$E5)=TRUE,M9*$G5,((($E5-SUM($B9:M9))*-1)*$G5)+(M9+($E5-SUM($B9:M9)))*$F5))
=IF($Q$8=M$7, $F$5*($E$5-$P$10) + $G$5*($P$9-$E$5), IF($Q$8>M$7,$F$5,$G$5)*M$9)
=IF(((SUM($B9:L9))-$E$5)>0,(M9*$G$5),(IF(((SUM($B9:M9))-$E$5)>0,(((M9-((SUM($B9:M9))-$E$5))*$F$5)+(((SUM($B9:M9))-$E$5)*$G$5)),(M9*$F$5))))
=IF((M12-M9)>$E$5,M9*$G$5,IF(M12<$E$5,M9*$F$5,((M12-$E$5)*$G$5)+((M9-(M12-$E$5))*$F$5)))
=IF((M9-M11)<=0,M9*$G$5,IF(M11<=0,M9*$F$5,(M9-M11)*$F$5+(M11*$G$5)))
=IF((M9-M11)<=0,M9*$G$5,IF(M11<=0,M9*$F$5,(M9-M11)*$F$5+(M11*$G$5)))
=IF((SUM($A$9:L9)>$E$5),M9*$G$5,IF(SUM($B$9:M9)<=$E$5,M9*$F$5,($E$5-SUM($B$9:L9))*$F$5+(SUM($B$9:M9)-$E$5)*$G$5))
=IF((SUM($A$9:M9)<=$E$5),(M9*$F$5), IF((SUM($A$9:L9)>=$E$5),(M9*$G$5),(($E$5-SUM($A$9:L9))*$F$5)+((SUM($A$9:M9) -$E$5)*$G$5)))
=IF((SUM($B$9:$M$9)-SUM(N9:Y9))<$E$5,(SUM($B$9:$M$9)-SUM(N9:Y9))*$F$5,$E$5*$F$5+(((SUM($B$9:$M$9)-SUM(N9:Y9))-$E$5)*$G$5))-(IF((SUM($B$9:$M$9)-SUM(M9:X9))<$E$5,(SUM($B$9:$M$9)-SUM(M9:X9))*$F$5,$E$5*$F$5+(((SUM($B$9:$M$9)-SUM(M9:X9))-$E$5)*$G$5)))
=IF((SUM($B$9:$M$9)-SUM(N9:Y9))<$E$5,(SUM($B$9:$M$9)-SUM(N9:Y9))*$F$5,$E$5*$F$5+(((SUM($B$9:$M$9)-SUM(N9:Y9))-$E$5)*$G$5))-(IF((SUM($B$9:$M$9)-SUM(M9:X9))<$E$5,(SUM($B$9:$M$9)-SUM(M9:X9))*$F$5,$E$5*$F$5+(((SUM($B$9:$M$9)-SUM(M9:X9))-$E$5)*$G$5)))
=IF((SUM($B$9:M$9)-M$9)>$E$5, M$9*$G$5, IF(SUM($B$9:M$9)<$E$5, M$9*$F$5, (SUM($B$9:M$9)-$E$5)*$G$5 + (M$9-(SUM($B$9:M$9)-$E$5))*$F$5))
=IF((SUM($B$9:M9)-$E$5-M9)>0, M9*1.25, IF(SUM($B$9:M9)>$E$5, (SUM($B$9:M9)-$E$5)*1.25+(M9-(SUM($B$9:M9)-$E$5))*1.5, M9*1.5))
=IF((SUM($B$9:M9))<$E$5,M9*$F$5,IF((SUM($B$9:M9))>=($E$5+M9),M9*$G$5,((($E$5-(SUM($B$9:M9))+M9)*$F$5)+((M9-($E$5-(SUM($B$9:M9))+M9))*$G$5))))
=IF((SUM($B$9:M9)<=$E$5),M9*$F$5,(((SUM($B$9:M9)-$E$5)*$G$5)+($E$5*$F$5))-SUM($A$10:L10))
=IF((SUM($B$9:OFFSET($A$8,1,MONTH(M8)))-M9)>$E$5,M9*$G$5,IF(SUM($B$9:OFFSET($A$8,1,MONTH(M8)))<$E$5,M9*$F$5,((SUM($B$9:OFFSET($A$8,1,MONTH(M8)))-$E$5)*$G$5)+((M9-(SUM($B$9:OFFSET($A$8,1,MONTH(M8)))-$E$5))*$F$5)))
=IF((SUM($B9:L9)-$E$5)=IF((SUM($B9:M9)-M9)>$E$5,M9*$G$5,IF(SUM($B9:M9)<=$E$5,M9*$F$5,SUM(($E$5-SUM($B9:L9))*$F$5,(M9-($E$5-SUM($B9:L9)))*$G$5)))
=IF((SUM(B9:M9)-M9)>$E$5,M9*$G$5,IF(SUM(B9:M9)<$E$5,M9*$F$5,((SUM(B9:M9)-$E$5)*$G$5)+((M9-(SUM(B9:M9)-$E$5))*$F$5)))
=IF((SUM(M9:OFFSET(M9,0,-M7)))<$E$5,M9*$F$5,IF((SUM(M9:OFFSET(M9,0,-M7)))-M9>=$E$5,M9*$G$5,($E$5-SUM(L9:OFFSET(L9,0,-L7)))*$F$5+(SUM(M9:OFFSET(M9,0,-M7))-$E$5)*$G$5))
=IF(2000-SUM($B9:M9)>0,M9*1.5,IF(AND(SUM($B9:L9)>2000,SUM($B9:M9)>2000)=TRUE,M9*1.25,(((2000-SUM($B9:M9))*-1)*1.25)+(M9+(2000-SUM($B9:M9)))*1.5))
=IF(AND($E$5<=M$12,(M$12-M$9)<=$E$5),(ABS(M$12-$E$5-M$9)*$F$5)+((M$9-ABS(M$12-$E$5-M$9))*$G$5),IF(M$12<=$E$5,M$9*$F$5,M$9*$G$5))
=IF(AND($E$5<=M$12,(M$12-M$9)<=$E$5),(ABS(M$12-$E$5-M$9)*$F$5)+((M$9-ABS(M$12-$E$5-M$9))*$G$5),IF(M$12<=$E$5,M$9*$F$5,M$9*$G$5))
=IF(AND((SUM($B$9:L9)<$E$5),($E$5=IF(AND(L$8="",SUM($B$9:M$9)>$E$5),($E$5*$F$5)+(($B$9-$E$5)*$G$5),IF(SUM($B$9:M$9)<=$E$5,M$9*$F$5,IF(AND(SUM($B$9:M$9)>$E$5,SUM($B$9:L$9)>$E$5),(M$9*$G$5),(($E$5-SUM($B$9:L$9))*$F$5)+((SUM($B$9:M$9)-$E$5)*$G$5))))
=IF(AND(L12<$E$5,M12<$E$5),M9*$F$5,IF(AND(L12<$E$5,M12>$E$5),SUM(($E$5-L12)*$F$5,(M12-$E$5)*$G$5),M9*$G$5))
=IF(AND(L12>0,M12<0),($E5-L11)*$F5+(M11-L11-L12)*$G5,IF(AND(L12<0,M12<0),M9*$G5,IF(AND(L12>0,M12>0),M9*$F5,227*$F5)))
=IF(AND(L12>0,M12<0),(2000-L11)*1.5+(M11-L11-L12)*1.25,IF(AND(L12<0,M12<0),M9*1.25,IF(AND(L12>0,M12>0),M9*1.5,227*1.5)))
=IF(AND(LEFT(RIGHT("0"&SUM($B$9:M9),4),1)=LEFT($E$5,1),NOT(LEFT(RIGHT("0"&SUM($A$9:L9),4),1)=LEFT($E$5,1))),MOD(SUM($B$9:M9),$E$5)*$G$5+(M9-MOD(SUM($B$9:M9),$E$5))*$F$5,M9*IF(SUM($B$9:M9)<$E$5,$F$5,$G$5))
=IF(AND(M$14>0,M$15>0),((M$15*$G$5)+(M$14*$F$5)),IF(AND(M$14>0,M$15<0),M$9*$F$5,IF(AND(M$14<0,M$15>0),M$9*$G$5,"oops")))
=IF(AND(M10>$E$5,L10<$E$5),($E$5-L10)*$F$5+(M10-$E$5)*$G$5,IF(M10<$E$5,M9*$F$5,M9*$G$5))
=IF(AND(M10>$E$5,L10<$E$5),((M10-$E$5)*$G$5)+($E$5-L10)*$F$5,(M10-L10)*IF(M10>$E$5,$G$5,$F$5))
=IF(AND(M10>$E$5,L10<$E$5),(M10-$E$5)*$G$5+(M9-(M10-$E$5))*$F$5,IF(M10<$E$5,M9*$F$5,M9*$G$5))
=IF(AND(M10>$E$5,L10<$E$5),(M10-$E$5)*$G$5+(M9-M10+$E$5)*$F$5,IF(M10<$E$5,M9*$F$5,M9*$G$5))
=IF(AND(M13=TRUE,M14=FALSE),M9*$F$5,IF(AND(M13=FALSE,M14=TRUE),M11*$G$5+M12*$F$5,IF(AND(M13=TRUE,M14=TRUE),M9*$G$5,0)))
=IF(AND(M15=0,M12<$E$5),M9*$F$5,IF(AND(M15=0,M12>$E$5),M9*$G$5,((M12-$E$5)*$G$5)+(M9-(M12-$E$5))*$F$5))
=IF(AND(M21>0,M22>0),M25,IF(SUM($B$9:M9)<$E$5,M9*$F$5,M9*$G$5))
=IF(AND(ROUNDDOWN(SUM($B$9:M9),"-"&LEN(INT(SUM($B$9:M9)))-1)=$E$5,NOT(ROUNDDOWN(SUM($A$9:L9),"-"&LEN(INT(SUM($A$9:L9)))-1)=$E$5)),MOD(SUM($B$9:M9),$E$5)*$G$5+(M9-MOD(SUM($B$9:M9),$E$5))*$F$5,M9*IF(SUM($B$9:M9)<$E$5,$F$5,$G$5))
=IF(AND(ROUNDDOWN(SUM($B$9:M9),"-"&LEN(INT(SUM($B$9:M9)))-1)>=$E$5,NOT(ROUNDDOWN(SUM($A$9:L9),"-"&LEN(INT(SUM($A$9:L9)))-1)>=$E$5)),(SUM($B$9:M9)-$E$5)*($G$5-$F$5)+M9*$F$5,M9*IF(SUM($B$9:M9)<$E$5,$F$5,$G$5))
=IF(AND(SUM($A$9:L9)<=$E$5,SUM($A$9:M9)>$E$5),($E$5-SUM($A$9:L9))*$F$5+$G$5*(SUM($A$9:M9)-$E$5),IF(SUM($A$9:M9)<=$E$5,M9*$F$5,M9*$G$5))
=IF(AND(SUM($A9:L9)<$E5,SUM($B9:M9)>$E5),($E5-SUM($A9:L9))*$F5+(SUM($A9:M9)-$E5)*$G5,IF(SUM($B9:M9)<$E5,M9*$F5,M9*$G5))
=IF(AND(SUM($A9:L9)<2000,SUM($A9:M9)>2000),((SUM($A9:M9)-2000)*1.25)+(2000-SUM($A9:L9))*1.5,IF(SUM($A9:M9)>2000,M9*1.25,M9*1.5))
=IF(AND(SUM($A9:M9)>$E5+1,SUM($A9:L9)<$E5),((SUM($A9:M9)-$E5)*$G5)+(M9-(SUM($A9:M9)-$E5))*$F5,IF(SUM($A9:M9)>$E5,M9*$G5,M9*$F5))
=IF(AND(SUM($B$10:M10)>=$E$5,SUM($A$10:L10)>=$E$5),M10*$G$5,IF(AND(SUM($B$10:M10)<=$E$5,SUM($A$10:L10)<=$E$5),M10*$F$5,(($E$5-SUM($B$10:L10))*$F$5)+((SUM($B$10:M10))-$E$5)*$G$5))
=IF(AND(SUM($B$10:M10)>=$E$5,SUM($A$10:L10)>=$E$5),M10*$G$5,IF(AND(SUM($B$10:M10)<=$E$5,SUM($A$10:L10)<=$E$5),M10*$F$5,(($E$5-SUM($B$10:L10))*$F$5)+((SUM($B$10:M10))-$E$5)*$G$5))
=IF(AND(SUM($B$2:M2)>$E$5,SUM($B$9:L9)<$E$5),((SUM($B2:M$2)-$E$5)*$G$5)+(M9-(SUM($B2:M$2)-$E$5))*$F$5,IF(SUM(H2:M2)<$E$5,M9*$F$5,M9*$G$5))
=IF(AND(SUM($B$9:L9)<=$E$5,SUM($B$9:M9)>$E$5),MAX($E$5-SUM($B$9:L9),0)*$F$5+MAX(SUM($B$9:M9)-$E$5,0)*$G$5,IF(SUM($B$9:M9)<$E$5,M9*$F$5,M9*$G$5))
=IF(AND(SUM($B$9:M$9)>$E$5,SUM($A$9:L$9)<$E$5),(($E$5-SUM($A$9:L$9))*$F$5)+(SUM($B$9:M$9)-$E$5)*$G$5,(IF(SUM($B$9:M$9)<=$E$5,M$9*$F$5,M$9*$G$5)))
=IF(AND(SUM($B$9:M$9)>$E$5,SUM($B$9:L$9)<$E$5),($E$5-SUM($B$9:L$9))*$F$5+(SUM($B$9:M$9)-$E$5)*$G$5,IF(SUM($B$9:L$9)>$E$5,M$9*$G$5,M$9*$F$5))
=IF(AND(SUM($B$9:M$9)>$E$5,SUM($B$9:L$9)<=$E$5),(($E$5-SUM($B$9:L$9))*$F$5+(SUM($B$9:M$9)-$E$5)*$G$5),IF(SUM($B$9:M$9)<=$E$5,M$9*$F$5,M$9*$G$5))
=IF(AND(SUM($B$9:M$9)>$E5,SUM($B$9:M$9)-M9<$E5),(($E5-(SUM($B$9:M$9)-M9))*$F5)+((SUM($B$9:M$9)-$E5)*$G5),IF((SUM($B$9:M$9)>$E5),(M9*$G5),(M9*$F5)))
=IF(AND(SUM($B$9:M9)<$E$5+M9,SUM($B$9:M9)>$E$5),((M9-(SUM($B$9:M9)-$E$5))*$F$5)+((SUM($B$9:M9)-$E$5)*$G$5),IF(SUM($B$9:M9)>$E$5,M9*$G$5,M9*$F$5))
=IF(AND(SUM($B$9:M9)>$E$5,SUM($B$9:M9)-$E$5$E$5,$G$5*M9,$F$5*M9))
=IF(AND(SUM($B$9:M9)>$E$5,SUM($B$9:M9)-$E$5$E$5,$G$5*M9,$F$5*M9))
=IF(AND(SUM($B$9:M9)>$E$5,SUM($B$9:M9)-M9<$E$5),(SUM($B$9:M9)-$E$5)*$G$5+(M9-(SUM($B$9:M9)-$E$5))*$F$5,IF(SUM($B$9:M9)<$E$5,M9*$F$5,IF(SUM($B$9:M9)>$E$5,M9*$G$5,"")))
=IF(AND(SUM($B$9:M9)>$E$5,SUM($B$9:M9)-M9<$E$5),(SUM($B$9:M9)-$E$5)*$G$5+(M9-(SUM($B$9:M9)-$E$5))*$F$5,IF(SUM($B$9:M9)<$E$5,M9*$F$5,M9*$G$5))
=IF(AND(SUM($B9:L$9)>=$E$5,SUM($B9:M$9)>=$E$5),M$9*$G$5,IF(SUM($B9:M$9)>=$E$5,(SUM($B9:M$9)-$E$5)*$G$5+($E$5-SUM($B9:L$9))*$F$5,M$9*$F$5))
=IF(AND(SUM($B9:M9)<=$E5,L10=L9*$F5),M9*$F5,IF(AND(SUM($B9:M9)>$E5,L10=L9*$F5),(M9-(SUM($B9:M9)-$E5))*$F5+(SUM($B9:M9)-$E5)*$G5,M9*$G5))
=IF(AND(SUM($B9:M9)>$E$5,(SUM($B9:M9)-M9)<$E$5)=TRUE,(($E$5-(SUM($B9:M9)-M9))*$F$5)+((SUM($B9:M9)-$E$5)*$G$5),IF(SUM($B9:M9)>$E$5,M9*$G$5,M9*$F$5))
=IF(AND(SUM(firstnum:M9)>$E$5,SUM($A$9:L9)<$E$5),($E$5-SUM($A$9:L9))*$F$5+(SUM(firstnum:M9)-$E$5)*$G$5,IF(SUM(firstnum:M9)>$E$5,M9*$G$5,M9*$F$5))
=IF(AND(SUM(firstnum:M9)>$E$5,SUM($A$9:L9)<$E$5),($E$5-SUM($A$9:L9))*$F$5+(SUM(firstnum:M9)-$E$5)*$G$5,IF(SUM(firstnum:M9)>$E$5,M9*$G$5,M9*$F$5))
=IF(AND(SUM(firstnum:M9)>$E$5,SUM($A$9:L9)<$E$5),($E$5-SUM($A$9:L9))*$F$5+(SUM(firstnum:M9)-$E$5)*$G$5,IF(SUM(firstnum:M9)>$E$5,M9*$G$5,M9*$F$5))
=IF(AND(SUM(firstnum:M9)>$E$5,SUM($A$9:L9)<$E$5),($E$5-SUM($A$9:L9))*$F$5+(SUM(firstnum:M9)-$E$5)*$G$5,IF(SUM(firstnum:M9)>$E$5,M9*$G$5,M9*$F$5))
=IF(AND(SUM(Month1:M9)>=Units,SUM(Month1:M9)-M9=Units)))
=IF(COLUMN(M9)=COLUMN($B$9),IF(M9<$E$5,M9*$F$5,($E$5*$F$5)+(M9-$E$5)*$G$5),IF(SUM($A9:L9)>$E$5,M9*$G$5,IF(SUM($B9:M9)>$E$5,(M9+$E$5-SUM($B$9:M9))*$F$5+((SUM($B$9:M9)-$E$5)*$G$5),M9*$F$5)))
=IF(GESTEP(SUM($B9:M9),$E5)=0,M9*$F5,IF(SUM($B9:M9)-M9>$E5,M9*$G5,((M9-(SUM($B9:M9)-$E5))*$F5)+((SUM($B9:M9)-$E5)*$G5)))
=IF(L10="Commission",IF(M9<=$E$5,M9*$F$5,$E$5*$F$5+(M9-$E$5)*$G$5),+(+(IF(SUM($B9:M9)<=$E$5,SUM($B9:M9),$E$5))*$F$5+(IF(SUM($B9:M9)>$E$5,SUM($B9:M9)-$E$5,0))*$G$5)-(+(IF((SUM($B9:L9))<=$E$5,(SUM($B9:L9)),$E$5))*$F$5+(IF((SUM($B9:L9))>$E$5,(SUM($B9:L9))-$E$5,0))*$G$5))
=IF(L10="Commission",IF(SUM($B9:M9)>$E5,($E5*$F5)+(SUM($B9:M9)-$E5)*$G5,SUM($B9:M9)*$F5),SUM(IF(SUM($B9:M9)>$E5,($E5*$F5)+(SUM($B9:M9)-$E5)*$G5,SUM($B9:M9)*$F5)-SUM($B10:L10)))
=IF(L10>$E$5,M9*$G$5,IF(M10<$E$5,M9*$F$5,IF(OR(M10>$E$5,M10=$E$5),((M10-$E$5)*$G$5)+(($E$5-L10)*$F$5),0)))
=IF(L10>$E$5,M9*$G$5,IF(M10>$E$5,((M9-(M10-$E$5))*$F$5)+((M10-$E$5)*$G$5),M9*$F$5))
=IF(L10>=$E$5,(M9*$G$5),+IF(M10>$E$5,((SUM($B9:M9)-$E$5)*$G$5)+(($E$5-SUM($B9:L9))*$F$5),M9*$F$5))
=IF(L11<$E$5,IF(M11<$E$5,M9*$F$5,(($E$5-L11)*$F$5)+((M9-($E$5-L11))*$G$5)),M9*$G$5)
=IF(L13="N",IF(M13="Y",($E$5-L12)*($F$5)+(M9-($E$5-L12))*$G$5,M9*$F$5),M9*$G$5)
=IF(M$10<=2000,M$9*$F$5,IF(M$9>=(M$10-2000),(M$9-(M$10-$E$5))*$F$5+(M$10-$E$5)*$G$5,M$9*$G$5))
=IF(M$100,SUM((M$12*COMMH),(ABS(M$11)*COMML)),PRODUCT(M$9,COMML)))
=IF(M$11<$E$5,M$9*$F$5,IF((M$11-M$9)<$E$5,($E$5-(M$11-M$9))*$F$5+(M$11-$E$5)*$G$5,M$9*$G$5))
=IF(M$11<$E$5,M$9*$F$5,IF((M$11-M$9)>2000,M$9*$G$5,(M$11-$E$5)*$G$5+(M$9-(M$11-$E$5))*$F$5))
=IF(M$9="",0,IF(M$14<2,M$9*$F$5,IF(M$14=2,(($E$5-SUM($B$9:L$9))*$F$5)+((SUM($B$9:M$9)-$E$5)*$G$5),M$9*$G$5)))
=IF(M$9="",0,IF(M$14<2,M$9*$F$5,IF(M$14=2,(($E$5-SUM($B$9:L$9))*$F$5)+((SUM($B$9:M$9)-$E$5)*$G$5),M$9*$G$5)))
=IF(M10<$E$5,$F$5*M9,IF((M10-M9-$E$5)<0,(M10-$E$5)*($G$5-$F$5)+M9*$F$5,$G$5*M9))
=IF(M10<$E$5,$F$5*M9,IF(L10<$E$5,$F$5*($E$5-L10)+$G$5*(M10-$E$5),$G$5*M9))
=IF(M10<$E$5,M9*$F$5,IF((M10-$E$5)>M9,M9*$G$5,((M9-(M10-$E$5))*$F$5)+(M9-(M9-(M10-$E$5)))*$G$5))
=IF(M10<$E$5,M9*$F$5,IF((M10-$E$5)>M9,M9*$G$5,((M9-(M10-$E$5))*$F$5)+(M9-(M9-(M10-$E$5)))*$G$5))
=IF(M10<$E$5,M9*$F$5,IF(L10<2000,($E$5-L10)*$F$5+(M10-$E$5)*$G$5,M9*$G$5))
=IF(M10<($E$5+1),M10*$F$5,(M10-$E$5)*$G$5+$E$5*$F$5)-SUM($B$11:L11)
=IF(M10<=$E$5,M9*$F$5,IF(AND(M10>$E$5,L10<=$E$5),($E$5-L10)*$F$5+(M9-($E$5-L10))*$G$5,M9*$G$5))
=IF(M10<=$E$5,M9*$F$5,IF(AND(M10>$E$5,L10>$E$5),M9*$G$5,((M10-$E$5)*$G$5)+((($E$5+M9)-M10)*$F$5)))
=IF(M10<=Mark,M9*HighC,IF(L10=IF(M10<2000,M9*$F5,IF(L10>2000,M9*$G5,(M10-$E5)*$G5+($E5-L10)*$F5))
=IF(M10<2000,M9*$F5,IF(L10>2000,M9*$G5,(M10-$E5)*$G5+($E5-L10)*$F5))
=IF(M10<2000,M9*1.5,IF(AND(M10>2000,M11>0),((2000-L10)*1.5)+((M9-(2000-L10))*1.25),M9*1.25))
=IF(M10<2000,M9*1.5,IF(M10-M9<2000,((M9-(M10-2000))*1.5+(M10-2000)*1.25),M9*1.25))
=IF(M10>$E$5,IF(L10>$E$5,M9*$G$5,(M10-$E$5)*$G$5+(M9-M10+$E$5)*$F$5),M9*$F$5)
=IF(M11 < $E$5,IF(M11 +M9 <$E$5, M9*$F$5, (SUM(M11,M9) - $E$5) *$G$5 + ((M9- ((M11 +M9) - $E$5)) *$F$5)),M9*$F$5)
=IF(M11 < $E$5,IF(M11 +M9 <$E$5, M9*$F$5, (SUM(M11,M9) - $E$5) *$G$5 + ((M9- ((M11 +M9) - $E$5)) *$F$5)),M9*$G$5)
=IF(M11<$E$5,$F$5,IF(L11<$E$5,(((M11-$E$5)*$G$5)+((M9-M11+$E$5)*$F$5))/M9,$G$5))*M9
=IF(M11<$E$5,M9*$F$5,IF((M11-M9)<$E$5,((M9-(M11-$E$5))*$F$5)+((M11-2000)*$G$5),M9*$G$5))
=IF(M11<$E$5,M9*$F$5,IF(L11>$E$5,M9*$G$5,(((M9-(M11-$E$5))*$F$5)+(M11-$E$5)*$G$5)))
=IF(M11<$E$5+1,M9*$F$5,IF(L11<$E$5+1,($E$5-L11)*$F$5+(M11-$E$5)*$G$5,M9*$G$5))
=IF(M11<$E5,+M9*$F5,IF(M12>$E5,+M9*$G5,(($E5-M12)*$F5)+((M11-$E5)*$G5)))
=IF(M11<=$E$5,M9*$F$5,IF(AND(M11>$E$5,L11<$E$5),(M11-$E$5)*$G$5+($E$5-L11)*$F$5,M9*$G$5))
=IF(M11<=$E$5,M9*$F$5,IF(L11>=$E$5,M9*$G$5,(($E$5-L11)*$F$5)+(M11-$E$5)*$G$5))
=IF(M11<=$E5,+M9*$F5,IF(M12>$E5,+M9*$G5,(($E5-M12)*$F5)+((M11-$E5)*$G5)))
=IF(M11<=2000,M9*1.5,IF(L11>2000,M9*1.25,(2000-L11)*1.5+(M11-2000)*1.25))
=IF(M11<=level2000,M9*com1.5,IF(L12<=0,(level2000-L11)*com1.5+(M11-level2000)*com1.25,M9*com1.25))
=IF(M11<0,M9*$F$5,IF(M11=IF(M11<0,M9*$F$5,IF(M9>=M11,(M9-M11)*$F$5+M11*$G$5,M9*$G$5))
=IF(M11<1,M9*$F$5,IF(L11>0,M9*$G$5,(M11*$G$5)+((M9-M11)*$F$5)))
=IF(M11<2000,M9*1.5,IF((M11-M9)<2000,((M9-(M11-2000))*1.5)+((M11-2000)*1.25),M9*1.25))
=IF(M11<2000,M9*1.5,IF(L11<2000,(2000-L11)*1.5+(M11-2000)*1.25,M9*1.25))
=IF(M11="N",M9*1.5,IF(M11="Y",M9*1.25,(2000-SUM($B$9:L9))*1.5+(SUM($B$9:M9)-2000)*1.25))
=IF(M11>$E$5, IF(L11<$E$5,($E$5-L11)*$F$5+(M11-$E$5)*$G$5,M9*$G$5),M9*$F$5)
=IF(M11>$E$5,(IF((($E$5-L11)*$F$5)>0,IF(((M11-$E$5)*$G$5)>0,(($E$5-L11)*$F$5)+(M11-$E$5)*$G$5),(M11-L11)*$G$5)),M9*$F$5)
=IF(M11>$E$5,IF(L11>$E$5,M9*$G$5,(M9-M11+$E$5)*$F$5+(M11-$E$5)*$G$5),M9*$F$5)
=IF(M11>$E$5,IF(L11>$E$5,M9*$G$5,(M9-M12)*$F$5+M12*$G$5),M9*$F$5)
=IF(M11>$E$5,IF(L11>$E$5,ROUND(M9*$G$5,1),ROUND((M11-$E$5)*$G$5+(M9-(M11-$E$5))*$F$5,1)),ROUND($F$5*M9,1))
=IF(M11>0,(M11*$F$5+M12*$G$5),IF(M11<0,M9*$G$5,M9*$F$5))
=IF(M11>E5,IF(M9>M11-E5,((M11-E5)*N5)+((M9-(M11-E5))*M5),M9*$G$5),M9*$F$5)
=IF(M12<$E$5,$F$5*M9,$E$5*$F$5+(M12-$E$5)*$G$5-L13)
=IF(M12<$E$5,$F$5*M9,IF(L12>$E$5,M9*$G$5,(M12-$E$5)*$G$5+($E$5-L12)*$F$5))
=IF(M12<$E$5,M9*$F$5,IF(AND(L12<$E$5,M12>=$E$5),(($E$5-L12)*$F$5)+((M12-$E$5)*$G$5),M9*$G$5))
=IF(M12<=$E$5,$F$5*M9,IF(L12>$E$5,$G$5*M9,(L5-L12)*M5+(M12-L5)*N5))
=IF(M12<=$E$5,M10*$F$5,IF(M10<(M12-$E$5),M10*$G$5,(((M12-$E$5-M10)*-1*($F$5-$G$5)+M10*$G$5))))
=IF(M12<=$E$5,M10*$F$5,IF(M10<(M12-$E$5),M10*$G$5,(((M12-$E$5-M10)*-1*($F$5-$G$5)+M10*$G$5))))
=IF(M12<=$E$5,M9*$F$5,IF((M12-M9)<=$E$5,(M9-(M12-$E$5))*$F$5+(M12-$E$5)*$G$5,M9*$G$5))
=IF(M12<=$E$5,M9*$F$5,IF(L12<=$E$5,SUM((M12-$E$5)*$G$5,($E$5-L12)*$F$5),M9*$G$5))
=IF(M12<=$E$5,M9*$F$5,IF(L12<=$E$5,SUM((M12-$E$5)*$G$5,($E$5-L12)*$F$5),M9*$G$5))
=IF(M12<=$E$5,M9*$F$5,IF(M12-M9<=$E$5,(M12-$E$5)*$G$5-(M12-M9-$E$5)*$F$5,M9*$G$5))
=IF(M12<=$E5,M9*$F5,IF(L12>=$E5,M9*$G5,((M12-$E5)*$G5+($E5-L12)*M5)))
=IF(M12<=hurdle,M9*level1,IF(AND(L12>hurdle,M12>hurdle),M9*level2,((hurdle-M12)+M9)*level1+((M12-hurdle)*level2)))
=IF(M12<2000,M9*$F$5,IF(L12<2000,(((2000-L12)*$F$5)+(((M9-(2000-L12))*$G$5))),(M9*$G$5)))
=IF(M12=L12,M12*M9,($E$5-L13)*L12+(M13-$E$5)*M12)
=IF(M12=L12,M12*M9,($E$5-L13)*L12+(M13-$E$5)*M12)
=IF(M12>$E$5,IF(L12<$E$5,($E$5-L12)*$F$5+(M12-$E$5)*$G$5,M9*$G$5),M9*$F$5)
=IF(M12>=0,M9*$F5,IF(L12<0,M9*$G5,((M12*-1)*$G5)+(M12+M9)*$F5))
=IF(M12>TOPE,(IF(M12-M9=IF(M13<=$E$5, M9*$F$5, IF(AND(M13>$E$5, M13-M9>$E$5), M9*$G$5, ((M13-2000)*$G$5+(M9+$E$5-M13)*$F$5)))
=IF(M13<=$E$5,M9*$F$5,IF(AND(M14<0,L14<0)=TRUE,M9*$G$5,L14*M5+ABS(M14)*N5))
=IF(M13<0,M9*1.5,(M9*1.5)-(M15*0.25)) "
=IF(M13>0,M10*$F$5, IF(M13=0,(L13*$F$5)+(M10-L13)*$G$5))"
=IF(M13>0,M9*$F$5,IF(M9+M13>0,((M9+M13)*$F$5)+((M13*-1)*$G$5),M9*$G$5))
=IF(M14 = FALSE, IF(M13 < $E$5, M9 *$F$5, M9 * $G$5), M15 * $G$5 + (M9 - M15) * $F$5)
=IF(M14<=$E$5,M14*$F$5-M15,($E$5*$F$5-M15)+(M14-$E$5)*$G$5)
=IF(M14<=$E$5,M9*$F$5,IF(AND(M14>$E$5,L14<$E$5),($E$5-L14)*$F$5+($F$14-$E$5)*$G$5,M9*$G$5))
=IF(M14<0,M9*$F$5,IF(M15>0,(M15*$F$5+M14*$G$5),M9*$G$5))
=IF(M15<$E$5,M9*$F$5,IF((M15-$E$5)>M9,M9*$G$5,((M15-$E$5)*N5)+($E$5-L15)*$F$5))
=IF(M15=0,M14*$F$5,M15*$F$5)+IF(M16=0,M13*$G$5,M16*$G$5)
=IF(M19<0,M9*$F$5,IF(M21<0,M9*$G$5,M23))
=IF(M7>$E$5,$E$5*$F$5+$G$5*(M7-$E$5),M7*($F$5))-L11
=IF(M7>$E$5,M9*$G$5,M9*$F$5)
=IF(M8="Jan",IF($B9>$E5,((($B9-$E5)*$G5)+($E5*$F5)),($B9*$F5)),ROUND(IF(SUM($B9:M9)>$E5,IF(SUM($B9:L9)<$E5,((SUM($B9:M9)-$E5)*$G5)+(($E5-SUM($B9:L9))*$F5),M9*$G5),M9*$F5),2))
=IF(M9-(SUM($B9:M9)-IF(SUM($B9:M9)<$E$5,SUM($B9:M9),$E$5))>0,M9-(SUM($B9:M9)-IF(SUM($B9:M9)<$E$5,SUM($B9:M9),$E$5)),0)*$F$5+(M9-IF(M9-(SUM($B9:M9)-IF(SUM($B9:M9)<$E$5,SUM($B9:M9),$E$5))>0,M9-(SUM($B9:M9)-IF(SUM($B9:M9)<$E$5,SUM($B9:M9),$E$5)),0))*$G$5
=IF(N12<$E$5,(N12-M12)*$F$5,IF(M12<$E$5,($E$5-M12)*$F$5+(N12-$E$5)*$G$5,(N12-M12)*$G$5))
=IF(OFFSET(M10,0,-1)<=$E$5,IF(OFFSET(M11,-1,0)<=$E$5,M9*$F$5,($E$5-OFFSET(M11,-1,-1))*$F$5+(M10-$E$5)*$G$5),M9*$G$5)
=IF(OR(SIGN($E$5-SUM($B9:M9))=1,SIGN($E$5-SUM($B9:M9))=0),M9*$F$5,IF(AND(SIGN($E$5-SUM($B9:M9))=-1,SIGN($E$5-SUM($A9:L9))=1),($E$5-SUM($A9:L9))*$F$5+(M9-($E$5-SUM($A9:L9)))*$G$5,M9*$G$5))
=IF(OR(SUM($B$9:M9)<$E$5,SUM($B$9:M9)=$E$5),M9*$F$5,SUM($B$9:M9)*$F$5-(SUM($B$9:M9)-$E$5)*($F$5-$G$5)-SUM($B$10:L10))
=IF(Sales<$E$5,Sales*$F$5-commission,(Sales-$E$5)*$G$5+($E$5*$F$5)-commission) "
=IF(sold_this_year<=top,sales*comm1, IF(Difference_to_Top=IF(SUBTOTAL(9,$B$9:M$9)>$E$5, IF($E$5+ M$9 >SUBTOTAL(9,$B$9:M$9), ((($E$5+ M$9)-SUBTOTAL(9,$B$9:M$9))*$F$5)+ ((M9-(($E$5+ M$9)-SUBTOTAL(9,$B$9:M$9)))*$G$5),M$9*$G$5),M$9*$F$5)
=IF(SUM($A$9:L9)>$E$5,M9*$G$5,IF(SUM($A$9:M9)<=$E$5,M9*$F$5,(($E$5-SUM($A9:L$9))*$F$5+(SUM($A$9:M9)-$E$5)*$G$5)))
=IF(SUM($A$9:L9)>$E$5,M9*$G$5,IF(SUM($A$9:M9)>$E$5,($E$5-SUM($A$9:L9))*$F$5+((SUM($A$9:M9)-$E$5)*$G$5),M9*$F$5))
=IF(SUM($A$9:L9)>$E$5,M9*$G$5,IF(SUM($A$9:M9)>$E$5,((SUM($A$9:M9)-$E$5)*$G$5)+((M9-(SUM($A$9:M9)-$E$5))*$F$5),M9*$F$5))
=IF(SUM($A$9:L9)>$E$5,M9*$G$5,IF(SUM($B$9:M9)>$E$5,(SUM($B$9:M9)-$E$5)*$G$5+(M9-(SUM($B$9:M9)-$E$5))*$F$5,M9*$F$5))
=IF(SUM($A$9:M9)<$E$5,M9*$F$5,IF($E$5-SUM($A$9:L9)>0,(($E$5-SUM($A$9:L9))*$F$5)+((M9-($E$5-SUM($A$9:L9)))*$G$5),M9*$G$5))
=IF(SUM($A$9:M9)<$E$5,M9*$F$5,IF(SUM($A$9:L9)<$E$5,(SUM($B$9:M9)-$E$5)*$G$5+(M9-(SUM($B$9:M9)-$E$5))*$F$5,M9*$G$5))
=IF(SUM($A$9:M9)<=$E$5,$F$5*M9,IF(SUM($A$9:L9)>$E$5,$G$5*M9,(($E$5-SUM($A$9:L9))*$F$5)+(SUM($A$9:M9)-$E$5)*$G$5))
=IF(SUM($A$9:M9)<=$E$5,M9*$F$5,IF((SUM($B$9:M9)-$E$5)>M9,M9*$G$5,(SUM($B$9:M9)-$E$5)*$G$5+(M9-(SUM($B$9:M9)-$E$5))*$F$5))
=IF(SUM($A$9:M9)<=$E$5,M9*$F$5,IF(AND(SUM($A$9:L9)<=$E$5,SUM($A$9:M9)>$E$5),(($E$5-(SUM($A$9:L9)))*$F$5)+((SUM($A$9:M9)-$E$5)*$G$5),M9*$G$5))
=IF(SUM($A$9:M9)<=$E$5,M9*$F$5,IF(AND(SUM($A$9:L9)<=$E$5,SUM($A$9:M9)>$E$5),(2000-SUM($A$9:L9))*$F$5+(SUM($A$9:M9)-$E$5)*$G$5,IF(SUM($A$9:M9)>$E$5,M9*$G$5)))
=IF(SUM($A$9:M9)<=$E$5,M9*$F$5,IF(SUM($A$9:L9)>=$E$5,M9*$G$5,($E$5-SUM($A$9:L9))*$F$5+(SUM($A$9:M9)-$E$5)*$G$5))
=IF(SUM($A$9:M9)<=$E$5,SUM($A$9:M9)*$F$5-SUM($A$10:L10),(SUM($A$9:M9)-$E$5)*$G$5+3000-SUM($A$10:L10))
=IF(SUM($A9:L9) > $E5, M9*$G5, IF(SUM($A9:M9) <= $E5, M9 * $F5, M9 * $F5 - ((SUM($A9:M9) - $E5) * ($F5 - $G5))))
=IF(SUM($A9:L9) > $E5, M9*$G5, IF(SUM($A9:M9) <= $E5, M9 * $F5, M9 * $F5 - (SUM($A9:M9) - $E5) * ($F5 - $G5)))
=IF(SUM($A9:L9)<=2000,IF(SUM($A9:M9)>2000,(2000-SUM($A9:L9))*1.5+(SUM($A9:M9)-2000)*1.25,M9*1.5),M9*1.25)
=IF(SUM($A9:L9)>=$E$5,(M9*$G$5),+IF(SUM($B9:M9)>$E$5,((SUM($B9:M9)-$E$5)*$G$5)+(($E$5-SUM($B9:L9))*$F$5),M9*$F$5))
=IF(SUM($A9:M9)<$E$5,M9*$F$5,IF(SUM($A9:L9)>$E$5,M9*$G$5,($E$5-SUM($A9:L9))*$F$5+(SUM($A9:M9)-$E$5)*$G$5))
=IF(SUM($B$10:M10)<$E$6,M10*$F$6,IF(SUM($B$10:M10,-M10)>$E$6,M10*$G$6,($F$6-$G$6)*($E$6-SUM($B$10:M10,-M10))+$G$6*M10))
=IF(SUM($B$13:M13)>=$E$5,IF(SUM($A$13:L13)>=$E$5,M13*$G$5,($E$5-SUM($A$13:L13))*$F$5+(M13-($E$5-SUM($A$13:L13)))*$G$5),M13*$F$5)
=IF(SUM($B$8:M8)<=$E$5,M8*$F$5,IF((SUM($B$8:M8)-M8)<=$E$5,(($E$5-(SUM($B$8:M8)-M8))*$F$5)+((SUM($B$8:M8)-$E$5)*$G$5),M8*$G$5))
=IF(SUM($B$9:$M$9)<=$E$5,$M$9*$F$5,IF(SUM($B$9:$L$9)>$E$5,$M$9*$G$5,(($E$5-(SUM($B$9:$M$9)-$M$9))*$F$5)+(($M$9-($E$5-(SUM($B$9:$M$9)-$M$9)))*$G$5)))
=IF(SUM($B$9:L$9)>=2000,M$9*1.25,IF(SUM($B$9:M$9)>=2000,((IF((M$9-(SUM($B$9:M$9)-2000))>0,(M$9-(SUM($B$9:M$9)-2000)),0))*1.5)+(SUM($B$9:M$9)-2000)*1.25,SUM($B$9:M$9)*1.5))
=IF(SUM($B$9:L9)>$E$5,(M9*$G$5),IF((SUM($B$9:L9)+M9)>$E$5,($F$5*($E$5-SUM($B$9:L9))+($G$5*(SUM($B$9:M9)-$E$5))),IF(SUM($B$9:M9)<$E$5,M9*$F$5)))
=IF(SUM($B$9:L9)>$E$5,M9*$G$5,IF(SUM($B$9:M9)<=$E$5,M9*$F$5,(($E$5-SUM($B$9:L9))*$F$5+(SUM($B$9:M9)-$E$5)*$G$5)))
=IF(SUM($B$9:L9)>=$E$5,M9*$G$5,IF(SUM($B$9:M9)<=$E$5,M9*$F$5,($E$5-SUM($B$9:L9))*$F$5+(SUM($B$9:M9)-$E$5)*$G$5))
=IF(SUM($B$9:L9)>=2000,M9*1.25,IF(SUM($B$9:M9)>=2000,((2000-SUM($B$9:L9))*1.5)+(M9-(2000-SUM($B$9:L9)))*1.25,M9*1.5))
=IF(SUM($B$9:M$9)<$E$5,M$9*$F$5,IF(SUM($B$9:L$9)<$E$5,(($E$5-SUM($B$9:L$9))*$F$5)+((M$9-($E$5-SUM($B$9:L$9)))*$G$5),M$9*$G$5))
=IF(SUM($B$9:M$9)<$E$5,M9*$F$5,(($E$5*$F$5)+(SUM($B$9:M$9)-$E$5)*$G$5)-SUM($A$10:L$10))
=IF(SUM($B$9:M$9)<$E$5,M9*$F$5,IF((SUM($B$9:M$9)-$E$5)>M9,M9*$G$5,(((M9-(SUM($B$9:M$9)-$E$5))*$F$5)+((SUM($B$9:M$9)-$E$5)*$G$5))))
=IF(SUM($B$9:M$9)<=$E$5,$F$5*M$9,IF(COLUMN()=2,$E$5*$F$5+(M$9-$E$5)*$G$5,IF(SUM($B$9:L$9)<=$E$5,($E$5-SUM($B$9:L$9))*$F$5+(M$9-($E$5-SUM($B$9:L$9)))*$G$5,$G$5*M$9)))
=IF(SUM($B$9:M$9)<=$E$5,$F$5*M$9,IF(SUM($B$9:L$9)>$E$5,$G$5*M$9,$F$5*($E$5-SUM($B$9:L$9))+$G$5*SUM($B$9:M$9,-$E$5)))
=IF(SUM($B$9:M$9)<=$E$5,M$9*$F$5,$E$5*$F$5+(SUM($B$9:M$9)-$E$5)*$G$5-(IF(M$8="Jan",M$10,SUM($A$10:L$10))))
=IF(SUM($B$9:M$9)<=$E$5,M$9*$F$5,IF(AND(($E$5+M$9)>SUM($B$9:M$9),SUM($B$9:M$9)>$E$5),((SUM($B$9:M$9)-$E$5)*$G$5+($E$5+M$9-SUM($B$9:M$9))*$F$5),M$9*$G$5))
=IF(SUM($B$9:M$9)<=$E$5,M$9*$F$5,IF(SUM($B$9:L$9)>$E$5,M$9*$G$5,IF(AND(SUM($B$9:L$9)<$E$5,SUM($B$9:M$9)>$E$5),(($E$5-SUM($B$9:L$9))*$F$5)+((SUM($B$9:M$9)-$E$5)*$G$5))))
=IF(SUM($B$9:M$9)<=$E$5,M$9*($F$5-$G$5),IF(SUM($B$9:L$9)<$E$5,((($E$5-SUM($B$9:L$9))*($F$5-$G$5))),0))+(M$9*$G$5)
=IF(SUM($B$9:M$9)<=($E$5),M$9*$F$5,IF(($E$5-SUM($A$9:L$9))>=0,SUM(($E$5-SUM($A$9:L$9))*$F$5,(SUM($B$9:M$9)-$E$5)*$G$5),M$9*$G$5))
=IF(SUM($B$9:M$9)>$E$5,IF(SUM($A$9:L$9)>$E$5,$G$5*M$9,(($E$5-SUM($A$9:L$9))*$F$5)+(M$9-($E$5-SUM($A$9:L$9)))*$G$5),$F$5*M$9)
=IF(SUM($B$9:M$9)>=2000,(SUM($B$9:M$9)-2000)*1.25+2000*1.5,SUM($B$9:M$9)*1.5)-SUM($B$10:L$10)
=IF(SUM($B$9:M9)-$E$5<0,M9*$F$5,IF(SUM($B$9:M9)-$E$5>M9,M9*$G$5,(SUM($B$9:M9)-$E$5)*$G$5+(M9-(SUM($B$9:M9)-$E$5))*$F$5))
=IF(SUM($B$9:M9)<$E$5,M9*$F$5,$E$5*$F$5+(SUM($B$9:M9)-$E$5)*$G$5-SUM($B$10:L10))
=IF(SUM($B$9:M9)<$E$5,M9*$F$5,($E$5*$F$5+((SUM($B$9:M9)-$E$5)*$G$5)-SUM($B$10:L10)))
=IF(SUM($B$9:M9)<$E$5,M9*$F$5,((SUM($B$9:M9)-$E$5)*$G$5+($E$5*$F$5)-(SUM($B$10:L10))))
=IF(SUM($B$9:M9)<$E$5,M9*$F$5,(IF((SUM($B$9:M9)-M9)<$E$5,(($E$5-(SUM($B$9:M9)-M9))*$F$5)+((SUM($B$9:M9)-($E$5))*$G$5),(M9*$G$5))))
=IF(SUM($B$9:M9)<$E$5,M9*$F$5,(IF(M9>(SUM($B$9:M9)-$E$5),(SUM($B$9:M9)-$E$5)*$G$5,M9*$G$5))+(IF(M9>(SUM($B$9:M9)-$E$5),M9-(SUM($B$9:M9)-$E$5),0))*$F$5)
=IF(SUM($B$9:M9)<$E$5,M9*$F$5,0)+IF(AND(SUM($B$9:M9)-M9<=$E$5,SUM($B$9:M9)>$E$5),($E$5-SUM($B$9:M9)+M9)*$F$5+(SUM($B$9:M9)-$E$5)*$G$5,IF(SUM($B$9:M9)-$E$5>0,M9*$G$5,0))
=IF(SUM($B$9:M9)<$E$5,M9*$F$5,IF(AND(SUM($B$9:M9)>$E$5,SUM($B$9:M9)-M9<$E$5),$F$5*M9-($F$5-$G$5)*(SUM($B$9:M9)-$E$5),M9*$G$5))
=IF(SUM($B$9:M9)<$E$5,M9*$F$5,IF(SUM($B$9:L9)<$E$5,($E$5-SUM($B$9:L9))*$F$5+(SUM($B$9:M9)-$E$5)*$G$5,M9*$G$5))
=IF(SUM($B$9:M9)<$E$5,M9*$F$5,IF(SUM($B$9:L9)<$E$5,($E$5-SUM($B$9:L9))*$F$5+(SUM($B$9:M9)-$E$5)*$G$5,M9*$G$5))
=IF(SUM($B$9:M9)<$E$5,M9*$F$5,IF(SUM($B$9:L9)<$E$5,((($E$5-SUM($B$9:L9)))*$F$5)+(M9-($E$5-SUM($B$9:L9)))*$G$5,M9*$G$5))
=IF(SUM($B$9:M9)<$E$5,M9*$F$5,IF(SUM($B$9:L9)<$E$5,(SUM($B$9:M9)-$E$5)*$G$5+($E$5-SUM($B$9:L9))*$F$5,M9*$G$5))
=IF(SUM($B$9:M9)<$E$5,M9*$F$5,IF(SUM($B$9:L9)>$E$5,M9*$G$5,((SUM($B$9:M9)-$E$5)*$G$5)+(M9-(SUM($B$9:M9)-$E$5))*$F$5))
=IF(SUM($B$9:M9)<$E$5,M9*$F$5,IF(SUM($B$9:L9)>$E$5,M9*$G$5,(M9-MOD(SUM($B$9:M9),$E$5))*$F$5+(M9-(M9-MOD(SUM($B$9:M9),$E$5)))*$G$5))
=IF(SUM($B$9:M9)<$E$5,M9*$F$5,IF(SUM($B$9:L9)>$E$5,M9*$G$5,(SUM($B$9:M9)-$E$5)*$G$5+($E$5-SUM($B$9:L9))*$F$5))
=IF(SUM($B$9:M9)<$E$5,M9*$F$5,IF(SUM($B$9:L9)>L5,M9*$G$5,(SUM($B$9:M9)-L5)*$G$5+(M9-((SUM($B$9:M9)-$E$5)))*$F$5))
=IF(SUM($B$9:M9)<$E$5,M9*$F$5,IF(SUM($B$9:M9)>$E$5,IF(M9>(SUM($B$9:M9)-$E$5),(M9-((SUM($B$9:M9)-$E$5)))*$F$5+((SUM($B$9:M9)-$E$5)*$G$5),M9*$G$5)))
=IF(SUM($B$9:M9)<$E$5,M9*$F$5,IF(SUM($B$9:M9)>$E$5,IF(SUM($B$9:M9)-$E$5>M9,M9*$G$5,(((M9-(SUM($B$9:M9)-$E$5))*$F$5))+(SUM($B$9:M9)-$E$5)*$G$5)))
=IF(SUM($B$9:M9)<$E$5,M9*$F$5,IF(SUM($B$9:M9)>$E$5+M9,M9*$G$5,((SUM($B$9:M9)-$E$5)*$G$5)+(M9-(SUM($B$9:M9)-$E$5))*$F$5))
=IF(SUM($B$9:M9)<$E$5,M9*$F$5,IF(SUM($B$9:M9)-M9>$E$5,M9*$G$5,((M9+$E$5-SUM($B$9:M9))*$F$5)+(SUM($B$9:M9)-$E$5)*$G$5))
=IF(SUM($B$9:M9)<$E$5,SUM($B$9:M9)*$F$5,$E$5*$F$5+(SUM($B$9:M9)-$E$5)*$G$5)-SUM($A$10:L10)
=IF(SUM($B$9:M9)<$E$5,SUM($B$9:M9)*$F$5,$E$5*$F$5+(SUM($B$9:M9)-$E$5)*$G$5)-SUM($B$10:L10)
=IF(SUM($B$9:M9)<$E$5+1,M9*$F$5,IF(SUM($B$9:M9)-$E$5>M9,M9*$G$5,(($E$5-SUM($B$9:L9))*$F$5)+(M9-($E$5-SUM($B$9:L9)))*$G$5))
=IF(SUM($B$9:M9)<($E$5+1),M9*$F$5,IF(SUM($B$9:M9)>($E$5+M9),M9*$G$5,(($E$5-(SUM($B$9:M9)-M9))*$F$5)+((SUM($B$9:M9)-$E$5)*$G$5)))
=IF(SUM($B$9:M9)<=$E$5, M9*$F$5, IF(SUM($B$9:L9)>=$E$5, M9*$G$5,($E$5-SUM($B$9:L9))*$F$5 + (SUM($B$9:M9)-$E$5)*$G$5))
=IF(SUM($B$9:M9)<=$E$5,$F$5*M9,IF((AND((SUM($B$9:M9)>$E$5),(SUM($B$9:L9))>$E$5)),$G$5*M9,($E$5-SUM($B$9:L9))*$F$5+(M9-($E$5-SUM($B$9:L9)))*$G$5))
=IF(SUM($B$9:M9)<=$E$5,$F$5*M9,IF((SUM($B$9:M9)-$E$5)=IF(SUM($B$9:M9)<=$E$5,$F$5*M9,IF(SUM($A$9:L9)>=$E$5,M9*$G$5,($E$5-SUM($A$9:L9))*$F$5+((SUM($B$9:M9)-$E$5)*$G$5)))
=IF(SUM($B$9:M9)<=$E$5,$F$5*M9,IF(SUM($B$9:M9)-M9<=$E$5,($E$5-(SUM($B$9:M9)-M9))*$F$5+(SUM($B$9:M9)-$E$5)*$G$5,M9*$G$5))
=IF(SUM($B$9:M9)<=$E$5,(M9*$F$5),IF(SUM($B$9:L9)>2000,($G$5*M9),((SUM($B$9:M9)-2000)*$G$5)+((M9-(SUM($B$9:M9)-2000))*$F$5)))
=IF(SUM($B$9:M9)<=$E$5,M9*$F$5,IF((SUM($B$9:M9)-$E$5)>M9,M9*$G$5,(SUM($B$9:M9)-$E$5)*$G$5+(M9-(SUM($B$9:M9)-$E$5))*$F$5))
=IF(SUM($B$9:M9)<=$E$5,M9*$F$5,IF(AND(SUM($B$9:M9)>$E$5,(SUM($B$9:M9)-$E$5)>M9),M9*$G$5,(SUM($B$9:M9)-$E$5)*$G$5+(M9-(SUM($B$9:M9)-$E$5))*$F$5))
=IF(SUM($B$9:M9)<=$E$5,M9*$F$5,IF(SUM($A$9:L9)<=$E$5,($E$5-SUM($A$9:L9))*$F$5+(SUM($B$9:M9)-$E$5)*$G$5,M9*$G$5))
=IF(SUM($B$9:M9)<=$E$5,M9*$F$5,IF(SUM($B$9:L9)<$E$5,(SUM($B$9:M9)-$E$5)*$G$5+(M9-(SUM($B$9:M9)-$E$5))*$F$5,M9*$G$5))
=IF(SUM($B$9:M9)<=$E$5,M9*$F$5,IF(SUM($B$9:L9)<=$E$5,(($E$5-SUM($B8:L9))*$F$5)+((SUM($B8:M9)-$E$5)*$G$5),M9*$G$5))
=IF(SUM($B$9:M9)<=$E$5,M9*$F$5,IF(SUM($B$9:L9)>$E$5,M9*$G$5,($E$5-SUM($B$9:L9))*$F$5+(M9-($E$5-SUM($B$9:L9)))*$G$5))
=IF(SUM($B$9:M9)<=$E$5,M9*$F$5,IF(SUM($B$9:L9)>$E$5,M9*$G$5,($E$5-SUM($B$9:L9))*$F$5+(M9-($E$5-SUM($B$9:L9)))*$G$5))
=IF(SUM($B$9:M9)<=$E$5,M9*$F$5,IF(SUM($B$9:L9)>$E$5,M9*$G$5,(SUM($B$9:M9)-$E$5)*$G$5+(M9-((SUM($B$9:M9))-$E$5))*$F$5))
=IF(SUM($B$9:M9)<=$E$5,M9*$F$5,IF(SUM($B$9:M9)-M9<$E$5,($E$5-(SUM($B$9:M9)-M9))*$F$5+((SUM($B$9:M9)-$E$5)*$G$5),M9*$G$5))
=IF(SUM($B$9:M9)<=$E$5,SUM($B$9:M9)*$F$5,$E$5*$F$5+(SUM($B$9:M9)-$E$5)*$G$5)-SUM($A$10:L10)
=IF(SUM($B$9:M9)<=$E$5,SUM($B$9:M9)*$F$5,$E$5*$F$5+(SUM($B$9:M9)-$E$5)*$G$5)-SUM($B$10:L10)
=IF(SUM($B$9:M9)<=$E$5,SUM($B$9:M9)*$F$5,IF(SUM($B$9:M9)>=$E$5,SUM(2000*$F$5)+SUM(SUM($B$9:M9)-2000)*$G$5))-SUM($B$10:L10)
=IF(SUM($B$9:M9)<=$E$5,SUM(($B$9:M9)*$F$5),((($E$5*$F$5)+((SUM($B$9:M$9)-$E$5))*$G$5)-SUM($B$10:L10)))
=IF(SUM($B$9:M9)<=$E$5,SUM(M9)*$F$5,IF(SUM($B$9:L$9)>$E$5,M9*$G$5,((M9-(SUM($B$9:M9)-$E$5))*$F$5)+(SUM($B$9:M9)-$E$5)*$G$5))
=IF(SUM($B$9:M9)<=2000,M9*1.5,IF(SUM($B$9:M9)-M9>2000,M9*1.25,MOD(SUM($B$9:M9),2000)*1.25+(M9-MOD(SUM($B$9:M9),2000))*1.5))
=IF(SUM($B$9:M9)<=sales_point,M9*commission_1,IF((SUM($B$9:M9)-M9)<=sales_point,((sales_point-(SUM($B$9:M9)-M9))*commission_1)+((SUM($B$9:M9)-sales_point)*commission_2),M9*commission_2))
=IF(SUM($B$9:M9)<=target,M9*TopCom,IF(AND(SUM($B$9:M9)>target,M9>SUM($B$9:M9)-target),(ABS(SUM($B$9:M9)-target-M9)*TopCom)+(SUM($B$9:M9)-target)*LowCom,M9*LowCom))
=IF(SUM($B$9:M9)<2000,1.5*M9,IF(SUM($B$9:L9)<2000,1.5*(2000-SUM($B$9:L9))+1.25*(M9-(2000-SUM($B$9:L9))),1.25*M9))
=IF(SUM($B$9:M9)<2000,M9*1.5,IF((SUM($B$9:M9)-2000)>M9,M9*1.25,(M9-(SUM($B$9:M9)-2000)*1.5+(SUM($B$9:M9)-2000)*1.25)))
=IF(SUM($B$9:M9)<2000,M9*1.5,IF(AND(SUM($A$9:L9)<2000,SUM($B$9:M9)>2000),((2000-SUM($A$9:L9))*1.5)+(SUM($B$9:M9)-2000)*1.25,M9*1.25))
=IF(SUM($B$9:M9)<2000,M9*1.5,IF(AND(SUM($B$9:M9)>2000,SUM($B$9:L9)>2000),M9*1.25,(SUM($B$9:M9)-2000)*1.25+(M9-(SUM($B$9:M9)-2000))*1.5))
=IF(SUM($B$9:M9)<2000,M9*1.5,IF(SUM($A$9:L9)>2000,M9*1.25,((SUM($B$9:M9)-2000)*1.25)+((2000-SUM($A$9:L9))*1.5)))
=IF(SUM($B$9:M9)<2000,M9*1.5,IF(SUM($B$9:L9)>2000,M9*1.25,(SUM($B$9:M9)-2000)*1.25+(2000-SUM($B$9:L9))*1.5))
=IF(SUM($B$9:M9)Limit,SUM($B$9:L9)>Limit),M9*Morethen2000,(SUM($B$9:M9)-Limit)*Morethen2000+(M9-(SUM($B$9:M9)-Limit))*LessThen2000))
=IF(SUM($B$9:M9)>$E$5,($E$5*$F$5+(SUM($B$9:M9)-$E$5)*$G$5)-SUM($B$10:L10),(SUM($B$9:M9)*$F$5)-SUM($B$10:L10))
=IF(SUM($B$9:M9)>$E$5,(($E$5*$F$5)+((SUM($B$9:M9)-$E$5)*$G$5))-SUM($B$10:L10),(SUM($B$9:M9)*$F$5)-SUM($B$10:L10))
=IF(SUM($B$9:M9)>$E$5,(SUM($B$9:M9)-$E$5)*$G$5+IF(SUM($B$9:L9)-$E$5<0,($E$5-SUM($B$9:L9))*$F$5,-(SUM($B$9:L9)-$E$5)*$G$5),(SUM($B$9:M9)*$F$5)-(SUM($A$9:L9)*$F$5))
=IF(SUM($B$9:M9)>$E$5,IF(SUM($B$9:L9)<$E$5,($E$5-(SUM($B$9:M9)-M9))*$F$5+(SUM($B$9:M9)-2000)*$G$5,M9*$G$5),M9*$F$5)
=IF(SUM($B$9:M9)>$E$5,IF(SUM($B$9:L9)>$E$5,M9*$G$5,$G$5*(SUM($B$9:M9)-$E$5)+(M9-(SUM($B$9:M9)-$E$5))*$F$5),M9*$F$5)
=IF(SUM($B$9:M9)>$E$5,IF(SUM($B$9:L9)>$E$5,M9*$G$5,$G$5*(SUM($B$9:M9)-$E$5)+(M9-(SUM($B$9:M9)-$E$5))*$F$5),M9*$F$5)
=IF(SUM($B$9:M9)>$E$5,IF(SUM($B$9:L9)>$E$5,M9*$G$5,($E$5-SUM($B$9:L9))*$F$5+(SUM($B$9:M9)-$E$5)*$G$5),M9*$F$5)
=IF(SUM($B$9:M9)>$E$5,IF(SUM($B$9:L9)>$E$5,M9*$G$5,(SUM($B$9:M9)-$E$5)*$G$5+(M9-(SUM($B$9:M9)-$E$5))*$F$5),M9*$F$5)
=IF(SUM($B$9:M9)>$E$5,IF(SUM($B$9:M9)-M9<$E$5,(M9-(SUM($B$9:M9)-$E$5))*$F$5+(SUM($B$9:M9)-$E$5)*$G$5,M9*$G$5),M9*$F$5)
=IF(SUM($B$9:M9)>$E$5,IF(SUM($B$9:M9)-M9<$E$5,M9*$F$5-(SUM($B$9:M9)-$E$5)*($F$5-$G$5),$G$5*M9),$F$5*M9)
=IF(SUM($B$9:M9)>$E$5,IF(SUM($B$9:M9)-M9>=$E$5,M9*$G$5,(SUM($B$9:M9)-$E$5)*$G$5+(M9+$E$5-SUM($B$9:M9))*$F$5),M9*$F$5)
=IF(SUM($B$9:M9)>2000,IF(SUM($B$9:L9)>2000,0,M9-(SUM($B$9:M9)-2000)),M9)*1.5+(M9-IF(SUM($B$9:M9)>2000,IF(SUM($B$9:L9)>2000,0,M9-(SUM($B$9:M9)-2000)),M9))*1.25
=IF(SUM($B$9:M9)>2000,IF(SUM($B$9:L9)>2000,1.25*M9,1.5*M9-(SUM($B$9:M9)-2000)*0.25),1.5*M9)
=IF(SUM($B$9:M9)>sales_cap,sales_cap*comm_1+(SUM($B$9:M9)-sales_cap)*comm_2,SUM($B$9:M9)*comm_1)-SUM($B$10:L10)
=IF(SUM($B$9:M9)-M9>$E$5,M9*$G$5,IF(SUM($B$9:M9)<$E$5,M9*$F$5,((SUM($B$9:M9)-$E$5)*$G$5)+((M9-(SUM($B$9:M9)-$E$5))*$F$5)))
=IF(SUM($B$9:M9)-M9>$E$5,M9*$G$5,IF(SUM($B$9:M9)<$E$5,M9*$F$5,((SUM(I9:M9)-$E$5)*$G$5)+((M9-(SUM($B$9:M9)-$E$5))*$F$5)))
=IF(SUM($B$9:M9)-M9>$E$5,M9*$G$5,IF(SUM($B$9:M9)<=$E$5,M9*$F$5,((SUM($B$9:M9)-$E$5)*$G$5)+((M9-(SUM($B$9:M9)-$E$5))*$F$5)))
=IF(SUM($B$9:X$9)<=2000,M$9*$F$5,IF(SUM($B$9:X$9)>2000,IF(COLUMN()HLOOKUP(2001,$B$13:$M$14,2)+1,M$9*$G$5,0)))))
=IF(SUM($B9:L9)>$E$5,M9*$G$5,IF(SUM($B9:M9)>$E$5,(SUM($B9:M9)-$E$5)*$G$5+($E$5-SUM($B9:L9))*$F$5,M9*$F$5))
=IF(SUM($B9:L9)>$E5,M9*$G5,IF(SUM($B9:M9)<$E5,M9*$F5,($E5-SUM($B9:L9)*$F5)+(SUM($B9:M9)-$E5)*$G5))
=IF(SUM($B9:L9)>=$E5,M9*$G5,IF(SUM($B9:M9)<=$E5,M9*$F5,$G5*(SUM($B9:M9,-2000))-$F5*SUM($B9:L9,-2000)))
=IF(SUM($B9:L9)>=$E5,M9*$G5,IF(SUM($B9:M9)<=$E5,M9*$F5,$G5*(SUM($B9:M9,-2000))-$F5*SUM($B9:L9,-2000)))
=IF(SUM($B9:M$9)<$E$5,M9*$F$5,IF(SUM($B$9:L9)<$E$5,(($E$5-SUM($B$9:L9))*$F$5+(SUM($B9:M$9)-$E$5)*$G$5),M9*$G$5))
=IF(SUM($B9:M$9)<=$E$5,$F$5*M9,IF(SUM($B9:M$9)>$E$5,IF(SUM($B9:L$9)<=$E$5,IF(CODE(L9)<=57,$G$5*(SUM($B9:M$9)-$E$5)+$F$5*(M9-(SUM($B9:M$9)-$E$5)),$G$5*M9),$G$5*M9),$G$5*M9))
=IF(SUM($B9:M9) > $E$5,IF(SUM($B9:L9)>$E$5,M9*$G$5,(($E$5-SUM($B9:L9))*$F$5)+((M9-($E$5-SUM($B9:L9)))*$G$5)),M9*$F$5)
=IF(SUM($B9:M9)-$E$5<=0,M9*$F$5,IF(AND(SUM($B9:M9)-$E$5>0,SUM($B9:M9)-$E$5-M9<0),(M9-(SUM($B9:M9)-$E$5))*$F$5 + (SUM($B9:M9)-$E$5)*$G$5,M9*$G$5))
=IF(SUM($B9:M9)-$E$5<0,SUM($B9:M9)*$F$5,(SUM($B9:M9)-$E$5)*$G$5+$E$5*$F$5)-SUM($A10:L10)
=IF(SUM($B9:M9)-$E$5>M9,M9*$G$5,IF(SUM($B9:M9)<$E$5,M9*$F$5,(M$9-(SUM($B$9:M$9)-$E$5))*$F$5+(SUM($B$9:M$9)-$E$5)*$G$5))
=IF(SUM($B9:M9)/(limit)<1,M9*(commission),IF(SUM($B9:L9)/(limit)<1,((SUM($B9:M9)-(limit))*(tier2_commission))+(((limit)-(SUM($B9:L9)))*(commission)),M9*(tier2_commission)))
=IF(SUM($B9:M9)<$E$5,M9*$F$5,IF((SUM($B9:M9)-M9)>$E$5,M9*$G$5,(SUM($B9:M9)-$E$5)*$G$5+(M9-(SUM($B9:M9)-$E$5))*$F$5))
=IF(SUM($B9:M9)<$E$5,M9*$F$5,IF(SUM($B9:L9)>$E$5,M9*$G$5,((2000-SUM($B9:L9))*$F$5)+((SUM($B9:M9)-2000)*$G$5)))
=IF(SUM($B9:M9)<$E$5,M9*$F$5,IF(SUM($B9:L9)>$E$5,M9*$G$5,((SUM($B9:M9)-$E$5)*$G$5)+(($E$5-(SUM($B9:L9)))*$F$5)))
=IF(SUM($B9:M9)<$E$5,M9*$F$5,IF(SUM($B9:M9)-M9<$E$5,(SUM($B9:M9)-$E$5)*$G$5+(M9-(SUM($B9:M9)-$E$5))*$F$5,M9*$G$5))
=IF(SUM($B9:M9)<$E5,M9*$F5,IF(SUM($A9:L9)>$E5,M9*$G5,((SUM($B9:M9)-$E5)*$G5)+(($E5-SUM($A9:L9))*$F5)))
=IF(SUM($B9:M9)<($E5+0.01),SUM($B9:M9)*$F5,(SUM($B9:M9)-$E5)*$G5+($E5*$F5))-IF(L10=0,0,SUM($A10:L10))
=IF(SUM($B9:M9)<=$E$5,M9*$F$5,$F$5*$E$5+((SUM($B9:M9)-$E$5)*$G$5)-SUM($B10:L10))
=IF(SUM($B9:M9)<=$E$5,M9*$F$5,((((SUM($B9:M9)-2000)*$G$5)+($E$5*$F$5))-SUM($B10:L10)))
=IF(SUM($B9:M9)<=$E$5,M9*$F$5,IF(AND($E$5-SUM($B9:M9)<0,$E$5-SUM($B9:L9)>0),$E$5*($F$5-$G$5)-SUM($B9:L9)*$F$5+SUM($B9:M9)*$G$5,M9*$G$5))
=IF(SUM($B9:M9)<=$E$5,M9*$F$5,IF(SUM($A9:L9)>$E$5,M9*$G$5,($E$5-SUM($A9:L9))*$F$5+(SUM($B9:M9)-$E$5)*$G$5))
=IF(SUM($B9:M9)<=$E$5,M9*$F$5,IF(SUM($A9:L9)>$E$5,M9*$G$5,SUM($B9:M9,-2000)*$G$5-SUM($A9:L9,-2000)*$F$5))
=IF(SUM($B9:M9)<=$E$5,M9*$F$5,IF(SUM($B9:L9)>$E$5,(SUM($B9:M9)-SUM($B9:L9))*$G$5,((SUM($B9:M9)-$E$5)*$G$5)+(M9-(SUM($B9:M9)-$E$5))*$F$5))
=IF(SUM($B9:M9)<=$E$5,M9*$F$5,IF(SUM($B9:L9)>$E$5,(SUM($B9:M9)-SUM($B9:L9))*$G$5,((SUM($B9:M9)-$E$5)*$G$5)+(M9-(SUM($B9:M9)-$E$5))*$F$5))
=IF(SUM($B9:M9)<=$E$5,M9*$F$5,IF(SUM($B9:M9)-$E$5>M9,M9*$G$5,((SUM($B9:M9)-$E$5)*$G$5)+((M9-(SUM($B9:M9)-$E$5))*$F$5)))
=IF(SUM($B9:M9)<=$E$5,M9*$F$5,IF(SUM($B9:M9)-M9 <= $E$5,(((SUM($B9:M9)-$E$5)*$G$5)+(($E$5+M9)-SUM($B9:M9))*$F$5),M9*$G$5))
=IF(SUM($B9:M9)<=$E$5,M9*1.5,IF(M9-(SUM($B9:M9)-$E$5)<0,M9*1.25,((SUM($B9:M9)-$E$5)*1.25)+(M9-(SUM($B9:M9)-$E$5))*1.5))
=IF(SUM($B9:M9)<=$E$5,SUM($B9:M9)*$F$5,+($E$5*$F$5+(SUM($B9:M9)-$E$5)*$G$5))-SUM($A10:L10)
=IF(SUM($B9:M9)<=$E5,+M9*$F5,IF(SUM($B9:L9)>$E5,+M9*$G5,(SUM($B9:M9)-$E5)*$G5+(M9-SUM($B9:M9)+$E5)*$F5))
=IF(SUM($B9:M9)<=$E5,M9*$F5,IF(M9>$E5,$E5*$F5+(M9-$E5)*$G5,IF(($E5-SUM($B9:L9))<0,(M9)*$G5,($E5-SUM($B9:L9))*$F5+(M9-($E5-SUM($B9:L9)))*$G5)))
=IF(SUM($B9:M9)<=$E5,M9*$F5,IF(SUM($A9:L9)>$E5,M9*$G5,((SUM($B9:M9)-$E5)*$G5)+(($E5-SUM($A9:L9))*$F5)))
=IF(SUM($B9:M9)<=$E5,M9*$F5,IF(SUM($A9:L9)>=$E5,M9*$G5,MIN(M9,$E5-SUM($A9:L9))*$F5+MAX(SUM($B9:M9)-$E5,0)*$G5))
=IF(SUM($B9:M9)<=$E5,SUM($B9:M9)*$F5-SUM($B10:L10),(SUM($B9:M9)-$E5)*$G5+$E5*$F5-SUM($B10:L10))
=IF(SUM($B9:M9)<=($E5),M9*$F5,IF(SUM($B9:M9)-M9>$E5,M9*$G5,((SUM($B9:M9)-$E5)*$G5+($E5-SUM($B9:M9)+M9)*$F5)))
=IF(SUM($B9:M9)<=2000,M9*1.5,MIN((SUM($B9:M9)-2000),M9)*1.25+MAX((M9-(SUM($B9:M9)-2000)),0)*1.5)
=IF(SUM($B9:M9)<=saleslimit,M9*highcomm,IF((SUM($B9:M9)-saleslimit)>L9,M9*lowcomm,(SUM($B9:M9)-saleslimit)*lowcomm+(M9-(SUM($B9:M9)-saleslimit))*highcomm))
=IF(SUM($B9:M9)<2000,M9*1.5,IF(AND(SUM($B9:M9)>2000,SUM($B9:L9)<2000),1.5*(2000-SUM($B9:M9)+M9)+1.25*(SUM($B9:M9)-2000),IF(AND(SUM($B9:M9)>2000,SUM($B9:L9)>2000),M9*1.25)))
=IF(SUM($B9:M9)=IF(SUM($B9:M9)=IF(SUM($B9:M9)=IF(SUM($B9:M9)>$E$5,((SUM($B9:M9)-$E$5)*$G$5)+($E$5*$F$5),(SUM($B9:M9)*$F$5))-IF(L10="Commission",0,SUM($A10:L10))
=IF(SUM($B9:M9)>$E$5,(SUM($B9:M9)-$E$5)*$G$5+$E$5*$F$5,SUM($B9:M9)*$F$5)-SUM($B10:L10)
=IF(SUM($B9:M9)>$E$5,IF((SUM($B9:M9)-$E$5)>M9,($G$5*M9),((M9-(SUM($B9:M9)-$E$5))*$F$5)+((M9-(M9-(SUM($B9:M9)-$E$5)))*$G$5)),(1.5*M9))
=IF(SUM($B9:M9)>$E$5,IF(SUM($B9:L9)>$E$5,$G$5*M9,IF(SUM($B$9:$M$9)>$E$5,SUM($B$9:$M$9)-$E$5,0)*$G$5+IF(SUM($B$9:$M$9)>$E$5,$E$5,SUM($B$9:$M$9))*$F$5-SUM($B10:L10)),$F$5*M9)
=IF(SUM($B9:M9)>$E$5,IF(SUM($B9:L9)>2000,M9*$G$5,(SUM($B9:M9)-$E$5)*$G$5+(M9-(SUM($B9:M9)-$E$5))*$F$5),$F$5*M9)
=IF(SUM($B9:M9)>$E5,IF(($E5-(SUM($B9:L9)))<0,M9*$G5,(($E5-(SUM($B9:L9)))*$F5)+((M9-($E5-(SUM($B9:L9))))*$G5)),($F5*M9))
=IF(SUM($B9:M9)>$E5,IF((SUM($B9:M9)-$E5)>M9,(M9*$G5),((SUM($B9:M9)-$E5)*$G5)+((M9-(SUM($B9:M9)-$E5))*$F5)),M9*$F5)
=IF(SUM($B9:M9)>$E5,IF(SUM($B9:L9)>$E5,M9*$G5,IF(M8="JAN",$E5*$F5+(SUM($B9:M9)-$E5)*$G5,($E5-SUM($B9:L9))*$F5+(SUM($B9:M9)-$E5)*$G5)),M9*$F5)
=IF(SUM($B9:M9)>$E5,IF(SUM($B9:M9)-M9>$E5,M9*$G5,(SUM($B9:M9)-$E5)*$G5+(M9-SUM($B9:M9)+$E5)*$F5),M9*$F5)
=IF(SUM(B9,C9,D9,E9,F9,G9,H9,I9,J9,K9,L9,M9)<2001,M9*1.5,IF(SUM(B9,C9,D9,E9,F9,G9,H9,I9,J9,K9,L9)>1999,M9*1.25,(SUM(B9,C9,D9,E9,F9,G9,H9,I9,J9,K9,L9,M9)-2000)*1.25+(M9-(SUM(B9,C9,D9,E9,F9,G9,H9,I9,J9,K9,L9,M9)-2000))*1.5))
=IF(SUM(D9:L9)>=$E$5,M9*$G$5,IF($E$5-SUM(D9:L9)>=M9,M9*$F$5,($E$5-SUM(D9:L9))*$F$5+(M9-($E$5-SUM(D9:L9)))*$G$5))
=IF(SUM(dec)-limit>M9,Com2*M9,IF(SUM(dec)=IF(SUM(Jan_to_Dec)E5,Dec*G5,SUM((SUM(Jan_to_Dec)-E5)*G5,(Dec-(SUM(Jan_to_Dec)-E5))*F5)))
=IF(SUM(Jd) < E5,M9 * F5,IF(SUM(Jn) < E5,(SUM(Jd) - E5) * G5 + (E5 - SUM(Jn)) * F5,M9 * G5))
=IF(SUM(Nov)+M9<=$E$5,M9*$F$5,IF(SUM(Nov)>$E$5,M9*$G$5,(SUM(Dec)-SUM(Nov)-(SUM(Dec)-$E$5))*$F$5+((SUM(Dec)-$E$5)*$G$5)))
=IF(SUM(OFFSET($B$9,0,0,1,COLUMNS($B$9:M9)))<$E$5,M9*$F$5,IF(SUM(OFFSET($B$9,0,0,1,COLUMNS($B$9:L9)))<$E$5,($E$5-SUM(OFFSET($B$9,0,0,1,COLUMNS($B$9:L9))))*$F$5+(M9-($E$5-SUM(OFFSET($B$9,0,0,1,COLUMNS($B$9:L9)))))*$G$5,M9*$G$5))
=IF(SUM(OFFSET($B$9:$M$9,0,0,1,COLUMN()-1))>$E$5,IF((SUM(OFFSET($B$9:$M$9,0,0,1,COLUMN()-1))-$E$5)>M9,M9*$G$5,((SUM(OFFSET($B$9:$M$9,0,0,1,COLUMN()-1))-$E$5)*$G$5)+((M9-(SUM(OFFSET($B$9:$M$9,0,0,1,COLUMN()-1))-$E$5))*$F$5)),M9*$F$5)
=IF(SUM(OFFSET($B9,0,0,,M11))<=2000,M9*$F$5,IF(SUM(OFFSET($B9,0,0,,M11-1))>2000,M9*$G$5,(((2000-SUM($A9:L9))*$F$5+(SUM(OFFSET($B9,0,0,,M11))-2000)*$G$5))))
=IF(SUM(OFFSET(M9,0,-(MONTH(M8&"-0")-1),1,MONTH(M8&"-0")))<=$E$5,M9*$F$5,IF(SUM(OFFSET(M9,0,-(MONTH(M8&"-0")-1),1,MONTH(M8&"-0")))-M9>$E$5+M9,M9*$G$5,((SUM(OFFSET(M9,0,-(MONTH(M8&"-0")-1),1,MONTH(M8&"-0")))-$E$5)*$G$5+(M9-(SUM(OFFSET(M9,0,-(MONTH(M8&"-0")-1),1,MONTH(M8&"-0")))-$E$5))*$F$5)))
=IF(SUM(OFFSET(M9,0,-(MONTH(M8)-1),1,MONTH(M8)))<=$E$5,M9*$F$5,IF(SUM(OFFSET(M9,0,-(MONTH(M8)-1),1,MONTH(M8)))-M9>$E$5+M9,M9*$G$5,((SUM(OFFSET(M9,0,-(MONTH(M8)-1),1,MONTH(M8)))-$E$5)*$G$5+(M9-(SUM(OFFSET(M9,0,-(MONTH(M8)-1),1,MONTH(M8)))-$E$5))*$F$5)))
=IF(TDsales=IF(Total_End <= Threshold, Below_Threshold_Commission*Sales, IF(Total_Start >= Threshold, Above_Threshold_Commission*Sales, Below_Threshold_Commission*(Threshold - Total_Start) + Above_Threshold_Commission*(Total_End - Threshold)))"
=IF(YTDSales_Below_or_Equal_Threshold,M$9*$F$5,IF(YTDSales_Exceed_Threshold_ThisMth,(($E$5-(SUM($B$9:M$9)-M$9))*$F$5)+((M$9-($E$5-(SUM($B$9:M$9)-M$9)))*$G$5),M$9*$G$5))
=M$9*$F$5-($F$5-$G$5)*MIN(MAX(0,SUM($B$9:M$9)-$E$5),M$9)
=M11*commission2+(M9-M11)*commission1
=M12*$F$5+M13*$G$5
=M12*comm1+M13*comm2
=M13*$F$5+M14*$F$5+M15*$G$5
=M13*$F$5+M14*$G$5
=M13*1.25+M14*1.5+M15*1.5
=M13-L13
=M13-L13
=M14+M15
=M14-L14
=M14-L14
=M14-SUM($A14,L14)
=M15*$F$5+M16*$G$5
=M15*H_Comm+M16*L_Comm
=M15*H_Comm+M16*L_Comm
=M18*$F$5+M19*$G$5
=M18*$F$5+M19*$G$5
=M18+M19
=M19
=M23
=M9*$F$5-($F$5-$G$5)*MIN(MAX(SUM($B$9:M9)-$E$5,0),(SUM($B$9:M9)>$E$5)*M9)
=M9*$F$5-IF(SUM($B9:M9)>$E$5,MIN(SUM($B9:M9)-$E$5,M9)*($F$5-$G$5),0)
=M9*$F$5-MIN(MAX(SUM($B$9:M9)-$E$5,0),M9)*($F$5-$G$5)
=M9*$F5 + (M9*(SUM($B9:M9)>$E5) - ($E5<=SUM($B9:M9))*($E5>SUM($B9:M9)-M9)*(M9-SUM($B9:M9)+$E5))*($G5-$F5)
=M9*$G$5+(SUM($A9:L9)<=$E$5)*MIN(M9,$E$5-SUM($A9:L9))*($F$5-$G$5)
=M9*$G$5+MIN(MAX($E$5-SUM($B9:L9),0),M9)*($F$5-$G$5)
=M9*($F$5-(($F$5-$G$5)*(SUM($B$9:M9)>$E$5)))+(SUM($B$9:M9)>$E$5)*((SUM($B$9:M9)-M9)<$E$5)*($F$5-$G$5)*($E$5-(SUM($B$9:M9)-M9))
=M9*($G$5)+M14*($F$5-$G$5)
=M9*IF(SUM($B9:M9)>$E$5,$G$5,$F$5)+(AND(SUM($B9:M9)>$E$5,SUM($B9:M9)-M9<$E$5)*($E$5-(SUM($B9:M9)-M9))*($F$5-$G$5))
=MAX((M13-MAX(IF(ISNUMBER(L13)=FALSE,0,L13),$E$5)),0)*$G$5+MIN(MAX(($E$5-IF(ISNUMBER(L13)=FALSE,0,L13)),0),M9)*$F$5
=MAX(0,M9-MAX(0,SUM($B9:M$9)-$E$5))*($F$5-$G$5)+M9*$G$5
=MAX(CHOOSE(SIGN(SUM($B$9:M9)-$E$5)+2,M9*$F$5,M9*$F$5,IF(SUM($B$9:M9)-M9<$E$5,(($E$5-(SUM($B$9:M9)-M9))*$F$5)+(SUM($B$9:M9)-$E$5)*$G$5,M9*$G$5)),0)
=MAX(IF(SUM($A$9:M9)>$E$5,$E$5-SUM($A$9:L9),M9),0)*$F$5+(M9-MAX(IF(SUM($A$9:M9)>$E$5,$E$5-SUM($A$9:L9),M9),0))*$G$5
=MAX(MIN(SUM($B9:M9),$E$5)-SUM($B9:M9)+M9,0)*$F$5+(M9-MAX(MIN(SUM($B9:M9),$E$5)-SUM($B9:M9)+M9,0))*$G$5
=MIN($E$5,SUM($B9:M9))*$F$5+MAX(0,SUM($B9:M9)-$E$5)*$G$5-SUM($A10:L10)
=MIN(2000,SUM($B9:M9))*1.5+MAX(0,SUM($B9:M9)-2000)*1.25-SUM($B10:L10)
=MIN(CumulativeSales,Threshold)*BaseRate+MAX(0,(CumulativeSales-Threshold)*OverbaseRate)-AlreadyEarned
=MIN(M9,M10)*$F$5+(M9-MIN(M9,M10))*$G$5
=MIN(SUM($A9:M9),$E$5)*$F$5+MAX(0,SUM($A9:M9)-$E$5)*$G$5-SUM($A10:L10)
=MIN(SUM($B$9:M9),$E$5)*$F$5 + MAX(SUM($B$9:M9)-$E$5,0)*$G$5 - SUM($B$10:L10)
=MIN(SUM($B$9:M9),$E$5)*$F$5 + MAX(SUM($B$9:M9)-$E$5,0)*$G$5-SUM($B$10:L10)
=MIN(SUM($B$9:M9),$E$5)*$F$5+MAX(SUM($B$9:M9)-$E$5,0)*$G$5-IF(M8<>$B$8,SUM($B$10:L10),0)
=MIN(SUM($B$9:M9),$E$5)*$F$5+MAX(SUM($B$9:M9)-$E$5,0)*$G$5-IF(M8<>$B$8,SUM($B$10:L10),0)
=MIN(SUM($B$9:M9),$E$5)*$F$5+MAX(SUM($B$9:M9)-$E$5,0)*$G$5-SUM($A$10:L10)
=MIN(SUM($B9:M9),$E$5)*$F$5-SUM($B10:L10)+IF(SUM($B9:M9)>$E$5,(SUM($B9:M9)-2000)*$G$5,0)
=ROUND((M14*$F$5)+(M15*$G$5),2)
=ROUND((MIN(M9,MAX(SUM($B9:M9)-$E5,0))*$G5)+(MIN(M9,MAX($E5-SUM($A9:L9),0))*$F5),2)
=ROUND(IF(AND(M10<2000,M102000,M10>M9+2000),$G$5*(M11+M12),$G$5*M11+$F$5*M12)),1)
=ROUND(IF(M12>$E$5,(M12-$E$5)*$G$5+$E$5*$F$5,M12*$F$5),2)-ROUND(IF(L12>$E$5,(L12-$E$5)*$G$5+$E$5*$F$5,L12*$F$5),2)
=ROUND(IF(M16+M17=0,(M15*$G$5+(M9-M15)*$F$5),M9*(M16*$F$5+M17*$G$5)), 1)
=ROUND(IF(SUM($B$9:M9)<=2000,M9*1.5,IF(SUM($B$9:L9)<2000,((2000-SUM($B$9:L9))*1.5)+((SUM($B$9:M9)-2000)*1.25),M9*1.25)),1)
=ROUND(MIN(Units,SUM($A9:M9))*HiCom+MAX(0,SUM($A9:M9)-Units)*LoCom-SUM($A10:L10),1)
=SUM($B$9:M9)*1.5-MAX(SUM($B$9:M9)-2000,0)*(1.5-1.25)-IF(M8="Jan",0,SUM($B$10:L10))
=SUM($B9:M9)*1.5-(IF(SUM($B9:M9)-2000<0,0,SUM($B9:M9)-2000)*0.25)-SUM($B10:L10)
=SUM(M15:M16)
=SUM(M15:M17)
=SUM(M15:M17)
=SUM(M18:M19)
=SUM(M19:M20)
=SUMPRODUCT((SUM($B9:M9)>{0;2000})*(SUM($B9:M9)-{0;2000})*{1.5;-0.25})-SUM($B10:L10)
=Tier_1_Comm+Tier_2_Comm
1225

I asked the readers to nominate formulas that looked interesting. We have to give away another power bundle to the best formula. The nominations are shown below.

Updates from May 10, 2007:

• Several people wrote in to say, "wait - my formula was right, but I had inserted extra rows, and so you were looking in the wrong place." Yes - I found another 80 correct formulas that fall into this category. The list above includes those formulas.
• A few wrote in to complain about my nomination of Laurie's formula. Cell A4 of the contest spreadsheet guides you to use the cells E4:G4 as parameters in the formula. Since Laurie's formula hard-coded these values instead of using the cells, some argued that it should not be allowed. I'll overrule this objection - hard coding the values in the formula lead to a really interesting formula. If you think I am bending the rules, then feel free to vote for another formula.
• Some complained that Laurie's formula did not work. I've uploaded the workbooks of the nominated formulas. Check it out. It works.
• Some noted that without seeing the rest of the workbook to learn the range names, etc., it is fairly impossible to evaluate some of the formulas. Good point. I am not ready to upload 500 workbooks and link them all here.
• Some sent in their favorite formulas, which are nominated below.

Updates from May 14, 2007:

• A great discussion has erupted over the January calculation. I had focused on having a correct May calculation since that is the month with a blending of both commission rates. Several people have noted that if you would encounter the situation where you would go over the 2500 in January, many of the formulas would fail. This is an interesting twist; many of us who have worked in accounting would argue that the whole point of a tiered commission plan is to hit one of the tiers half way through the year. If a VP of Sales designed the commission plan so that the tier was hit in the first month, it would be a bad plan. However, in an excellent note, Jeremy B pointed out that if your January commission was supposed to be $3000 and the formula calculates $2500 and it is your commission, then you would certainly notice. Daniel Ferry also noted that the IF formulas will completely fail if January is greater than 2000.

Updates from May 15, 2007:

• Which is better - =MIN(MAX or =MEDIAN? I had a discussion with Daniel Ferry where I noted that the solution with a single function - MEDIAN would seem to be better than a solution that used two functions - MIN and MAX. Daniel replied: "It's really the same thing. What the Min(Max construct does is find the middle value of three things: 0, ThisMonth'sSales and CumulativeSalesMinusSalesThreshold. If anything, the Median function states this more clearly than Min(Max. However, my Min(Max construct is slightly faster, as it is simpler for Excel to find extremes than the median. If you paste Brad's row of formulas all the way down to row 65536 and time how long it takes to calculate, then close Excel to clear the memory, open it again and do the same with mine, you'll see that mine is about 20% faster".

• Daniel also wrote comments about the other approaches, which I will include below.

  1. To get started, I will nominate this formula by Laurie Fullerton:

     =SUMPRODUCT((SUM($B9:M9)>{0;2000})*(SUM($B9:M9)-{0;2000})*{1.5;-0.25})-SUM($B10:L10) Why: only three people tried SumProduct and of the three, this was the shortest entry. For those of you who say that the formula doesn't work, check out the entry

     Daniel Ferry questioned how Laurie's formula would work in January. The final term of -SUM($B10:L10) would transform to subtract the January commission and lead to a commission of zero. Laurie did actually have a different formula for January to handle this. Daniel Ferry proposed changing the $B10 in the final term to $A10 to allow it to work in all of the months.

     Another note in support of Laurie arrived; "originally it seemed too complicated for any new spreadsheet user to understand, but it is so innovative, if I would have been able to come up with it, I would have used it myself."

  2. Damien Scott wrote in and nominated this formula:

     =$F$5*F9-($F$5-$G$5)*MIN(F9,MAX(0,SUM($B9:F9)-$E$5))

     Damien noted the formula was simple and would be fast to calculate.

     That formula is from Daniel Ferry (I presume this is not the general manager of the Cleveland Cavaliers...). Daniel notes, "I optimized my entry for brevity without the use of named formulas. In terms of characters used, I believe my entry to be the most efficient possible. I've used no "IF" statements and no supporting rows, cols, or cells."

     This is a great formula. Download the entry. Select F10 and, if you have Excel 2003, use Tools - Formula Auditing - Evaluate formula to watch the formula evaluation. Daniel wrote in with these thoughts: "I believe that avoiding IF statements when a brief and clear alternative is available is desirable, for two reasons. Often an unforeseen and undesirable condition will be setup with the careless use of IF (as Matt's formula for January demonstrates). Secondly, when you do this for a living often you run across situations where you need to use nested IF statements and since Excel is limited to seven, wasting them on situations where better alternatives exist is not good".

  3. Michael Girvin nominated this formula:

     =M$9*$F$5-($F$5-$G$5)*MIN(MAX(0,SUM($B$9:M$9)-$E$5),M$9)

     Michael writes: It is amongst the shortest of all the formulas, and more than anything it clearly demonstrates that the author sees this problem through the kaleidoscope/glasses of "differences". If you run formula evaluator you can see how the author saw this whole problem as one of differences. It is ingenious and really unique amongst all the formulas in its "thought process." Total Rad!!

     That formula is from Daan Sprünken. Download the entry. Daan wrote in to say that his formula is basically the same as Daniel Ferry's, although Daan did not take out as many $ to make the formula shorter. Peter from The Netherlands wrote in to support Daan's formula; "only 3 functions and 7 ranges - the most elegant".

  4. K Mohan nominates this formula:

     =IF(SUM($B$9:M9)<$E$5,M9*$F$5,IF(SUM($B$9:L9)<$E$5,($E$5-SUM($B$9:L9))*$F$5+ (SUM($B$9:M9)-$E$5)*$G$5,M9*$G$5))

     Mohan writes: A simple solution with the power of IF.

     This formula was sent in by both Roland Drahos and Matt Basler. About 20 people used similar approaches. Matt's entry came in 40 days before Roland's so Matt would qualify for the nomination. Download the entry Jeremy B pointed out that the formula fails if you sell exactly 2000 units in January. Jeremy proposed amending by changing the initial < to <=: