Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"Between" function?
Is there such a thing? I'm working on a spreadsheet with a scoring matrix
that looks like this: A B C D E Low High Score QScore EndScore 0 1.00 1 3.56 4 1.01 2.00 2 2.01 3.00 3 3.01 4.00 4 4.01 5.00 5 Col A is the low end of the range, Col B is the high end of the range. I'm looking to take D2, compare it to the table above and return the appropriate score from Col C into Col E. So essentially I am looking to take the value in Col D, find out which range it falls within, and return the appropriate value from Col C. I can't seem to find a function that will allow me to take the value from Col D, find which range it's "between" and return the right value. Make sense? Thanks in advance for your help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"Between" function?
How about
=VLOOKUP(D2,A1:C6,3) -- HTH RP "fpd833" wrote in message ... Is there such a thing? I'm working on a spreadsheet with a scoring matrix that looks like this: A B C D E Low High Score QScore EndScore 0 1.00 1 3.56 4 1.01 2.00 2 2.01 3.00 3 3.01 4.00 4 4.01 5.00 5 Col A is the low end of the range, Col B is the high end of the range. I'm looking to take D2, compare it to the table above and return the appropriate score from Col C into Col E. So essentially I am looking to take the value in Col D, find out which range it falls within, and return the appropriate value from Col C. I can't seem to find a function that will allow me to take the value from Col D, find which range it's "between" and return the right value. Make sense? Thanks in advance for your help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"Between" function?
No go, this wont work unless the QScore in ColD is an exact match to the low
or high end of the range. In the example I provided, the person received a QScore of 3.56 (ColD)....this is between 3.01 and 4.0 (ColA and ColB) so it should return the number 4 from ColC in to ColE. Thank you for the help Bob! "Bob Phillips" wrote: How about =VLOOKUP(D2,A1:C6,3) -- HTH RP "fpd833" wrote in message ... Is there such a thing? I'm working on a spreadsheet with a scoring matrix that looks like this: A B C D E Low High Score QScore EndScore 0 1.00 1 3.56 4 1.01 2.00 2 2.01 3.00 3 3.01 4.00 4 4.01 5.00 5 Col A is the low end of the range, Col B is the high end of the range. I'm looking to take D2, compare it to the table above and return the appropriate score from Col C into Col E. So essentially I am looking to take the value in Col D, find out which range it falls within, and return the appropriate value from Col C. I can't seem to find a function that will allow me to take the value from Col D, find which range it's "between" and return the right value. Make sense? Thanks in advance for your help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"Between" function?
"fpd833" wrote...
No go, this wont work unless the QScore in ColD is an exact match to the low or high end of the range. Wrong. With no 4th argument, VLOOKUP matches the largest value in the first column of its 2nd argument less than or equal to its 1st argument. Try reading online help for functions suggested by others before you blithely dismiss them. In the example I provided, the person received a QScore of 3.56 (ColD)....this is between 3.01 and 4.0 (ColA and ColB) so it should return the number 4 from ColC in to ColE. Why don't you just *TRY* Bob's formula? What's the worst that could happen other than proving you don't know how VLOOKUP works? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"Between" function?
Harlan,
I didnt dismiss the advice (blithely or otherwise). I actually did try Bobs suggestion and received an error. In the end the error was mine, as I had not noticed Bob had left off the 4th argument. For this I apologize. I will strive to be as infallible as you in the future. Nowhere in this string did I proclaim to be an expert on how a VLOOKUP works, or anything else for that matter. If I were an expert I would have no reason to be posting in this newsgroup. I would like to thank you for taking time out of your busy life to peruse this newsgroup and clarify issues for the less educated. Despite your reply being presumptuous, insulting and rude; this issue is much clearer for me now. It is very comforting to know when a novice like me reaches out for help that there are fine dilettantes such as yourself providing the subject matter expertise. Very eloquent and reassuring. Maybe in the future you could provide those you decide to grace with your wisdom with a modicum of respect. Whats the worst that could happen? Someone may be appreciative and thankful? Neither happened in this case. Your Excel skills may be finely tuned, but your people skills leave a lot to be desired. Kudos to you. My true thanks go out to Bob Phillips. You sir, are a gentleman and a scholar. Maybe Harlan will glean a small sliver of your tact and understanding. Thanks! "Harlan Grove" wrote: "fpd833" wrote... No go, this wont work unless the QScore in ColD is an exact match to the low or high end of the range. Wrong. With no 4th argument, VLOOKUP matches the largest value in the first column of its 2nd argument less than or equal to its 1st argument. Try reading online help for functions suggested by others before you blithely dismiss them. In the example I provided, the person received a QScore of 3.56 (ColD)....this is between 3.01 and 4.0 (ColA and ColB) so it should return the number 4 from ColC in to ColE. Why don't you just *TRY* Bob's formula? What's the worst that could happen other than proving you don't know how VLOOKUP works? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"Between" function?
"fpd833" wrote...
I didn't dismiss the advice (blithely or otherwise). I actually did try Bob's suggestion and received an error. In the end the error was mine, as I had not noticed Bob had left off the 4th argument. For this I apologize. I will strive to be as infallible as you in the future. .... Yes, do try. Perhaps just copying formulas from newsgroup postings and pasting into Excel. That way your own manifestly inadequate typing, er, skills wouldn't be overtaxed. Maybe in the future you could provide those you decide to grace with your wisdom with a modicum of respect. What's the worst that could happen? .... When they DESERVE respect, they get respect. Then there are those like you. Reread your response: "fpd833" wrote... No go, this wont work unless the QScore in ColD is an exact match to the low or high end of the range. .... Don't presume you know the answer. Here's evidence you don't. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date & Time | New Users to Excel | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions | |||
Find a Function to use accross different worksheets | Excel Worksheet Functions |