Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combing an if then statement and an offset cell?
Good evening all,
After a full week of searching and trying on my own I give up! I know what I want but I can't write it, it's very frustrating!! If anyone has some time to help me it would be appreciated. Below is my commission model. I want to be able to add an if then statement to it. The catch is, I want the if then statement to look at an offset cell besides the active function cell and if it has a number (discount) in it then apply, if not carry on as normal. 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 The if statement will allow me to have a variable fee since I have more than one client and they can all have different fee's. If I have not made myself clear or if you think there is a better way of doing this please say so. Thanks again. 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
|
|||
|
|||
Combing an if then statement and an offset cell?
Robert,
The easist thing I can think of to save you using the 'if then' statement is just simply alter your formula to deal with the discount. Just add 1*(1-x) to deal with the discount where x is the discount. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combing an if then statement and an offset cell?
x = ActiveCell.Offset(0, -1).Value
If isNumeric(x) then fee = fee * (1 - x) End If On Sun, 12 Sep 2004 16:06:32 -0700, robert burger wrote: Good evening all, After a full week of searching and trying on my own I give up! I know what I want but I can't write it, it's very frustrating!! If anyone has some time to help me it would be appreciated. Below is my commission model. I want to be able to add an if then statement to it. The catch is, I want the if then statement to look at an offset cell besides the active function cell and if it has a number (discount) in it then apply, if not carry on as normal. 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 The if statement will allow me to have a variable fee since I have more than one client and they can all have different fee's. If I have not made myself clear or if you think there is a better way of doing this please say so. Thanks again. RTB |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combing an if then statement and an offset cell?
David,
thanks for the reply. However, the discount can be different for each client, so i don't think hard coding will work. do you think what i'm requesting is even possible with VBA? thanks. Robert *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combing an if then statement and an offset cell?
Myrna,
Thanks for the reply. I think what you wrote is my answer! Since I'm new at VBA I'm not exactly sure how I would incorporate this into my code. Would you be kind enough to explain how? thanks, Robert *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combing an if then statement and an offset cell?
Robert yep it's possible.
I have to do a bit of guessing here since I can't see your model. Or I have totaly misinterpreted what you said (very likely in my case) Col A Col B 1 NAME Discount 2 Fred 20% I take it you are processing the data for each indivudal So something like (note I haven't tested this) Sub Define_Data_To USe () Dim Name as String Dim Dis as double Dim i as integer 'asuming you have 100 clients to run the analysis on For i = 1 to 100 'to do simple offsetting to get the data With Worksheets("Data Set") Set Name = .cells (2,i) 'to change the offset change the 1 to the appropraite column or row number -1, as i is already 1) Set Dis = .cells(2,1+i) End With 'Once this is defined then simply pass the data you wish to use to the function) Function fee(Assets As "please define this as it will save you in the end", Name As String, Dis As Double) 'then rewite your function to include the discount rate 'then do what you want with the results. 'this means that each time you run the model you are defining the name and the discount rate to use for that individual You could also use a Vlookup function to set the Name and Dis variables by just changing the cloum number to return the data Any questions let me know David "robert burger" wrote in message ... David, thanks for the reply. However, the discount can be different for each client, so i don't think hard coding will work. do you think what i'm requesting is even possible with VBA? thanks. Robert *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combing an if then statement and an offset cell?
David,
Thanks for taking the time to help. David, i'm new at VBA and am having trouble understanding what your saying? I kinda get the first part but you lose me on: 'Once this is defined then simply pass the data you wish to use to the function) Function fee(Assets As "please define this as it will save you in the end", Name As String, Dis As Double) 'then rewite your function to include the discount rate 'then do what you want with the results. 'this means that each time you run the model you are defining the name and the discount rate to use for that individual fyi posted my fee model in the original post. Thanks, Robert *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combing an if then statement and an offset cell?
Robert
VBA is very subjective on how you do things. So yes you do get confused looking at other peoples suggestions. I would try something like this but there may be better suggestions out there. -------------------- Sub Name() 'Dim all your variables 'set what data you wish to look at With Worksheets("Data Set") 'range (3a) = name 'after loop is will be 4a,etc Set Name = .cells (2+i,1) 'discount for Name is in cell 3c, after it loops it will be 4c,etc Set Dis = .cells(2+i,3) 'find the fee 'send the data you need to analyse to your function and return the value FeeResult = fee(Assets, Dis) 'now paste the feeReult where you want it 'assumed that it will be pasted in same worksheet in 3d, after it loops result will be pasted into 4d,etc ..cells(2+i, 4) = FeeResult End With Next i End sub Function fee(Assets as double, Dis as double) ' calculates annual management fee SetTier Select Case Assets Case 1 To 499999.99 fee = (Assets * Tier1) * (1*(1-dis)) Case 500000 To 999999.99 fee = (500000 * Tier1 + (Assets - 500000) * Tier2) * (1*(1-dis)) " *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combing an if then statement and an offset cell?
David,
I don't think i'm making myself clear. Here's an 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) I need the function to look at B1 (discount%) and subract this amount, if any, from each tier%, then calc. the fee amount. 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 else has a suggestion for the best methodology, please feel free to comment. Thanks, Robert *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combing an if then statement and an offset cell?
I need to see your existing code and more about your layout. You have maybe
provided that in another response. I will look at it later today if someone else hasn't come up with your solution. BTW, I would use a VLOOKUP table for the amounts instead of hard-codeing in the VBA. That way, if the cutoffs change, you can just change the table without modifying the VBA code. On Sun, 12 Sep 2004 17:17:03 -0700, robert burger wrote: Myrna, Thanks for the reply. I think what you wrote is my answer! Since I'm new at VBA I'm not exactly sure how I would incorporate this into my code. Would you be kind enough to explain how? thanks, Robert *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combing an if then statement and an offset cell?
Robert,
I have sent you a file. Hope you get it David |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combing an if then statement and an offset cell?
Here's the way I would write the code.
Note that I pass the discount as argument. If you don't want to do that, remove the Discount argument and remove the apostrophes from the two lines that Dim the variable and set its value. I marked those two lines with <<< The reason I don't like fetching the discount from the worksheet is that it locks you into a particular layout on the worksheet: in this case it must be 2 columns to the left of the cell containing the formula. If you decide to move the column with the discount from the left of the assets to the right of the assets, the code will no longer work. Note that I handle the case where you give a discount that's greater than the percentages in one or more tiers. i.e. if you say the discount is 5%, the fee is $0. Option Explicit Option Base 0 Function Fee(Assets As Double, Discount As Double) As Double Dim AssetsInThisTier As Double Dim AssetsRemaining As Double Dim PctForThisTier As Double Dim Pcts As Variant Dim t As Long Dim Tiers As Variant Dim TotalFee As Double 'Dim Discount As Double '<<< 'Discount = Application.Caller.Offset(0, -2) '<<< Tiers = Array(0, 500000, 1000000, 2000000, 5000000) Pcts = Array(0.03, 0.025, 0.0225, 0.02, 0.0175) AssetsRemaining = Assets TotalFee = 0 For t = UBound(Tiers) To LBound(Tiers) Step -1 AssetsInThisTier = AssetsRemaining - Tiers(t) If AssetsInThisTier 0 Then PctForThisTier = Pcts(t) - Discount If PctForThisTier 0 Then TotalFee = TotalFee + AssetsInThisTier * PctForThisTier End If AssetsRemaining = AssetsRemaining - AssetsInThisTier End If Next t Fee = TotalFee End Function On Mon, 13 Sep 2004 07:44:28 -0700, robert burger wrote: David, I don't think i'm making myself clear. Here's an 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) I need the function to look at B1 (discount%) and subract this amount, if any, from each tier%, then calc. the fee amount. 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 else has a suggestion for the best methodology, please feel free to comment. Thanks, Robert *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combing an if then statement and an offset cell?
Myrna,
Thank you very much! I am absolutely amazed at the amount of time that you and others take to help people with VBA questions. You all should be commended it seems to be rare these days that people are so giving. Thanks again, Robert *** 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 | |||
Combing 2 lists of data | Excel Discussion (Misc queries) | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
Help with OFFSET statement needed | Excel Worksheet Functions | |||
Combing months in ONE Cell | Excel Discussion (Misc queries) | |||
Formula Help Combing two AND statements | Excel Worksheet Functions |