What I have are a series of Work Codes (shown Below). All work codes in the
10 range are paid at one rate all work codes in the 20 range at another. I
need write a formula which looks only at the first number of the work code
and if it is 1 return the value $18.50, if it is 2 then return the value
$21.36.

I know I can use an If statement to look at each number but this is just a
representative example. I actually have 6 different pay rates associated
with over 30 work codes.

10
11
12

20
21
22

See this:

http://contextures.com/xlFunctions02.html

--
Biff
Microsoft Excel MVP


"Patrick C. Simonds" wrote in message
...
What I have are a series of Work Codes (shown Below). All work codes in
the 10 range are paid at one rate all work codes in the 20 range at
another. I need write a formula which looks only at the first number of
the work code and if it is 1 return the value $18.50, if it is 2 then
return the value $21.36.

I know I can use an If statement to look at each number but this is just a
representative example. I actually have 6 different pay rates associated
with over 30 work codes.

10
11
12

20
21
22

Try:

=IF(A1="","",LOOKUP(FLOOR(A1,10),{10,20,30,40,50,6 0;18.5,21.36,24.22,27.08,29.94,32.8}))

using your own cell reference for A1 and your own values for 18.5,21.36 etc.

However, to make it more flexable use:

=IF(A1="","",VLOOKUP(FLOOR(A1,10),$H$1:$I$6,2))

Where H1:I6 is a lookup table and H1:H6 is 10 to 60 respectively. This will
allow you to change the dollar values in the table without having to adjust
all the formulas.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Patrick C. Simonds" wrote in message
...
What I have are a series of Work Codes (shown Below). All work codes in
the 10 range are paid at one rate all work codes in the 20 range at
another. I need write a formula which looks only at the first number of
the work code and if it is 1 return the value $18.50, if it is 2 then
return the value $21.36.

I know I can use an If statement to look at each number but this is just a
representative example. I actually have 6 different pay rates associated
with over 30 work codes.

10
11
12

20
21
22