Function Trouble: Not sure how to correctly reference my variable

[Jim]

I am trying to calculate the variance between the average number of
guests per week and the actual guests who have visited. I have 5
tiers of guest counts and they are all listed in the piece of code I
submitted.

The code seems to work, but when I spot check a few of the
calculations they don’t add up. Specifically, I believe the formula
stops working at Tier2. For some reason, (probably obvious to one of
the more experienced users) anything greater than 4000 is calculated
against the Tier2 reference. Any suggestions? Please don’t be shy, I
understand this group is brutally honest. If this doesn’t make since,
let me know and I will clarify. This is irritating.

Function AWGCVAR(Avg_Guests_Wk)
' Calulates the variance in AWGC as determined by the appropiate
tier. YTD
Dim Tier1 As Double, Tier2 As Double
Dim Tier3 As Double, tier4 As Double
Dim tier5 As Double
Tier1 = Range("F$743")
Tier2 = Range("F$744")
Tier3 = Range("F$745")
tier4 = Range("F$746")
tier5 = Range("F$747")
Select Case Avg_Guests_Wk
Case Is < 3000: AWGCVAR = -Tier1 + Avg_Guests_Wk
Case Is > 3000 <= 4000: AWGCVAR = -Tier2 + Avg_Guests_Wk
Case Is > 4000 <= 5000: AWGCVAR = -Tier3 + Avg_Guests_Wk
Case Is > 5000 <= 6000: AWGCVAR = -tier4 + Avg_Guests_Wk
Case Is > 6000: AWGCVAR = -tier5 + Avg_Guests_Wk
End Select
End Function

 

[Jim Thomlinson]

Your case statement is wrong. You do not need (or want to include) the
greater than critera as the value must be greater than if it cot to that
point in the case statement... so something like this...

Function AWGCVAR(Avg_Guests_Wk)
' Calulates the variance in AWGC as determined by the appropiate
tier. YTD
Dim Tier1 As Double, Tier2 As Double
Dim Tier3 As Double, tier4 As Double
Dim tier5 As Double
Tier1 = Range("F$743")
Tier2 = Range("F$744")
Tier3 = Range("F$745")
tier4 = Range("F$746")
tier5 = Range("F$747")
Select Case Avg_Guests_Wk
Case Is <= 3000: AWGCVAR = -Tier1 + Avg_Guests_Wk
Case Is <= 4000: AWGCVAR = -Tier2 + Avg_Guests_Wk
Case Is <= 5000: AWGCVAR = -Tier3 + Avg_Guests_Wk
Case Is <= 6000: AWGCVAR = -tier4 + Avg_Guests_Wk
Case Is > 6000: AWGCVAR = -tier5 + Avg_Guests_Wk
End Select
End Function