If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need help writing formulas for following functions -
I am building a spreadsheet which only needs to contain a
few fields but I am having great difficulty getting the calculations right. If you can help I would be eternally grateful! Thanks in advance - Here are the columns I wish to include: Sale Price, Units, Sale Volume, Commission %, Affinity Fee (%), Company commission, Agent commission. Here is what needs to be calculated: Sale Price - manually entered (e.g. $100,000) Units - L, S or LS manually entered are the only possible entries (where L(ist) is worth .5, S(ell) is worth .5 and LS is worth 1) Sale Volume - $ amount that one gets credit for, based on Sale Price, depending on whether the Units sold were .5 or 1. If .5 (either L or S), and the Sale Price was $100,000, Sale Volume will equal $50,000. If Units = LS (i.e. "1"), Sale Volume in this example = $100,000. This field must be calculated. Commission % - Manually entered percentage. Examples are 3%, 5% or 6%. Affinity Fee % - Manually entered percentage. May be 0%, 10%, 20%, etc. Company Commission - This field must be calculated. It is the Sale Price multiplied by the Commission %, the result of which is then multiplied by the Affinity Fee % (if there is one). The resulting $ amount is the Company Commission. If you can figure this out and send a spreadsheet containing the necessary formulas, you will be my savior. I am at wit's end on this, despite the fact that for a veteran Excel user, the formulas may not be terribly difficult. Again, many thanks in advance for any assistance you can offer. Thank you, Yoohah |
#2
|
|||
|
|||
Need help writing formulas for following functions -
Sale Price Units Sale Volume Commission % Affinity Fee (%)
Company commission Agent commission $100,000.00 L $50,000.00 3% 0% $0.00 $3,000.00 Sales Volume =IF(B2="LS",1,0.5)*A2 Company commission =A2*D2*E2 Agent commission =A2*D2 Format Commission and Affinity Fee as a percentage wrote in message ... I am building a spreadsheet which only needs to contain a few fields but I am having great difficulty getting the calculations right. If you can help I would be eternally grateful! Thanks in advance - Here are the columns I wish to include: Sale Price, Units, Sale Volume, Commission %, Affinity Fee (%), Company commission, Agent commission. Here is what needs to be calculated: Sale Price - manually entered (e.g. $100,000) Units - L, S or LS manually entered are the only possible entries (where L(ist) is worth .5, S(ell) is worth .5 and LS is worth 1) Sale Volume - $ amount that one gets credit for, based on Sale Price, depending on whether the Units sold were .5 or 1. If .5 (either L or S), and the Sale Price was $100,000, Sale Volume will equal $50,000. If Units = LS (i.e. "1"), Sale Volume in this example = $100,000. This field must be calculated. Commission % - Manually entered percentage. Examples are 3%, 5% or 6%. Affinity Fee % - Manually entered percentage. May be 0%, 10%, 20%, etc. Company Commission - This field must be calculated. It is the Sale Price multiplied by the Commission %, the result of which is then multiplied by the Affinity Fee % (if there is one). The resulting $ amount is the Company Commission. If you can figure this out and send a spreadsheet containing the necessary formulas, you will be my savior. I am at wit's end on this, despite the fact that for a veteran Excel user, the formulas may not be terribly difficult. Again, many thanks in advance for any assistance you can offer. Thank you, Yoohah |
#3
|
|||
|
|||
Need help writing formulas for following functions -
Works great except for 1 thing:
1. If there is no Affinity Fee %, Company Commission winds up equalling zero. Instead, what should happen is no amount should be deducted from the Company Commission if no Affinity Fee % exists. Any Affinity Fee % 0% should take that percentage off of the Sale Price. Thanks again, RJB. I bow to thee!! -----Original Message----- Sale Price Units Sale Volume Commission % Affinity Fee (%) Company commission Agent commission $100,000.00 L $50,000.00 3% 0% $0.00 $3,000.00 Sales Volume =IF(B2="LS",1,0.5)*A2 Company commission =A2*D2*E2 Agent commission =A2*D2 Format Commission and Affinity Fee as a percentage wrote in message ... I am building a spreadsheet which only needs to contain a few fields but I am having great difficulty getting the calculations right. If you can help I would be eternally grateful! Thanks in advance - Here are the columns I wish to include: Sale Price, Units, Sale Volume, Commission %, Affinity Fee (%), Company commission, Agent commission. Here is what needs to be calculated: Sale Price - manually entered (e.g. $100,000) Units - L, S or LS manually entered are the only possible entries (where L(ist) is worth .5, S(ell) is worth .5 and LS is worth 1) Sale Volume - $ amount that one gets credit for, based on Sale Price, depending on whether the Units sold were .5 or 1. If .5 (either L or S), and the Sale Price was $100,000, Sale Volume will equal $50,000. If Units = LS (i.e. "1"), Sale Volume in this example = $100,000. This field must be calculated. Commission % - Manually entered percentage. Examples are 3%, 5% or 6%. Affinity Fee % - Manually entered percentage. May be 0%, 10%, 20%, etc. Company Commission - This field must be calculated. It is the Sale Price multiplied by the Commission %, the result of which is then multiplied by the Affinity Fee % (if there is one). The resulting $ amount is the Company Commission. If you can figure this out and send a spreadsheet containing the necessary formulas, you will be my savior. I am at wit's end on this, despite the fact that for a veteran Excel user, the formulas may not be terribly difficult. Again, many thanks in advance for any assistance you can offer. Thank you, Yoohah . |
#4
|
|||
|
|||
Need help writing formulas for following functions -
In article ,
"Yoohah" wrote: Works great except for 1 thing: 1. If there is no Affinity Fee %, Company Commission winds up equalling zero. Hi, Try, =A2*D2*IF(E20,E2,1) Hope this helps! |
#5
|
|||
|
|||
Need help writing formulas for following functions -
Thanks, Domenic. That helps but I'm still running into
the problem I cited above in the message to "Arvi Laanemets" May 30 2004 4:54AM. -----Original Message----- In article , "Yoohah" wrote: Works great except for 1 thing: 1. If there is no Affinity Fee %, Company Commission winds up equalling zero. Hi, Try, =A2*D2*IF(E20,E2,1) Hope this helps! . |
Thread Tools | |
Display Modes | |
|
|