|
Calculate commissions
Here is the issue:
Margin Market Seg Cust Seg Commission 50.00 Digital A ? 32.00 Professional E ? 50.00 Digital B ? 42.00 Professional D ? I want to be able to say: If Market Seg = Digital then Multiple Margin by ..008 If Market Seg = Professional then Multiple Margin by .011 How can I Do this and how? |
Hi
in D1: =A1*IF(B1="Digital",0.08,0.11) if you only have these two segments -- Regards Frank Kabel Frankfurt, Germany "Pete Petersen" <Pete schrieb im Newsbeitrag ... Here is the issue: Margin Market Seg Cust Seg Commission 50.00 Digital A ? 32.00 Professional E ? 50.00 Digital B ? 42.00 Professional D ? I want to be able to say: If Market Seg = Digital then Multiple Margin by .008 If Market Seg = Professional then Multiple Margin by .011 How can I Do this and how? |
One way:
Assuming Margin in column A, Market Segment in column B, Commission in Column D: D2: =A1*(0.008*(B2="Digital")+0.011*(B2="Professional" )) If Digital and Professional are the only two options, you can shorten it to: D2: =A2*(0.008+0.003*(B2="Professional")) In article , "Pete Petersen" <Pete wrote: Here is the issue: Margin Market Seg Cust Seg Commission 50.00 Digital A ? 32.00 Professional E ? 50.00 Digital B ? 42.00 Professional D ? I want to be able to say: If Market Seg = Digital then Multiple Margin by .008 If Market Seg = Professional then Multiple Margin by .011 How can I Do this and how? |
I really appreciate your help with this but it gets a liitle more complex.
Here is the breakdown: We have more than 2 market segments that will base our commissions. Let's take this example: Column A Column B $(65.60) Digital Printer $(69.35) Professional Photogr $(29.16) Professional Photogr $(152.48) Digital Printer $(306.00) Digital Printer $(95.90) Digital Printer $(40.00) Professional Photog $(110.42) Exhibit House $(110.42) Exhibit House $(23.74) Professional Photogr $(99.50) Digital Printer $(29.16) Professional Photogr $(26.74) Professional Photogr $(153.00) Digital Printer $(29.16) Professional Photogr $(63.75) Digital Printer $(69.50) Digital Printer $(35.98) Digital Printer $(93.60) Digital Printer I want to be able to use the following: Digital Printer = .0779 Professional Photogr = .1179 Exhibit House = .0779 There are a couple levels of commission...I want to be able to say if Column B = one the above statements then multiple my Colomn A by the rates stated above. Thank you for help in this issue. Pete Petersen "Frank Kabel" wrote: Hi in D1: =A1*IF(B1="Digital",0.08,0.11) if you only have these two segments -- Regards Frank Kabel Frankfurt, Germany "Pete Petersen" <Pete schrieb im Newsbeitrag ... Here is the issue: Margin Market Seg Cust Seg Commission 50.00 Digital A ? 32.00 Professional E ? 50.00 Digital B ? 42.00 Professional D ? I want to be able to say: If Market Seg = Digital then Multiple Margin by .008 If Market Seg = Professional then Multiple Margin by .011 How can I Do this and how? |
One way:
Put your descriptions and rates in a table, perhaps on a separate sheet (say, Sheet2): A B 1 Description Rate 2 Digital Printer 7.79% 2 Professional Photogr 11.79% 3 Exhibit House 7.79% 4 ... Then in column C in sheet1, enter =A1*VLOOKUP(B1,Sheet2!A:B,2,FALSE) In article , "Pete Petersen" wrote: I really appreciate your help with this but it gets a liitle more complex. Here is the breakdown: We have more than 2 market segments that will base our commissions. Let's take this example: Column A Column B $(65.60) Digital Printer $(69.35) Professional Photogr $(29.16) Professional Photogr $(152.48) Digital Printer $(306.00) Digital Printer $(95.90) Digital Printer $(40.00) Professional Photog $(110.42) Exhibit House $(110.42) Exhibit House $(23.74) Professional Photogr $(99.50) Digital Printer $(29.16) Professional Photogr $(26.74) Professional Photogr $(153.00) Digital Printer $(29.16) Professional Photogr $(63.75) Digital Printer $(69.50) Digital Printer $(35.98) Digital Printer $(93.60) Digital Printer I want to be able to use the following: Digital Printer = .0779 Professional Photogr = .1179 Exhibit House = .0779 There are a couple levels of commission...I want to be able to say if Column B = one the above statements then multiple my Colomn A by the rates stated above. Thank you for help in this issue. Pete Petersen |
You ROCK!....But I like to mix things up and I have added yet another
obsticle to the equation: I have created a SHEET2 with the way you have suggested and added the commissions using the formula to column Commission but take a look at this issue: Margin Commission MRKT SEG SEG $(15.52) $(1.21) Digital Printer A $(45.00) $(3.51) Digital Printer A $(15.00) $(1.17) Digital Printer A $(15.00) $(1.17) Digital Printer A $(30.00) $(2.34) Digital Printer C $(83.00) $(6.47) Digital Printer C $(276.59) $(21.55) Quick Printers C $(58.00) $(4.52) Digital Printer C $(51.00) $(3.97) Digital Printer C $(39.00) $(4.56) Professional Photogr E $(33.25) $(2.59) Digital Printer D $(39.78) $(4.65) Professional Photogr D $(14.00) $(1.64) Professional Photogr E $(65.60) $(5.11) Digital Printer C $(69.35) $(8.10) Professional Photogr D $(29.16) $(3.41) Professional Photogr E $(152.48) $(11.88) Digital Printer P $(306.00) $(23.84) Digital Printer C $(95.90) $(7.47) Digital Printer C $(40.00) $(4.67) Professional Photog D $(110.42) $(8.60) Exhibit House Q $(110.42) $(8.60) Exhibit House Q $(23.74) $(2.77) Professional Photogr C $(99.50) $(7.75) Digital Printer B $(29.16) $(3.41) Professional Photogr D $(26.74) $(3.12) Professional Photogr D $(153.00) $(11.92) Digital Printer C $(29.16) $(3.41) Professional Photogr E $(63.75) $(4.97) Digital Printer B $(69.50) $(5.41) Digital Printer B $(35.98) $(2.80) Digital Printer B $(93.60) $(7.29) Digital Printer A I need to keep the same thing as before only if column SEG equals either "M", "N", "O", "P", or "Q" then they get paid an even 5% regardless of the Market SEGMENT. Do you have a way to do this. "JE McGimpsey" wrote: One way: Put your descriptions and rates in a table, perhaps on a separate sheet (say, Sheet2): A B 1 Description Rate 2 Digital Printer 7.79% 2 Professional Photogr 11.79% 3 Exhibit House 7.79% 4 ... Then in column C in sheet1, enter =A1*VLOOKUP(B1,Sheet2!A:B,2,FALSE) In article , "Pete Petersen" wrote: I really appreciate your help with this but it gets a liitle more complex. Here is the breakdown: We have more than 2 market segments that will base our commissions. Let's take this example: Column A Column B $(65.60) Digital Printer $(69.35) Professional Photogr $(29.16) Professional Photogr $(152.48) Digital Printer $(306.00) Digital Printer $(95.90) Digital Printer $(40.00) Professional Photog $(110.42) Exhibit House $(110.42) Exhibit House $(23.74) Professional Photogr $(99.50) Digital Printer $(29.16) Professional Photogr $(26.74) Professional Photogr $(153.00) Digital Printer $(29.16) Professional Photogr $(63.75) Digital Printer $(69.50) Digital Printer $(35.98) Digital Printer $(93.60) Digital Printer I want to be able to use the following: Digital Printer = .0779 Professional Photogr = .1179 Exhibit House = .0779 There are a couple levels of commission...I want to be able to say if Column B = one the above statements then multiple my Colomn A by the rates stated above. Thank you for help in this issue. Pete Petersen |
One way:
=A2*IF(ISERR(FIND(D2,"MNOPQ")),VLOOKUP(C2,Sheet2!A :B,2,FALSE),5%) Note: In general, it's a better use of the replyer's time to lay out the entire problem, rather than playing bring me a rock... In article , "Pete Petersen" wrote: You ROCK!....But I like to mix things up and I have added yet another obsticle to the equation: I have created a SHEET2 with the way you have suggested and added the commissions using the formula to column Commission but take a look at this issue: Margin Commission MRKT SEG SEG $(15.52) $(1.21) Digital Printer A $(45.00) $(3.51) Digital Printer A $(15.00) $(1.17) Digital Printer A $(15.00) $(1.17) Digital Printer A $(30.00) $(2.34) Digital Printer C $(83.00) $(6.47) Digital Printer C $(276.59) $(21.55) Quick Printers C $(58.00) $(4.52) Digital Printer C $(51.00) $(3.97) Digital Printer C $(39.00) $(4.56) Professional Photogr E $(33.25) $(2.59) Digital Printer D $(39.78) $(4.65) Professional Photogr D $(14.00) $(1.64) Professional Photogr E $(65.60) $(5.11) Digital Printer C $(69.35) $(8.10) Professional Photogr D $(29.16) $(3.41) Professional Photogr E $(152.48) $(11.88) Digital Printer P $(306.00) $(23.84) Digital Printer C $(95.90) $(7.47) Digital Printer C $(40.00) $(4.67) Professional Photog D $(110.42) $(8.60) Exhibit House Q $(110.42) $(8.60) Exhibit House Q $(23.74) $(2.77) Professional Photogr C $(99.50) $(7.75) Digital Printer B $(29.16) $(3.41) Professional Photogr D $(26.74) $(3.12) Professional Photogr D $(153.00) $(11.92) Digital Printer C $(29.16) $(3.41) Professional Photogr E $(63.75) $(4.97) Digital Printer B $(69.50) $(5.41) Digital Printer B $(35.98) $(2.80) Digital Printer B $(93.60) $(7.29) Digital Printer A I need to keep the same thing as before only if column SEG equals either "M", "N", "O", "P", or "Q" then they get paid an even 5% regardless of the Market SEGMENT. Do you have a way to do this. |
| All times are GMT +1. The time now is 06:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com