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