Hi Jim,

try something like this

Function AWGCVAR(Avg_G,TierRange as range)
' Calulates the variance in AWGC as determined by the appropiate
tier. YTD
' TierRange is the address of the tier F743:F747

Dim vTier as variant

vTier=TierRange.value

Select Case Avg_G
Case Is <= 3000: AWGCVAR = -Vtier(1,1) + Avg_G
Case Is <= 4000: AWGCVAR = -Vtier(2,1) + Avg_G
Case Is <= 5000: AWGCVAR = -Vtier(3,1) + Avg_G
Case Is <= 6000: AWGCVAR = -Vtier(4,1) + Avg_G
Case Is 6000: AWGCVAR = -Vtier(5,1) + Avg_G
End Select
End Function

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Jim" wrote in message
...
Mike -

Here is one of the functions:

Function AWGCVAR(Avg_G)
' 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_G
Case Is <= 3000: AWGCVAR = -Tier1 + Avg_G
Case Is <= 4000: AWGCVAR = -Tier2 + Avg_G
Case Is <= 5000: AWGCVAR = -Tier3 + Avg_G
Case Is <= 6000: AWGCVAR = -tier4 + Avg_G
Case Is 6000: AWGCVAR = -tier5 + Avg_G
End Select
End Function

This function works well for me, but when I open Excel now it just
returns Avg_G as the function's result. When I redo the function it
works fine and then I AutoFill the rest of the cells. It is just a
pain to do it everytime I open up the file. Not sure if it has to do
with the fact I have several array functions and several user-defined
functions on this worksheet. Also, I am trying to write a macro for
this file and it could be causing some problems, maybe when I am
debugging?