Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to return a single value for a range of numbers
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 A 29,000 - 30,000 Grade Level B 25,000 - 28,999 Grade Level C 23,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 and = but I cannot get it to recognize the range. thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to return a single value for a range of numbers
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 "Tami" wrote: 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 A 29,000 - 30,000 Grade Level B 25,000 - 28,999 Grade Level C 23,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 and = but I cannot get it to recognize the range. thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to return a single value for a range of numbers
One way
=VLOOKUP(A2,{0,"C";24999,"B";29000,"A"},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 A you 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 "Tami" wrote in message ... 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 A 29,000 - 30,000 Grade Level B 25,000 - 28,999 Grade Level C 23,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 and = but I cannot get it to recognize the range. thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to return a single value for a range of numbers
Hi,
Have your matrix of salary values and Grade level built like this: Lowest value in salary range Grade level 23,000 C 25,000 B 29,000 A Then, use this formula in column B =VLOOKUP(A1,RANGE,2,TRUE) where Range refers to the range where you have your salary & grade matrix. Regards Govind. Tami wrote: 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 A 29,000 - 30,000 Grade Level B 25,000 - 28,999 Grade Level C 23,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 and = but I cannot get it to recognize the range. thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return Range of Numerical Values in Single Column based on Frequency Percentage | Excel Worksheet Functions | |||
How do I use a range of numbers in an if statement? | Excel Worksheet Functions | |||
How do i set up a range of numbers which prompts at a particular n | Excel Discussion (Misc queries) | |||
return a value based on a range | Excel Worksheet Functions | |||
get a count of numbers whose value falls within a given range | Excel Worksheet Functions |