Combing an if then statement and an offset cell?

[robert burger]

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

 

[David Adamson]

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.

 

[Myrna Larson]

x = ActiveCell.Offset(0, -1).Value
If isNumeric(x) then
fee = fee * (1 - x)
End If

 

[robert burger]

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

 

[robert burger]

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

 

[David Adamson]

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]

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

 

[David Adamson]

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))



















"

 

[robert burger]

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

 

[Myrna Larson]

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.

 

[David Adamson]

Robert,

I have sent you a file.

Hope you get it

David

 

[Myrna Larson]

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

 

[robert burger]

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