|
IF/AND Statements in Comp Calculator
Hello,
Almost done with this but I'm stumped on the last remaining formula. The way this compensation plan will work is if reps are between 0-25% growth they will get paid $100 per point, 26-50% $150 per point, 51-75% $200 per point. Growth is calculated monthly vs. a monthly #. My problem is that if the rep is at 40% they get the first 25% at $100 and the next 15% at $150. My formula is an either or thing. The following are my formulas for 0-25%, 26-50%,51-75% respectively: =IF(AND(D9/C90,D9/C9<1.25),((D9/C9-1))*100)/(B9/52) =IF(AND(D9/C91.26,D9/C9<1.5),((D9/C9-1))*150)/(B9/52) =IF(AND(D9/C91.51,D9/C9<1.75),((D9/C9-1))*200)/(B9/52) Any suggestions? Thanks, Bob |
IF/AND Statements in Comp Calculator
Bob:
See if this helps Build this table in A1:B4 Increase Incremental Rate 0% 100 25% 50 50% 50 Then, with values in B9, C9, and D9 E9: =SUMPRODUCT(((D9-C9)/C9=$A$2:$A$4)*(((D9-C9)/C9-A2:A4)*100)*(B2:B4))/(B9/52) Note: in case text wrap impacts the display, there are no spaces in that formula. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "BobVA" wrote: Hello, Almost done with this but I'm stumped on the last remaining formula. The way this compensation plan will work is if reps are between 0-25% growth they will get paid $100 per point, 26-50% $150 per point, 51-75% $200 per point. Growth is calculated monthly vs. a monthly #. My problem is that if the rep is at 40% they get the first 25% at $100 and the next 15% at $150. My formula is an either or thing. The following are my formulas for 0-25%, 26-50%,51-75% respectively: =IF(AND(D9/C90,D9/C9<1.25),((D9/C9-1))*100)/(B9/52) =IF(AND(D9/C91.26,D9/C9<1.5),((D9/C9-1))*150)/(B9/52) =IF(AND(D9/C91.51,D9/C9<1.75),((D9/C9-1))*200)/(B9/52) Any suggestions? Thanks, Bob |
IF/AND Statements in Comp Calculator
Hi Bob,
You could also try: =(D9/C90)*(D9/C9)*100+(D9/C91.25)*(D9/C9)*50+(D9/C91.5)*(D9/C9)*50 Cheers "BobVA" wrote in message ... Hello, Almost done with this but I'm stumped on the last remaining formula. The way this compensation plan will work is if reps are between 0-25% growth they will get paid $100 per point, 26-50% $150 per point, 51-75% $200 per point. Growth is calculated monthly vs. a monthly #. My problem is that if the rep is at 40% they get the first 25% at $100 and the next 15% at $150. My formula is an either or thing. The following are my formulas for 0-25%, 26-50%,51-75% respectively: =IF(AND(D9/C90,D9/C9<1.25),((D9/C9-1))*100)/(B9/52) =IF(AND(D9/C91.26,D9/C9<1.5),((D9/C9-1))*150)/(B9/52) =IF(AND(D9/C91.51,D9/C9<1.75),((D9/C9-1))*200)/(B9/52) Any suggestions? Thanks, Bob |
IF/AND Statements in Comp Calculator
"Ron Coderre" wrote: Bob: See if this helps Build this table in A1:B4 Increase Incremental Rate 0% 100 25% 50 50% 50 Then, with values in B9, C9, and D9 E9: =SUMPRODUCT(((D9-C9)/C9=$A$2:$A$4)*(((D9-C9)/C9-A2:A4)*100)*(B2:B4))/(B9/52) Note: in case text wrap impacts the display, there are no spaces in that formula. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "BobVA" wrote: Hello, Almost done with this but I'm stumped on the last remaining formula. The way this compensation plan will work is if reps are between 0-25% growth they will get paid $100 per point, 26-50% $150 per point, 51-75% $200 per point. Growth is calculated monthly vs. a monthly #. My problem is that if the rep is at 40% they get the first 25% at $100 and the next 15% at $150. My formula is an either or thing. The following are my formulas for 0-25%, 26-50%,51-75% respectively: =IF(AND(D9/C90,D9/C9<1.25),((D9/C9-1))*100)/(B9/52) =IF(AND(D9/C91.26,D9/C9<1.5),((D9/C9-1))*150)/(B9/52) =IF(AND(D9/C91.51,D9/C9<1.75),((D9/C9-1))*200)/(B9/52) Any suggestions? Thanks, Bob Thanks for trying. The formula generates an answer that doesn't make sense. The numbers I'm using gives the rep 52% growth in a month. The formula generates 2.94. |
IF/AND Statements in Comp Calculator
"macropod" wrote: Hi Bob, You could also try: =(D9/C90)*(D9/C9)*100+(D9/C91.25)*(D9/C9)*50+(D9/C91.5)*(D9/C9)*50 Cheers "BobVA" wrote in message ... Hello, Almost done with this but I'm stumped on the last remaining formula. The way this compensation plan will work is if reps are between 0-25% growth they will get paid $100 per point, 26-50% $150 per point, 51-75% $200 per point. Growth is calculated monthly vs. a monthly #. My problem is that if the rep is at 40% they get the first 25% at $100 and the next 15% at $150. My formula is an either or thing. The following are my formulas for 0-25%, 26-50%,51-75% respectively: =IF(AND(D9/C90,D9/C9<1.25),((D9/C9-1))*100)/(B9/52) =IF(AND(D9/C91.26,D9/C9<1.5),((D9/C9-1))*150)/(B9/52) =IF(AND(D9/C91.51,D9/C9<1.75),((D9/C9-1))*200)/(B9/52) Any suggestions? Thanks, Bob I changed the (2) 50s to 150 and 200 and the numbers are getting really close. One thing though the plan is capped at 75% growth. By the looks of this that is not accounted for, is it? Thanks for your suggestion. Almost there. |
IF/AND Statements in Comp Calculator
I understand your point...
I tried to emulate your posted formula, but I didn't have much to go on. I'm guessing that C9 is some kind of volume? hundreds? millions? units? Is C9 the previous amount and D9 the new amount? What does B9 represent? *********** Regards, Ron XL2002, WinXP "BobVA" wrote: "Ron Coderre" wrote: Bob: See if this helps Build this table in A1:B4 Increase Incremental Rate 0% 100 25% 50 50% 50 Then, with values in B9, C9, and D9 E9: =SUMPRODUCT(((D9-C9)/C9=$A$2:$A$4)*(((D9-C9)/C9-A2:A4)*100)*(B2:B4))/(B9/52) Note: in case text wrap impacts the display, there are no spaces in that formula. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "BobVA" wrote: Hello, Almost done with this but I'm stumped on the last remaining formula. The way this compensation plan will work is if reps are between 0-25% growth they will get paid $100 per point, 26-50% $150 per point, 51-75% $200 per point. Growth is calculated monthly vs. a monthly #. My problem is that if the rep is at 40% they get the first 25% at $100 and the next 15% at $150. My formula is an either or thing. The following are my formulas for 0-25%, 26-50%,51-75% respectively: =IF(AND(D9/C90,D9/C9<1.25),((D9/C9-1))*100)/(B9/52) =IF(AND(D9/C91.26,D9/C9<1.5),((D9/C9-1))*150)/(B9/52) =IF(AND(D9/C91.51,D9/C9<1.75),((D9/C9-1))*200)/(B9/52) Any suggestions? Thanks, Bob Thanks for trying. The formula generates an answer that doesn't make sense. The numbers I'm using gives the rep 52% growth in a month. The formula generates 2.94. |
IF/AND Statements in Comp Calculator
Hi Bob,
Your original post indicated you wanted the payees to get $250 per point for 1-25%, an additional $50 per point for 26-50% and an additional $50 per point for 51-75%. So, why would you change the two 50s to 150 and 200? That would result in the payees getting $250 per point for 1-25%, an additional $150 per point for 26-50% (i.e. $250 per point) and an additional $200 per point for 51-75% (i.e. $450 per point). As for capping, try: =MIN((D9/C90)*(D9/C9)*100+(D9/C91.25)*(D9/C9)*50+(D9/C91.5)*(D9/C9)*50,1. 75*200) or =MIN((D9/C90)*(D9/C9)*100+(D9/C91.25)*(D9/C9)*50+(D9/C91.5)*(D9/C9)*50,35 0) Cheers "BobVA" wrote in message ... "macropod" wrote: Hi Bob, You could also try: =(D9/C90)*(D9/C9)*100+(D9/C91.25)*(D9/C9)*50+(D9/C91.5)*(D9/C9)*50 Cheers "BobVA" wrote in message ... Hello, Almost done with this but I'm stumped on the last remaining formula. The way this compensation plan will work is if reps are between 0-25% growth they will get paid $100 per point, 26-50% $150 per point, 51-75% $200 per point. Growth is calculated monthly vs. a monthly #. My problem is that if the rep is at 40% they get the first 25% at $100 and the next 15% at $150. My formula is an either or thing. The following are my formulas for 0-25%, 26-50%,51-75% respectively: =IF(AND(D9/C90,D9/C9<1.25),((D9/C9-1))*100)/(B9/52) =IF(AND(D9/C91.26,D9/C9<1.5),((D9/C9-1))*150)/(B9/52) =IF(AND(D9/C91.51,D9/C9<1.75),((D9/C9-1))*200)/(B9/52) Any suggestions? Thanks, Bob I changed the (2) 50s to 150 and 200 and the numbers are getting really close. One thing though the plan is capped at 75% growth. By the looks of this that is not accounted for, is it? Thanks for your suggestion. Almost there. |
IF/AND Statements in Comp Calculator
"Ron Coderre" wrote: I understand your point... I tried to emulate your posted formula, but I didn't have much to go on. I'm guessing that C9 is some kind of volume? hundreds? millions? units? Is C9 the previous amount and D9 the new amount? What does B9 represent? *********** Regards, Ron XL2002, WinXP "BobVA" wrote: "Ron Coderre" wrote: Bob: See if this helps Build this table in A1:B4 Increase Incremental Rate 0% 100 25% 50 50% 50 Then, with values in B9, C9, and D9 E9: =SUMPRODUCT(((D9-C9)/C9=$A$2:$A$4)*(((D9-C9)/C9-A2:A4)*100)*(B2:B4))/(B9/52) Note: in case text wrap impacts the display, there are no spaces in that formula. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "BobVA" wrote: Hello, Almost done with this but I'm stumped on the last remaining formula. The way this compensation plan will work is if reps are between 0-25% growth they will get paid $100 per point, 26-50% $150 per point, 51-75% $200 per point. Growth is calculated monthly vs. a monthly #. My problem is that if the rep is at 40% they get the first 25% at $100 and the next 15% at $150. My formula is an either or thing. The following are my formulas for 0-25%, 26-50%,51-75% respectively: =IF(AND(D9/C90,D9/C9<1.25),((D9/C9-1))*100)/(B9/52) =IF(AND(D9/C91.26,D9/C9<1.5),((D9/C9-1))*150)/(B9/52) =IF(AND(D9/C91.51,D9/C9<1.75),((D9/C9-1))*200)/(B9/52) Any suggestions? Thanks, Bob Thanks for trying. The formula generates an answer that doesn't make sense. The numbers I'm using gives the rep 52% growth in a month. The formula generates 2.94. Hello, D9 represents actual sales that month, C9 is Quota for the month. B9 represents the number of weeks in that given month. Not all our months have the same number of weeks. THanks again. |
IF/AND Statements in Comp Calculator
OK...Let's see if I'm following here.
You want to calculate the percent increase over quota. There is a stratified bonus system associated with that increase. The first 25% of increase earns $100 per percentage point. The second 25% of increase earns $150 per percentage point. The third 25% of increase earns $200 per percentage point. The total bonus earned is prorated to the percentage of a year for the period covered. A 4-week month pays 4/52 of the bonus. If those statements are true, with the table of data I posted earlier, try this: Whe B9 = Weeks in the period C9 = Sales Quota D9 = Actual Sales Bonus for this period is: E9: =SUMPRODUCT(((D9-C9)/C9=$A$1:$A$3)*(((D9-C9)/C9-A1:A3)*100)*(B1:B3))*B9/52 Is that any closer to what you're looking for? *********** Regards, Ron XL2002, WinXP "BobVA" wrote: "Ron Coderre" wrote: I understand your point... I tried to emulate your posted formula, but I didn't have much to go on. I'm guessing that C9 is some kind of volume? hundreds? millions? units? Is C9 the previous amount and D9 the new amount? What does B9 represent? *********** Regards, Ron XL2002, WinXP "BobVA" wrote: "Ron Coderre" wrote: Bob: See if this helps Build this table in A1:B4 Increase Incremental Rate 0% 100 25% 50 50% 50 Then, with values in B9, C9, and D9 E9: =SUMPRODUCT(((D9-C9)/C9=$A$2:$A$4)*(((D9-C9)/C9-A2:A4)*100)*(B2:B4))/(B9/52) Note: in case text wrap impacts the display, there are no spaces in that formula. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "BobVA" wrote: Hello, Almost done with this but I'm stumped on the last remaining formula. The way this compensation plan will work is if reps are between 0-25% growth they will get paid $100 per point, 26-50% $150 per point, 51-75% $200 per point. Growth is calculated monthly vs. a monthly #. My problem is that if the rep is at 40% they get the first 25% at $100 and the next 15% at $150. My formula is an either or thing. The following are my formulas for 0-25%, 26-50%,51-75% respectively: =IF(AND(D9/C90,D9/C9<1.25),((D9/C9-1))*100)/(B9/52) =IF(AND(D9/C91.26,D9/C9<1.5),((D9/C9-1))*150)/(B9/52) =IF(AND(D9/C91.51,D9/C9<1.75),((D9/C9-1))*200)/(B9/52) Any suggestions? Thanks, Bob Thanks for trying. The formula generates an answer that doesn't make sense. The numbers I'm using gives the rep 52% growth in a month. The formula generates 2.94. Hello, D9 represents actual sales that month, C9 is Quota for the month. B9 represents the number of weeks in that given month. Not all our months have the same number of weeks. THanks again. |
IF/AND Statements in Comp Calculator
"macropod" wrote: Hi Bob, Your original post indicated you wanted the payees to get $250 per point for 1-25%, an additional $50 per point for 26-50% and an additional $50 per point for 51-75%. So, why would you change the two 50s to 150 and 200? That would result in the payees getting $250 per point for 1-25%, an additional $150 per point for 26-50% (i.e. $250 per point) and an additional $200 per point for 51-75% (i.e. $450 per point). As for capping, try: =MIN((D9/C90)*(D9/C9)*100+(D9/C91.25)*(D9/C9)*50+(D9/C91.5)*(D9/C9)*50,1. 75*200) or =MIN((D9/C90)*(D9/C9)*100+(D9/C91.25)*(D9/C9)*50+(D9/C91.5)*(D9/C9)*50,35 0) Cheers "BobVA" wrote in message ... "macropod" wrote: Hi Bob, You could also try: =(D9/C90)*(D9/C9)*100+(D9/C91.25)*(D9/C9)*50+(D9/C91.5)*(D9/C9)*50 Cheers "BobVA" wrote in message ... Hello, Almost done with this but I'm stumped on the last remaining formula. The way this compensation plan will work is if reps are between 0-25% growth they will get paid $100 per point, 26-50% $150 per point, 51-75% $200 per point. Growth is calculated monthly vs. a monthly #. My problem is that if the rep is at 40% they get the first 25% at $100 and the next 15% at $150. My formula is an either or thing. The following are my formulas for 0-25%, 26-50%,51-75% respectively: =IF(AND(D9/C90,D9/C9<1.25),((D9/C9-1))*100)/(B9/52) =IF(AND(D9/C91.26,D9/C9<1.5),((D9/C9-1))*150)/(B9/52) =IF(AND(D9/C91.51,D9/C9<1.75),((D9/C9-1))*200)/(B9/52) Any suggestions? Thanks, Bob I changed the (2) 50s to 150 and 200 and the numbers are getting really close. One thing though the plan is capped at 75% growth. By the looks of this that is not accounted for, is it? Thanks for your suggestion. Almost there. Hello again. Before I go on I appreciate the help. The following is my original post: Almost done with this but I'm stumped on the last remaining formula. The way this compensation plan will work is if reps are between 0-25% growth they will get paid $100 per point, 26-50% $150 per point, 51-75% $200 per point. Growth is calculated monthly vs. a monthly #. My problem is that if the rep is at 40% they get the first 25% at $100 and the next 15% at $150. My formula is an either or thing. The following are my formulas for 0-25%, 26-50%,51-75% respectively: =IF(AND(D9/C90,D9/C9<1.25),((D9/C9-1))*100)/(B9/52) =IF(AND(D9/C91.26,D9/C9<1.5),((D9/C9-1))*150)/(B9/52) =IF(AND(D9/C91.51,D9/C9<1.75),((D9/C9-1))*200)/(B9/52) Not sure if that makes things any clearer. The second formula you sent generates an answer but it's not quit right. Not paying people enough. I'll play around with it a bit more and see what I can come up with. Thanks again. |
IF/AND Statements in Comp Calculator
Hi Bob,
It looks like I overlooked a couple of elements from your original formulae. Try: =MIN((D9/C90)*(D9/C9-1)*100+(D9/C91.25)*(D9/C9-1)*50+(D9/C91.5)*(D9/C9-1) *50,1.75*200)/(B9/52) or =MIN((D9/C90)*(D9/C9-1)*100+(D9/C91.25)*(D9/C9-1)*50+(D9/C91.5)*(D9/C9-1) *50,350)/(B9/52) Cheers "BobVA" wrote in message ... "macropod" wrote: Hi Bob, Your original post indicated you wanted the payees to get $250 per point for 1-25%, an additional $50 per point for 26-50% and an additional $50 per point for 51-75%. So, why would you change the two 50s to 150 and 200? That would result in the payees getting $250 per point for 1-25%, an additional $150 per point for 26-50% (i.e. $250 per point) and an additional $200 per point for 51-75% (i.e. $450 per point). As for capping, try: =MIN((D9/C90)*(D9/C9)*100+(D9/C91.25)*(D9/C9)*50+(D9/C91.5)*(D9/C9)*50,1. 75*200) or =MIN((D9/C90)*(D9/C9)*100+(D9/C91.25)*(D9/C9)*50+(D9/C91.5)*(D9/C9)*50,35 0) Cheers "BobVA" wrote in message ... "macropod" wrote: Hi Bob, You could also try: =(D9/C90)*(D9/C9)*100+(D9/C91.25)*(D9/C9)*50+(D9/C91.5)*(D9/C9)*50 Cheers "BobVA" wrote in message ... Hello, Almost done with this but I'm stumped on the last remaining formula. The way this compensation plan will work is if reps are between 0-25% growth they will get paid $100 per point, 26-50% $150 per point, 51-75% $200 per point. Growth is calculated monthly vs. a monthly #. My problem is that if the rep is at 40% they get the first 25% at $100 and the next 15% at $150. My formula is an either or thing. The following are my formulas for 0-25%, 26-50%,51-75% respectively: =IF(AND(D9/C90,D9/C9<1.25),((D9/C9-1))*100)/(B9/52) =IF(AND(D9/C91.26,D9/C9<1.5),((D9/C9-1))*150)/(B9/52) =IF(AND(D9/C91.51,D9/C9<1.75),((D9/C9-1))*200)/(B9/52) Any suggestions? Thanks, Bob I changed the (2) 50s to 150 and 200 and the numbers are getting really close. One thing though the plan is capped at 75% growth. By the looks of this that is not accounted for, is it? Thanks for your suggestion. Almost there. Hello again. Before I go on I appreciate the help. The following is my original post: Almost done with this but I'm stumped on the last remaining formula. The way this compensation plan will work is if reps are between 0-25% growth they will get paid $100 per point, 26-50% $150 per point, 51-75% $200 per point. Growth is calculated monthly vs. a monthly #. My problem is that if the rep is at 40% they get the first 25% at $100 and the next 15% at $150. My formula is an either or thing. The following are my formulas for 0-25%, 26-50%,51-75% respectively: =IF(AND(D9/C90,D9/C9<1.25),((D9/C9-1))*100)/(B9/52) =IF(AND(D9/C91.26,D9/C9<1.5),((D9/C9-1))*150)/(B9/52) =IF(AND(D9/C91.51,D9/C9<1.75),((D9/C9-1))*200)/(B9/52) Not sure if that makes things any clearer. The second formula you sent generates an answer but it's not quit right. Not paying people enough. I'll play around with it a bit more and see what I can come up with. Thanks again. |
IF/AND Statements in Comp Calculator
"macropod" wrote: Hi Bob, It looks like I overlooked a couple of elements from your original formulae. Try: =MIN((D9/C90)*(D9/C9-1)*100+(D9/C91.25)*(D9/C9-1)*50+(D9/C91.5)*(D9/C9-1) *50,1.75*200)/(B9/52) or =MIN((D9/C90)*(D9/C9-1)*100+(D9/C91.25)*(D9/C9-1)*50+(D9/C91.5)*(D9/C9-1) *50,350)/(B9/52) Cheers "BobVA" wrote in message ... "macropod" wrote: Hi Bob, Your original post indicated you wanted the payees to get $250 per point for 1-25%, an additional $50 per point for 26-50% and an additional $50 per point for 51-75%. So, why would you change the two 50s to 150 and 200? That would result in the payees getting $250 per point for 1-25%, an additional $150 per point for 26-50% (i.e. $250 per point) and an additional $200 per point for 51-75% (i.e. $450 per point). As for capping, try: =MIN((D9/C90)*(D9/C9)*100+(D9/C91.25)*(D9/C9)*50+(D9/C91.5)*(D9/C9)*50,1. 75*200) or =MIN((D9/C90)*(D9/C9)*100+(D9/C91.25)*(D9/C9)*50+(D9/C91.5)*(D9/C9)*50,35 0) Cheers "BobVA" wrote in message ... "macropod" wrote: Hi Bob, You could also try: =(D9/C90)*(D9/C9)*100+(D9/C91.25)*(D9/C9)*50+(D9/C91.5)*(D9/C9)*50 Cheers "BobVA" wrote in message ... Hello, Almost done with this but I'm stumped on the last remaining formula. The way this compensation plan will work is if reps are between 0-25% growth they will get paid $100 per point, 26-50% $150 per point, 51-75% $200 per point. Growth is calculated monthly vs. a monthly #. My problem is that if the rep is at 40% they get the first 25% at $100 and the next 15% at $150. My formula is an either or thing. The following are my formulas for 0-25%, 26-50%,51-75% respectively: =IF(AND(D9/C90,D9/C9<1.25),((D9/C9-1))*100)/(B9/52) =IF(AND(D9/C91.26,D9/C9<1.5),((D9/C9-1))*150)/(B9/52) =IF(AND(D9/C91.51,D9/C9<1.75),((D9/C9-1))*200)/(B9/52) Any suggestions? Thanks, Bob I changed the (2) 50s to 150 and 200 and the numbers are getting really close. One thing though the plan is capped at 75% growth. By the looks of this that is not accounted for, is it? Thanks for your suggestion. Almost there. Hello again. Before I go on I appreciate the help. The following is my original post: Almost done with this but I'm stumped on the last remaining formula. The way this compensation plan will work is if reps are between 0-25% growth they will get paid $100 per point, 26-50% $150 per point, 51-75% $200 per point. Growth is calculated monthly vs. a monthly #. My problem is that if the rep is at 40% they get the first 25% at $100 and the next 15% at $150. My formula is an either or thing. The following are my formulas for 0-25%, 26-50%,51-75% respectively: =IF(AND(D9/C90,D9/C9<1.25),((D9/C9-1))*100)/(B9/52) =IF(AND(D9/C91.26,D9/C9<1.5),((D9/C9-1))*150)/(B9/52) =IF(AND(D9/C91.51,D9/C9<1.75),((D9/C9-1))*200)/(B9/52) Not sure if that makes things any clearer. The second formula you sent generates an answer but it's not quit right. Not paying people enough. I'll play around with it a bit more and see what I can come up with. Thanks again. Hello. I think you are back where I started. I think the formula you just gave me pays everyone at one level instead of splitting it up among the different payouts. Example; based on a 5 week month someone at 52% over plan would get $240 for the 0-25 range and $360 for the 26-50 range. and $38 for the 51-75 range. This formula generated $1,086. I'm assuming like my original formula it is paying everyone at the highest teer only. |
IF/AND Statements in Comp Calculator
Hi Bob,
Well, without knowing exactly how all your cell references relate to the intended outcome, it's hard to say where things are going wrong. For example, I have no idea what the '-1' in your '(D9/C9-1)' formulae that I've replicated is meant to achieve - all I can tell that it deducts 1 from the D9/C9 calculation. A table with some sample data and expected results might help. Until your last post to Ron, we didn't even have data descriptions. Cheers "BobVA" wrote in message ... "macropod" wrote: Hi Bob, It looks like I overlooked a couple of elements from your original formulae. Try: =MIN((D9/C90)*(D9/C9-1)*100+(D9/C91.25)*(D9/C9-1)*50+(D9/C91.5)*(D9/C9-1) *50,1.75*200)/(B9/52) or =MIN((D9/C90)*(D9/C9-1)*100+(D9/C91.25)*(D9/C9-1)*50+(D9/C91.5)*(D9/C9-1) *50,350)/(B9/52) Cheers "BobVA" wrote in message ... "macropod" wrote: Hi Bob, Your original post indicated you wanted the payees to get $250 per point for 1-25%, an additional $50 per point for 26-50% and an additional $50 per point for 51-75%. So, why would you change the two 50s to 150 and 200? That would result in the payees getting $250 per point for 1-25%, an additional $150 per point for 26-50% (i.e. $250 per point) and an additional $200 per point for 51-75% (i.e. $450 per point). As for capping, try: =MIN((D9/C90)*(D9/C9)*100+(D9/C91.25)*(D9/C9)*50+(D9/C91.5)*(D9/C9)*50,1. 75*200) or =MIN((D9/C90)*(D9/C9)*100+(D9/C91.25)*(D9/C9)*50+(D9/C91.5)*(D9/C9)*50,35 0) Cheers "BobVA" wrote in message ... "macropod" wrote: Hi Bob, You could also try: =(D9/C90)*(D9/C9)*100+(D9/C91.25)*(D9/C9)*50+(D9/C91.5)*(D9/C9)*50 Cheers "BobVA" wrote in message ... Hello, Almost done with this but I'm stumped on the last remaining formula. The way this compensation plan will work is if reps are between 0-25% growth they will get paid $100 per point, 26-50% $150 per point, 51-75% $200 per point. Growth is calculated monthly vs. a monthly #. My problem is that if the rep is at 40% they get the first 25% at $100 and the next 15% at $150. My formula is an either or thing. The following are my formulas for 0-25%, 26-50%,51-75% respectively: =IF(AND(D9/C90,D9/C9<1.25),((D9/C9-1))*100)/(B9/52) =IF(AND(D9/C91.26,D9/C9<1.5),((D9/C9-1))*150)/(B9/52) =IF(AND(D9/C91.51,D9/C9<1.75),((D9/C9-1))*200)/(B9/52) Any suggestions? Thanks, Bob I changed the (2) 50s to 150 and 200 and the numbers are getting really close. One thing though the plan is capped at 75% growth. By the looks of this that is not accounted for, is it? Thanks for your suggestion. Almost there. Hello again. Before I go on I appreciate the help. The following is my original post: Almost done with this but I'm stumped on the last remaining formula. The way this compensation plan will work is if reps are between 0-25% growth they will get paid $100 per point, 26-50% $150 per point, 51-75% $200 per point. Growth is calculated monthly vs. a monthly #. My problem is that if the rep is at 40% they get the first 25% at $100 and the next 15% at $150. My formula is an either or thing. The following are my formulas for 0-25%, 26-50%,51-75% respectively: =IF(AND(D9/C90,D9/C9<1.25),((D9/C9-1))*100)/(B9/52) =IF(AND(D9/C91.26,D9/C9<1.5),((D9/C9-1))*150)/(B9/52) =IF(AND(D9/C91.51,D9/C9<1.75),((D9/C9-1))*200)/(B9/52) Not sure if that makes things any clearer. The second formula you sent generates an answer but it's not quit right. Not paying people enough. I'll play around with it a bit more and see what I can come up with. Thanks again. Hello. I think you are back where I started. I think the formula you just gave me pays everyone at one level instead of splitting it up among the different payouts. Example; based on a 5 week month someone at 52% over plan would get $240 for the 0-25 range and $360 for the 26-50 range. and $38 for the 51-75 range. This formula generated $1,086. I'm assuming like my original formula it is paying everyone at the highest teer only. |
IF/AND Statements in Comp Calculator
"macropod" wrote: Hi Bob, Well, without knowing exactly how all your cell references relate to the intended outcome, it's hard to say where things are going wrong. For example, I have no idea what the '-1' in your '(D9/C9-1)' formulae that I've replicated is meant to achieve - all I can tell that it deducts 1 from the D9/C9 calculation. A table with some sample data and expected results might help. Until your last post to Ron, we didn't even have data descriptions. Cheers "BobVA" wrote in message ... "macropod" wrote: Hi Bob, It looks like I overlooked a couple of elements from your original formulae. Try: =MIN((D9/C90)*(D9/C9-1)*100+(D9/C91.25)*(D9/C9-1)*50+(D9/C91.5)*(D9/C9-1) *50,1.75*200)/(B9/52) or =MIN((D9/C90)*(D9/C9-1)*100+(D9/C91.25)*(D9/C9-1)*50+(D9/C91.5)*(D9/C9-1) *50,350)/(B9/52) Cheers "BobVA" wrote in message ... "macropod" wrote: Hi Bob, Your original post indicated you wanted the payees to get $250 per point for 1-25%, an additional $50 per point for 26-50% and an additional $50 per point for 51-75%. So, why would you change the two 50s to 150 and 200? That would result in the payees getting $250 per point for 1-25%, an additional $150 per point for 26-50% (i.e. $250 per point) and an additional $200 per point for 51-75% (i.e. $450 per point). As for capping, try: =MIN((D9/C90)*(D9/C9)*100+(D9/C91.25)*(D9/C9)*50+(D9/C91.5)*(D9/C9)*50,1. 75*200) or =MIN((D9/C90)*(D9/C9)*100+(D9/C91.25)*(D9/C9)*50+(D9/C91.5)*(D9/C9)*50,35 0) Cheers "BobVA" wrote in message ... "macropod" wrote: Hi Bob, You could also try: =(D9/C90)*(D9/C9)*100+(D9/C91.25)*(D9/C9)*50+(D9/C91.5)*(D9/C9)*50 Cheers "BobVA" wrote in message ... Hello, Almost done with this but I'm stumped on the last remaining formula. The way this compensation plan will work is if reps are between 0-25% growth they will get paid $100 per point, 26-50% $150 per point, 51-75% $200 per point. Growth is calculated monthly vs. a monthly #. My problem is that if the rep is at 40% they get the first 25% at $100 and the next 15% at $150. My formula is an either or thing. The following are my formulas for 0-25%, 26-50%,51-75% respectively: =IF(AND(D9/C90,D9/C9<1.25),((D9/C9-1))*100)/(B9/52) =IF(AND(D9/C91.26,D9/C9<1.5),((D9/C9-1))*150)/(B9/52) =IF(AND(D9/C91.51,D9/C9<1.75),((D9/C9-1))*200)/(B9/52) Any suggestions? Thanks, Bob I changed the (2) 50s to 150 and 200 and the numbers are getting really close. One thing though the plan is capped at 75% growth. By the looks of this that is not accounted for, is it? Thanks for your suggestion. Almost there. Hello again. Before I go on I appreciate the help. The following is my original post: Almost done with this but I'm stumped on the last remaining formula. The way this compensation plan will work is if reps are between 0-25% growth they will get paid $100 per point, 26-50% $150 per point, 51-75% $200 per point. Growth is calculated monthly vs. a monthly #. My problem is that if the rep is at 40% they get the first 25% at $100 and the next 15% at $150. My formula is an either or thing. The following are my formulas for 0-25%, 26-50%,51-75% respectively: =IF(AND(D9/C90,D9/C9<1.25),((D9/C9-1))*100)/(B9/52) =IF(AND(D9/C91.26,D9/C9<1.5),((D9/C9-1))*150)/(B9/52) =IF(AND(D9/C91.51,D9/C9<1.75),((D9/C9-1))*200)/(B9/52) Not sure if that makes things any clearer. The second formula you sent generates an answer but it's not quit right. Not paying people enough. I'll play around with it a bit more and see what I can come up with. Thanks again. Hello. I think you are back where I started. I think the formula you just gave me pays everyone at one level instead of splitting it up among the different payouts. Example; based on a 5 week month someone at 52% over plan would get $240 for the 0-25 range and $360 for the 26-50 range. and $38 for the 51-75 range. This formula generated $1,086. I'm assuming like my original formula it is paying everyone at the highest teer only. Hello again. You guys are troopers! The -1 in the original formula was to pay folks on .25, .5, or .75. I.E. using the numbers that follow the answer is 1.52 so I subtracted 1 to get to .52 and then run the calculation. Not sure how much data you want but the "real" numbers I am using is 5 for B9, 53,731 for C9, and 81,808 for D9. The "correct" answer using a calculator would be $638, $240 for the 0-25 range and $360 for the 26-50 range, and $38 for the 51-75 range. |
IF/AND Statements in Comp Calculator
BobVA
Using your latest sample data of: B9: 5 C9: 53,731 D9: 81,808 And...this lookup table in A1:B5 A1: -1000% B1: 0 A2: 0% B2: 100 A3: 25% B3: 50 A4: 50% B4: 50 This formula resolves to $639: E9: =INT(SUMPRODUCT(((D9-C9)/C9=$A$1:$A$4)*INT(((MIN((D9-C9)/C9,75%)-A1:A4)*100))*(B1:B4))*B9/52) Does that help? *********** Regards, Ron XL2002, WinXP "BobVA" wrote: "Ron Coderre" wrote: I understand your point... I tried to emulate your posted formula, but I didn't have much to go on. I'm guessing that C9 is some kind of volume? hundreds? millions? units? Is C9 the previous amount and D9 the new amount? What does B9 represent? *********** Regards, Ron XL2002, WinXP "BobVA" wrote: "Ron Coderre" wrote: Bob: See if this helps Build this table in A1:B4 Increase Incremental Rate 0% 100 25% 50 50% 50 Then, with values in B9, C9, and D9 E9: =SUMPRODUCT(((D9-C9)/C9=$A$2:$A$4)*(((D9-C9)/C9-A2:A4)*100)*(B2:B4))/(B9/52) Note: in case text wrap impacts the display, there are no spaces in that formula. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "BobVA" wrote: Hello, Almost done with this but I'm stumped on the last remaining formula. The way this compensation plan will work is if reps are between 0-25% growth they will get paid $100 per point, 26-50% $150 per point, 51-75% $200 per point. Growth is calculated monthly vs. a monthly #. My problem is that if the rep is at 40% they get the first 25% at $100 and the next 15% at $150. My formula is an either or thing. The following are my formulas for 0-25%, 26-50%,51-75% respectively: =IF(AND(D9/C90,D9/C9<1.25),((D9/C9-1))*100)/(B9/52) =IF(AND(D9/C91.26,D9/C9<1.5),((D9/C9-1))*150)/(B9/52) =IF(AND(D9/C91.51,D9/C9<1.75),((D9/C9-1))*200)/(B9/52) Any suggestions? Thanks, Bob Thanks for trying. The formula generates an answer that doesn't make sense. The numbers I'm using gives the rep 52% growth in a month. The formula generates 2.94. Hello, D9 represents actual sales that month, C9 is Quota for the month. B9 represents the number of weeks in that given month. Not all our months have the same number of weeks. THanks again. |
IF/AND Statements in Comp Calculator
Hi Bob,
The correct formula depends how the rep is paid. For example, a rep could be paid: A $100/point on all points over quota $150/point on all points over quota if the points exceed the quota by more than 25 $150/point on all points over quota if the points exceed the quota by more than 50 to a maximum of 75 points , or B $100/point for the 1st 25% over quota, plus $150/point for the 2nd 25% over quota, plus $200/point for the 3rd 25% over quota Using your data, the Sales/Quota figure is 1.522548... Leaving aside the weeks part of the calculation, if the rep is paid on the basis of A, the result would be: $100*52.248 = $5,225.48 + $50*52.248 = $2,612.74 + $50*52.248 = $2,612.74 + = $10,450.95 Alternatively, if the rep is paid on the basis of B, the result would be: $100*25.00 = $2,500.00 + $150*25.00 = $3,750.00 + $200*2.2548 = $450.96 = $6,700.96 Introducing the weeks part of the calculation for 5 weeks gives $1,004.90 and $644.32 for A and B, respectively. Your description fits A, but your calculation comes close to fitting B. For A, the formula is: =MIN((D9/C90)*(D9/C9-1)*100+(D9/C91.25)*(D9/C9-1)*50+(D9/C91.5)*(D9/C9-1) *50,0.75*200)*100*B9/52 For B, the formula is: =((D9/C90)*MIN((D9/C9-1),0.25)*100+(D9/C91.25)*MIN((D9/C9-1.25),0.25)*150+ (D9/C91.5)*MIN((D9/C9-1.5),0.25)*200)*100*B9/52 Add whatever rounding you need. Cheers "BobVA" wrote in message ... "macropod" wrote: Hi Bob, Well, without knowing exactly how all your cell references relate to the intended outcome, it's hard to say where things are going wrong. For example, I have no idea what the '-1' in your '(D9/C9-1)' formulae that I've replicated is meant to achieve - all I can tell that it deducts 1 from the D9/C9 calculation. A table with some sample data and expected results might help. Until your last post to Ron, we didn't even have data descriptions. Cheers "BobVA" wrote in message ... "macropod" wrote: Hi Bob, It looks like I overlooked a couple of elements from your original formulae. Try: =MIN((D9/C90)*(D9/C9-1)*100+(D9/C91.25)*(D9/C9-1)*50+(D9/C91.5)*(D9/C9-1) *50,1.75*200)/(B9/52) or =MIN((D9/C90)*(D9/C9-1)*100+(D9/C91.25)*(D9/C9-1)*50+(D9/C91.5)*(D9/C9-1) *50,350)/(B9/52) Cheers "BobVA" wrote in message ... "macropod" wrote: Hi Bob, Your original post indicated you wanted the payees to get $250 per point for 1-25%, an additional $50 per point for 26-50% and an additional $50 per point for 51-75%. So, why would you change the two 50s to 150 and 200? That would result in the payees getting $250 per point for 1-25%, an additional $150 per point for 26-50% (i.e. $250 per point) and an additional $200 per point for 51-75% (i.e. $450 per point). As for capping, try: =MIN((D9/C90)*(D9/C9)*100+(D9/C91.25)*(D9/C9)*50+(D9/C91.5)*(D9/C9)*50,1. 75*200) or =MIN((D9/C90)*(D9/C9)*100+(D9/C91.25)*(D9/C9)*50+(D9/C91.5)*(D9/C9)*50,35 0) Cheers "BobVA" wrote in message ... "macropod" wrote: Hi Bob, You could also try: =(D9/C90)*(D9/C9)*100+(D9/C91.25)*(D9/C9)*50+(D9/C91.5)*(D9/C9)*50 Cheers "BobVA" wrote in message ... Hello, Almost done with this but I'm stumped on the last remaining formula. The way this compensation plan will work is if reps are between 0-25% growth they will get paid $100 per point, 26-50% $150 per point, 51-75% $200 per point. Growth is calculated monthly vs. a monthly #. My problem is that if the rep is at 40% they get the first 25% at $100 and the next 15% at $150. My formula is an either or thing. The following are my formulas for 0-25%, 26-50%,51-75% respectively: =IF(AND(D9/C90,D9/C9<1.25),((D9/C9-1))*100)/(B9/52) =IF(AND(D9/C91.26,D9/C9<1.5),((D9/C9-1))*150)/(B9/52) =IF(AND(D9/C91.51,D9/C9<1.75),((D9/C9-1))*200)/(B9/52) Any suggestions? Thanks, Bob I changed the (2) 50s to 150 and 200 and the numbers are getting really close. One thing though the plan is capped at 75% growth. By the looks of this that is not accounted for, is it? Thanks for your suggestion. Almost there. Hello again. Before I go on I appreciate the help. The following is my original post: Almost done with this but I'm stumped on the last remaining formula. The way this compensation plan will work is if reps are between 0-25% growth they will get paid $100 per point, 26-50% $150 per point, 51-75% $200 per point. Growth is calculated monthly vs. a monthly #. My problem is that if the rep is at 40% they get the first 25% at $100 and the next 15% at $150. My formula is an either or thing. The following are my formulas for 0-25%, 26-50%,51-75% respectively: =IF(AND(D9/C90,D9/C9<1.25),((D9/C9-1))*100)/(B9/52) =IF(AND(D9/C91.26,D9/C9<1.5),((D9/C9-1))*150)/(B9/52) =IF(AND(D9/C91.51,D9/C9<1.75),((D9/C9-1))*200)/(B9/52) Not sure if that makes things any clearer. The second formula you sent generates an answer but it's not quit right. Not paying people enough. I'll play around with it a bit more and see what I can come up with. Thanks again. Hello. I think you are back where I started. I think the formula you just gave me pays everyone at one level instead of splitting it up among the different payouts. Example; based on a 5 week month someone at 52% over plan would get $240 for the 0-25 range and $360 for the 26-50 range. and $38 for the 51-75 range. This formula generated $1,086. I'm assuming like my original formula it is paying everyone at the highest teer only. Hello again. You guys are troopers! The -1 in the original formula was to pay folks on .25, .5, or .75. I.E. using the numbers that follow the answer is 1.52 so I subtracted 1 to get to .52 and then run the calculation. Not sure how much data you want but the "real" numbers I am using is 5 for B9, 53,731 for C9, and 81,808 for D9. The "correct" answer using a calculator would be $638, $240 for the 0-25 range and $360 for the 26-50 range, and $38 for the 51-75 range. |
| All times are GMT +1. The time now is 08:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com