Hello, I am new to VBA and I am trying to create a formula that will
reference my variable "AW" and based on the range of numbers it falls
between returns the formula you see listed in my IF...then
statements. Any help would be appreciated.

Right now, my function always returns a value of zero. Thanks.



Function AVGCASEVAR(Avg_Cases_Wk, AW)
' Calulates the variance in Cases of beer used per week versus an
average as determined by the appropiate tier.
Dim Tier1 As Long, Tier2 As Long
Dim Tier3 As Long, Tier4 As Long
Dim Tier5 As Long
Dim Avg_Cases_Week As Long
Tier1 = Range("$H$743")
Tier2 = Range("$H$744")
Tier3 = Range("$H$745")
Tier4 = Range("$H$746")
Tier5 = Range("$H$747")
If AW < 3000 Then AVGCASEVAR = Tier1 - Avg_Cases_Wk
[ElseIf AW 3000 AND <= 4000 Then [AVGCASEVAR = Tier2 -
Avg_Cases_Wk]]
[ElseIf AW 4000 AND <= 5000 Then [AVGCASEVAR = Tier3 -
Avg_Cases_Wk]]
[ElseIf AW 5000 AND <= 6000 Then [AVGCASEVAR = Tier4
- Avg_Cases_Wk]]
[Else: [AVGCASEVAR = Tier5 - Avg_Cases_Wk]]
End Function