Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help w/ commission function
Hello all,
could really use some help with this I need the function to look at B1 (discount%) and subract this amount, if any, from each tier%, then calc. the fee amount. Example: A B C D 1 client1 0.25% 400,000 11,000 2 client2 0 300,000 9,000 3 client3 0.10% 450,000 13,050 etc. In this example my Tiers are 3%,2.5%,2.25%,2%,1.75% and my dicount for client #1 is 0.25%, client #2 is 0% and client #3 is 0.10%. Given Tier1 is =<499,999.99, client #1 would pay 11,000 ((3%-0.25%)*400,000), client #2 9,000(3%*300,000) and client #3 13,050((3%-0.10%)*450,000) Code so far: Function fee(Assets) ' quarterly fee Const Tier1 = 0.03 Const Tier2 = 0.025 Const Tier3 = 0.0225 Const Tier4 = 0.02 Const Tier5 = 0.0175 ' calculates annual management fee Select Case Assets Case 1 To 499999.99 fee = Assets * Tier1 Case 500000 To 999999.99 fee = 500000 * Tier1 + (Assets - 500000) * Tier2 Case 1000000 To 1999999.99 fee = 500000 * Tier1 + 500000 * Tier2 + (Assets - 1000000) * Tier3 Case 2000000 To 5000000 fee = 500000 * Tier1 + 500000 * Tier2 + 1000000 * Tier3 + _ (Assets - 2000000) * Tier4 Case Is = 5000000 fee = 500000 * Tier1 + 500000 * Tier2 + 1000000 * Tier3 + _ 3000000 * Tier4 + (Assets - 5000000) * Tier5 End Select End Function If anyone has a suggestion for the best methodology, please feelfree to comment. Thanks, RTB *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help w/ commission function
See one reply in response to your post in m.p.e.worksheet.functions.
Please don't post the same question in multiple groups. It only serves to fragment your answers, and potentially wastes the time of those replying to a question that has already been answered. For tips on using these newsgroups effectively, see http://cpearson.com/excel/newposte.htm In article , robert burger wrote: Hello all, could really use some help with this |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Commission - IF formulas | Excel Discussion (Misc queries) | |||
commission lookup | Excel Discussion (Misc queries) | |||
NEED HELP! Commission calculation | Excel Discussion (Misc queries) | |||
Commission Spreadsheet Help | Excel Discussion (Misc queries) | |||
Formula for Commission | Excel Worksheet Functions |