Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
direction needed
Good morning all,
Does anyone know what I mean when I say? Problem: I have a sliding commission function that ref. a range of cells for the tier % in the sheet. The problem is that the tier % is negotiable and can change for each client (multiple clients). Question: How can I ref. different tier % for the same function? Is this even possible? I 've review a number of posts here and on google and am confused. Please point me in the right direction. 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
|
|||
|
|||
direction needed
Hi Robert
if i'm understanding you correctly, you can embed an IF in a VLOOKUP statement: say i have four columns, showing amount invested and % dividend for three different investment types (say this spans rows 1 - 10) column A is investment amount column B is scale A column C is scale B and column D is scale C then i have my list of clients (say this spans rows 15-100) column A is name column B is investment amount column C is scale (A, B or C) and in column D i want the % returned in column D of the client list i can use the following =VLOOKUP(B16,$A$2:$D$10,IF(C16="A",2,IF(C16="B",3, 4)) Hope this helps Cheers JulieD "robert burger" wrote in message ... Good morning all, Does anyone know what I mean when I say? Problem: I have a sliding commission function that ref. a range of cells for the tier % in the sheet. The problem is that the tier % is negotiable and can change for each client (multiple clients). Question: How can I ref. different tier % for the same function? Is this even possible? I 've review a number of posts here and on google and am confused. Please point me in the right direction. Thanks, RTB *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
direction needed
JulieD,
Thanks for your response! However, i would like to do this as a VBA. Any ideas? Thanks, Rob *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
direction needed
Hi Robert
do you want to use VBA to put the formula in the cell or do you want to process the statement using VBA - both can be done, but how about you post some details of cell addresses etc so we're playing with "real" numbers. Cheers JulieD "robert burger" wrote in message ... JulieD, Thanks for your response! However, i would like to do this as a VBA. Any ideas? Thanks, Rob *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
direction needed
Good morning JulieD,
I looked at your vlookup suggestion again and I think I'm not explaining myself well enough. So here goes again. (Please keep in mind that I’m new to VBA!) I have a function that calculates a sliding commission fee, here's the code: Public Tier1 As Double Public Tier2 As Double Public Tier3 As Double Public Tier4 As Double Public Tier5 As Double Sub SetTier() Tier1 = Worksheets("Fee schedule").Range("D3").Value Tier2 = Worksheets("Fee schedule").Range("E3").Value Tier3 = Worksheets("Fee schedule").Range("F3").Value Tier4 = Worksheets("Fee schedule").Range("G3").Value Tier5 = Worksheets("Fee schedule").Range("H3").Value End Sub Function fee(Assets) ' calculates annual management fee SetTier 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 I originally got the commission code from Walkenbach's book and have altered it to suit my needs (w/ the help of Tom Ogilvy) The problem is that I need the Public Tier's to reference different numbers. For example: Client A tier 1 = 0.05 tier 2 = 0.04 tier 3 = 0.03 tier 4 = 0.02 tier 5 = 0.01 Client B tier 1 = 0.045 tier 2 = 0.035 tier 3 = 0.025 tier 4 = 0.015 tier 5 = 0.005 Client C ... There are multiple clients and they can all have different fees Tier's. I have thought of a couple of ways to get around this, which is to name a different function for each client i.e. clientAfee(), clientBfee(), etc and hard code each respective tier in the VBA I also thought I could start of with a standard fee and have the function reference a cell to see if a discount is applied and therefore get the required %. How I imagine this is: A1(discount %) = 0.005 B1(name) = client B C1(standard fee %) = 0.05 D1(tier1 %) = 0.045 E1(fee calc) = 22,500 In this case the fee is not 25,000(500k*0.05) but instead has taken into account the discount of 0.005 and calc. 22,500 (500k*0.045). I plan to have one workbook for all clients w/ 4 quarterly worksheets. I hope I’ve made myself clear. Thanks for all your help. Rob *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
direction needed
Hi Rob
haven't forgotten you .. but probably won't be able to get to this until the weekend or so ... Cheers JulieD "robert burger" wrote in message ... Good morning JulieD, I looked at your vlookup suggestion again and I think I'm not explaining myself well enough. So here goes again. (Please keep in mind that I'm new to VBA!) I have a function that calculates a sliding commission fee, here's the code: Public Tier1 As Double Public Tier2 As Double Public Tier3 As Double Public Tier4 As Double Public Tier5 As Double Sub SetTier() Tier1 = Worksheets("Fee schedule").Range("D3").Value Tier2 = Worksheets("Fee schedule").Range("E3").Value Tier3 = Worksheets("Fee schedule").Range("F3").Value Tier4 = Worksheets("Fee schedule").Range("G3").Value Tier5 = Worksheets("Fee schedule").Range("H3").Value End Sub Function fee(Assets) ' calculates annual management fee SetTier 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 I originally got the commission code from Walkenbach's book and have altered it to suit my needs (w/ the help of Tom Ogilvy) The problem is that I need the Public Tier's to reference different numbers. For example: Client A tier 1 = 0.05 tier 2 = 0.04 tier 3 = 0.03 tier 4 = 0.02 tier 5 = 0.01 Client B tier 1 = 0.045 tier 2 = 0.035 tier 3 = 0.025 tier 4 = 0.015 tier 5 = 0.005 Client C ... There are multiple clients and they can all have different fees Tier's. I have thought of a couple of ways to get around this, which is to name a different function for each client i.e. clientAfee(), clientBfee(), etc and hard code each respective tier in the VBA I also thought I could start of with a standard fee and have the function reference a cell to see if a discount is applied and therefore get the required %. How I imagine this is: A1(discount %) = 0.005 B1(name) = client B C1(standard fee %) = 0.05 D1(tier1 %) = 0.045 E1(fee calc) = 22,500 In this case the fee is not 25,000(500k*0.05) but instead has taken into account the discount of 0.005 and calc. 22,500 (500k*0.045). I plan to have one workbook for all clients w/ 4 quarterly worksheets. I hope I've made myself clear. Thanks for all your help. Rob *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
direction needed
JulieD,
I was thinking over the weekend...would it be possible to attach an offset cell (in addition to the active cell) to the function that can be referenced by the function itself? This way I could use the one function but always have a different discount number in the offset cell. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
some help or direction on using excel | Excel Worksheet Functions | |||
Need Direction | New Users to Excel | |||
Need direction | Excel Discussion (Misc queries) | |||
Direction | Excel Discussion (Misc queries) | |||
I need direction | Excel Discussion (Misc queries) |