I need a formula to return a value in a column (say column B) for a number in
Column A based on a range: EX: As in grading, A = 90-100, B=80-90, etc.
I have a column of data of various salaries. If the salary is between x and
x, i need to return a corresponding letter set up by our salary grades.example: here are the values
Grade Level A29,000 - 30,000
Grade Level B25,000 - 28,999
Grade Level C23,000 - 24,9000
Column A Column B
Salary Grade Level
$29,500.00 A
$26,500.00 B
$25,100.00 B
$24,000.00 C
I want a formula in column B, that returns the corresponding value from
column A in column B. I tried IF statements using gt; and = but I cannot get
it to recognize the range. thanks!
If you create a table of mimimum salary and associated grade, sorted so that
the lowest salary is at the top of the list, then this is a perfect
application of the vlookup function. Suppose your table is in the range
$A$1:$B$6, with salary in column A and associated grade in column B. Then if
you have an employee's salary in A20; the associated grade is found in B20
using the formula =vlookup(a20,$A$1:$B$6,2).
--Bruce
quot;Tamiquot; wrote:
gt; I need a formula to return a value in a column (say column B) for a number in
gt; Column A based on a range: EX: As in grading, A = 90-100, B=80-90, etc.
gt; I have a column of data of various salaries. If the salary is between x and
gt; x, i need to return a corresponding letter set up by our salary grades.
gt;
gt;
gt; example: here are the values
gt; Grade Level A29,000 - 30,000
gt; Grade Level B25,000 - 28,999
gt; Grade Level C23,000 - 24,9000
gt;
gt; Column A Column B
gt; Salary Grade Level
gt; $29,500.00 A
gt; $26,500.00 B
gt; $25,100.00 B
gt; $24,000.00 C
gt;
gt; I want a formula in column B, that returns the corresponding value from
gt; column A in column B. I tried IF statements using gt; and = but I cannot get
gt; it to recognize the range. thanks!
One way
=VLOOKUP(A2,{0,quot;Cquot;;24999,quot;Bquot;;29000,quot;Aquot;},2)
where A2 is the first salary (29500)
you can create a table that in your example should look like
0 C
24999 B
29000 Ayou can refer to that table as well
=VLOOKUP(A2,$E$2:$F$4,2)
copy down both formulas will return your desired result--
Regards,
Peo Sjoblom
Portland, Oregon
quot;Tamiquot; gt; wrote in message
...
gt;I need a formula to return a value in a column (say column B) for a number
gt;in
gt; Column A based on a range: EX: As in grading, A = 90-100, B=80-90, etc.
gt; I have a column of data of various salaries. If the salary is between x
gt; and
gt; x, i need to return a corresponding letter set up by our salary grades.
gt;
gt;
gt; example: here are the values
gt; Grade Level A 29,000 - 30,000
gt; Grade Level B 25,000 - 28,999
gt; Grade Level C 23,000 - 24,9000
gt;
gt; Column A Column B
gt; Salary Grade Level
gt; $29,500.00 A
gt; $26,500.00 B
gt; $25,100.00 B
gt; $24,000.00 C
gt;
gt; I want a formula in column B, that returns the corresponding value from
gt; column A in column B. I tried IF statements using gt; and = but I cannot
gt; get
gt; it to recognize the range. thanks!Hi,
Have your matrix of salary values and Grade level built like this:
Lowest value in salary range Grade level
23,000C
25,000B
29,000A
Then, use this formula in column B
=VLOOKUP(A1,RANGE,2,TRUE)
where Range refers to the range where you have your salary amp; grade matrix.
Regards
Govind.Tami wrote:
gt; I need a formula to return a value in a column (say column B) for a number in
gt; Column A based on a range: EX: As in grading, A = 90-100, B=80-90, etc.
gt; I have a column of data of various salaries. If the salary is between x and
gt; x, i need to return a corresponding letter set up by our salary grades.
gt;
gt;
gt; example: here are the values
gt; Grade Level A29,000 - 30,000
gt; Grade Level B25,000 - 28,999
gt; Grade Level C23,000 - 24,9000
gt;
gt; Column A Column B
gt; Salary Grade Level
gt; $29,500.00 A
gt; $26,500.00 B
gt; $25,100.00 B
gt; $24,000.00 C
gt;
gt; I want a formula in column B, that returns the corresponding value from
gt; column A in column B. I tried IF statements using gt; and = but I cannot get
gt; it to recognize the range. thanks!
Column A based on a range: EX: As in grading, A = 90-100, B=80-90, etc.
I have a column of data of various salaries. If the salary is between x and
x, i need to return a corresponding letter set up by our salary grades.example: here are the values
Grade Level A29,000 - 30,000
Grade Level B25,000 - 28,999
Grade Level C23,000 - 24,9000
Column A Column B
Salary Grade Level
$29,500.00 A
$26,500.00 B
$25,100.00 B
$24,000.00 C
I want a formula in column B, that returns the corresponding value from
column A in column B. I tried IF statements using gt; and = but I cannot get
it to recognize the range. thanks!
If you create a table of mimimum salary and associated grade, sorted so that
the lowest salary is at the top of the list, then this is a perfect
application of the vlookup function. Suppose your table is in the range
$A$1:$B$6, with salary in column A and associated grade in column B. Then if
you have an employee's salary in A20; the associated grade is found in B20
using the formula =vlookup(a20,$A$1:$B$6,2).
--Bruce
quot;Tamiquot; wrote:
gt; I need a formula to return a value in a column (say column B) for a number in
gt; Column A based on a range: EX: As in grading, A = 90-100, B=80-90, etc.
gt; I have a column of data of various salaries. If the salary is between x and
gt; x, i need to return a corresponding letter set up by our salary grades.
gt;
gt;
gt; example: here are the values
gt; Grade Level A29,000 - 30,000
gt; Grade Level B25,000 - 28,999
gt; Grade Level C23,000 - 24,9000
gt;
gt; Column A Column B
gt; Salary Grade Level
gt; $29,500.00 A
gt; $26,500.00 B
gt; $25,100.00 B
gt; $24,000.00 C
gt;
gt; I want a formula in column B, that returns the corresponding value from
gt; column A in column B. I tried IF statements using gt; and = but I cannot get
gt; it to recognize the range. thanks!
One way
=VLOOKUP(A2,{0,quot;Cquot;;24999,quot;Bquot;;29000,quot;Aquot;},2)
where A2 is the first salary (29500)
you can create a table that in your example should look like
0 C
24999 B
29000 Ayou can refer to that table as well
=VLOOKUP(A2,$E$2:$F$4,2)
copy down both formulas will return your desired result--
Regards,
Peo Sjoblom
Portland, Oregon
quot;Tamiquot; gt; wrote in message
...
gt;I need a formula to return a value in a column (say column B) for a number
gt;in
gt; Column A based on a range: EX: As in grading, A = 90-100, B=80-90, etc.
gt; I have a column of data of various salaries. If the salary is between x
gt; and
gt; x, i need to return a corresponding letter set up by our salary grades.
gt;
gt;
gt; example: here are the values
gt; Grade Level A 29,000 - 30,000
gt; Grade Level B 25,000 - 28,999
gt; Grade Level C 23,000 - 24,9000
gt;
gt; Column A Column B
gt; Salary Grade Level
gt; $29,500.00 A
gt; $26,500.00 B
gt; $25,100.00 B
gt; $24,000.00 C
gt;
gt; I want a formula in column B, that returns the corresponding value from
gt; column A in column B. I tried IF statements using gt; and = but I cannot
gt; get
gt; it to recognize the range. thanks!Hi,
Have your matrix of salary values and Grade level built like this:
Lowest value in salary range Grade level
23,000C
25,000B
29,000A
Then, use this formula in column B
=VLOOKUP(A1,RANGE,2,TRUE)
where Range refers to the range where you have your salary amp; grade matrix.
Regards
Govind.Tami wrote:
gt; I need a formula to return a value in a column (say column B) for a number in
gt; Column A based on a range: EX: As in grading, A = 90-100, B=80-90, etc.
gt; I have a column of data of various salaries. If the salary is between x and
gt; x, i need to return a corresponding letter set up by our salary grades.
gt;
gt;
gt; example: here are the values
gt; Grade Level A29,000 - 30,000
gt; Grade Level B25,000 - 28,999
gt; Grade Level C23,000 - 24,9000
gt;
gt; Column A Column B
gt; Salary Grade Level
gt; $29,500.00 A
gt; $26,500.00 B
gt; $25,100.00 B
gt; $24,000.00 C
gt;
gt; I want a formula in column B, that returns the corresponding value from
gt; column A in column B. I tried IF statements using gt; and = but I cannot get
gt; it to recognize the range. thanks!
