Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I have a spreadsheet that in Column A has a department and column B has a
wage. I need to look up this wage in a national survey file, that will return in column C the nat'l percentile, for that department. Is this possible? Column A Column B Column C Acctg $95,243 65th percentile Mrktg Nursing The problme I have ran into with my formula's, is that the survey file will have of course a range of numbers, not my exact #. Therefore, my vlookup formula is not working. How can I write a formula that would do this? Thanks in advance. |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi,
when you said national survey do you want to pull an average, could you send an example of your survey "Sr Accountant" wrote: I have a spreadsheet that in Column A has a department and column B has a wage. I need to look up this wage in a national survey file, that will return in column C the nat'l percentile, for that department. Is this possible? Column A Column B Column C Acctg $95,243 65th percentile Mrktg Nursing The problme I have ran into with my formula's, is that the survey file will have of course a range of numbers, not my exact #. Therefore, my vlookup formula is not working. How can I write a formula that would do this? Thanks in advance. |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I am wanting to pull the percentile that my wage falls into. Here is an
example of my survey file. Col A = Depart Col B= Median % Col C = 10th %tile Col D =11th %tile....Etc.... Accounting $64,987 $32,765 $33,456 Does this help? I am not sure how to write a forumla to look for the department name and then the range of what my wage is. I do not need the median %, I am truly looking for the %tile. If my wages were $33,000 for Acctg, I would want the formula to return 10th %tile. Thank you. "Eduardo" wrote: Hi, when you said national survey do you want to pull an average, could you send an example of your survey "Sr Accountant" wrote: I have a spreadsheet that in Column A has a department and column B has a wage. I need to look up this wage in a national survey file, that will return in column C the nat'l percentile, for that department. Is this possible? Column A Column B Column C Acctg $95,243 65th percentile Mrktg Nursing The problme I have ran into with my formula's, is that the survey file will have of course a range of numbers, not my exact #. Therefore, my vlookup formula is not working. How can I write a formula that would do this? Thanks in advance. |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi,
Which is the criteria to select 10th in the case you enter a salary of 33000 and no 11th from column D since the salary there is 33456 "Sr Accountant" wrote: I am wanting to pull the percentile that my wage falls into. Here is an example of my survey file. Col A = Depart Col B= Median % Col C = 10th %tile Col D =11th %tile....Etc.... Accounting $64,987 $32,765 $33,456 Does this help? I am not sure how to write a forumla to look for the department name and then the range of what my wage is. I do not need the median %, I am truly looking for the %tile. If my wages were $33,000 for Acctg, I would want the formula to return 10th %tile. Thank you. "Eduardo" wrote: Hi, when you said national survey do you want to pull an average, could you send an example of your survey "Sr Accountant" wrote: I have a spreadsheet that in Column A has a department and column B has a wage. I need to look up this wage in a national survey file, that will return in column C the nat'l percentile, for that department. Is this possible? Column A Column B Column C Acctg $95,243 65th percentile Mrktg Nursing The problme I have ran into with my formula's, is that the survey file will have of course a range of numbers, not my exact #. Therefore, my vlookup formula is not working. How can I write a formula that would do this? Thanks in advance. |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I entered the 10th %tile, because my wage of $33,000 falls between $32,765 -
$33,456. Is this what you're asking? "Eduardo" wrote: Hi, Which is the criteria to select 10th in the case you enter a salary of 33000 and no 11th from column D since the salary there is 33456 "Sr Accountant" wrote: I am wanting to pull the percentile that my wage falls into. Here is an example of my survey file. Col A = Depart Col B= Median % Col C = 10th %tile Col D =11th %tile....Etc.... Accounting $64,987 $32,765 $33,456 Does this help? I am not sure how to write a forumla to look for the department name and then the range of what my wage is. I do not need the median %, I am truly looking for the %tile. If my wages were $33,000 for Acctg, I would want the formula to return 10th %tile. Thank you. "Eduardo" wrote: Hi, when you said national survey do you want to pull an average, could you send an example of your survey "Sr Accountant" wrote: I have a spreadsheet that in Column A has a department and column B has a wage. I need to look up this wage in a national survey file, that will return in column C the nat'l percentile, for that department. Is this possible? Column A Column B Column C Acctg $95,243 65th percentile Mrktg Nursing The problme I have ran into with my formula's, is that the survey file will have of course a range of numbers, not my exact #. Therefore, my vlookup formula is not working. How can I write a formula that would do this? Thanks in advance. |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi
I am leaving soon try I assumed that the survey is in sheet1 and you are entering the information in sheet2. in sheet2 Cell C1 enter. Column A contains the departments and column B you enter the salary =INDEX(sheet1!b1:G1,AND(MATCH(A1,sheet1!A1:A1000,1 ),MATCH(B1,Sheet1!B1:E1000,0))) "Eduardo" wrote: Hi, Which is the criteria to select 10th in the case you enter a salary of 33000 and no 11th from column D since the salary there is 33456 "Sr Accountant" wrote: I am wanting to pull the percentile that my wage falls into. Here is an example of my survey file. Col A = Depart Col B= Median % Col C = 10th %tile Col D =11th %tile....Etc.... Accounting $64,987 $32,765 $33,456 Does this help? I am not sure how to write a forumla to look for the department name and then the range of what my wage is. I do not need the median %, I am truly looking for the %tile. If my wages were $33,000 for Acctg, I would want the formula to return 10th %tile. Thank you. "Eduardo" wrote: Hi, when you said national survey do you want to pull an average, could you send an example of your survey "Sr Accountant" wrote: I have a spreadsheet that in Column A has a department and column B has a wage. I need to look up this wage in a national survey file, that will return in column C the nat'l percentile, for that department. Is this possible? Column A Column B Column C Acctg $95,243 65th percentile Mrktg Nursing The problme I have ran into with my formula's, is that the survey file will have of course a range of numbers, not my exact #. Therefore, my vlookup formula is not working. How can I write a formula that would do this? Thanks in advance. |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Thank you. I will try this, and will post a reply if I need further
assistance. Have a great day. "Eduardo" wrote: Hi I am leaving soon try I assumed that the survey is in sheet1 and you are entering the information in sheet2. in sheet2 Cell C1 enter. Column A contains the departments and column B you enter the salary =INDEX(sheet1!b1:G1,AND(MATCH(A1,sheet1!A1:A1000,1 ),MATCH(B1,Sheet1!B1:E1000,0))) "Eduardo" wrote: Hi, Which is the criteria to select 10th in the case you enter a salary of 33000 and no 11th from column D since the salary there is 33456 "Sr Accountant" wrote: I am wanting to pull the percentile that my wage falls into. Here is an example of my survey file. Col A = Depart Col B= Median % Col C = 10th %tile Col D =11th %tile....Etc.... Accounting $64,987 $32,765 $33,456 Does this help? I am not sure how to write a forumla to look for the department name and then the range of what my wage is. I do not need the median %, I am truly looking for the %tile. If my wages were $33,000 for Acctg, I would want the formula to return 10th %tile. Thank you. "Eduardo" wrote: Hi, when you said national survey do you want to pull an average, could you send an example of your survey "Sr Accountant" wrote: I have a spreadsheet that in Column A has a department and column B has a wage. I need to look up this wage in a national survey file, that will return in column C the nat'l percentile, for that department. Is this possible? Column A Column B Column C Acctg $95,243 65th percentile Mrktg Nursing The problme I have ran into with my formula's, is that the survey file will have of course a range of numbers, not my exact #. Therefore, my vlookup formula is not working. How can I write a formula that would do this? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
lookup on values between ranges | Excel Discussion (Misc queries) | |||
Lookup one value and count in two ranges | Excel Discussion (Misc queries) | |||
Name ranges and Lookup | Excel Worksheet Functions | |||
Lookup Value between 2 ranges | Excel Worksheet Functions | |||
Ranges with in a LOOKUP | Excel Worksheet Functions |