Combine Custom Function Results

[Dale]

I have written a function with 3 IF statements for a tiered commission
calculation; is there any way to have the function result include all 3 IF
statement results as a sum?

 

[Phil]

(while I'm waiting for my answer, thought I would respond to yours)

I would use 4 seperate cells, one for each portion, and then a cell that
totals them.

You can always hide the 3 cells you dont want, or put them on another tab.

Otherwise you would need to use a function or subroutine / macro code to do
that.

-phil

 

[Dale]

That's what I have, a function Macro...and I'm not quite sure how to get it
to sum the three tiers within the function

 

[ytayta555]

Post your formula here

 

[Dale]

Sorry if this is long; I am only just learning function creation....

Public Function ATLN(ATLN_BASE, ATLN_SUM, MONTH_N)

Dim lngGrowthTier1 As Long
Dim lngGrowthTier2 As Long
Dim lngGrowthTier3 As Long
mlngMonthlyAccrual = 0
mlngMonthlyAccrual2 = 0
mlngMonthlyAccrual3 = 0

lngGrowthTier1 = 207995
lngGrowthTier2 = 407995
lngGrowthTier3 = 507995
msngGrowth1 = 0.03
msngGrowth2 = 0.04
msngGrowth3 = 0.05

If (ATLN_SUM / MONTH_N) * 12 > ATLN_BASE And (ATLN_SUM / MONTH_N) * 12 <
407996 Then

mlngMonthlyAccrual = (((ATLN_SUM / MONTH_N) * 12) - lngGrowthTier1) *
msngGrowth1

End If

If (ATLN_SUM / MONTH_N) * 12 > ATLN_BASE And (ATLN_SUM / MONTH_N) * 12 >=
407996 And (ATLN_SUM / MONTH_N) * 12 < 507996 Then

mlngMonthlyAccrual2 = (((ATLN_SUM / MONTH_N) * 12) - lngGrowthTier2) *
msngGrowth2

End If

If (ATLN_SUM / MONTH_N) * 12 > ATLN_BASE And (ATLN_SUM / MONTH_N) * 12 >=
507996 Then

mlngMonthlyAccrual3 = (((ATLN_SUM / MONTH_N) * 12) - lngGrowthTier3) *
msngGrowth3

End If

ATLN = mlngMonthlyAccrual + mlngMonthlyAccrual2 + mlngMonthlyAccrual3

End Function