Formula help

[Patrick C. Simonds]

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

 

[T. Valko]

See this:

http://contextures.com/xlFunctions02.html

 

[Sandy Mann]

Try:

=IF(A1="","",LOOKUP(FLOOR(A1,10),{10,20,30,40,50,60;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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk