direction needed

[robert burger]

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

 

[JulieD]

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]

JulieD,
Thanks for your response!
However, i would like to do this as a VBA. Any ideas?
Thanks,
Rob

 

[JulieD]

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]

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

 

[JulieD]

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]

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.