If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
I think it is a lookup question???
I have a range of numbers - effect sizes AD2:AD47. In another column AP, I
want write a formula that will look up the percentile conversion within the same worksheet. The percentile conversion list the estimate effect size in Column A - AQ2:AQ22 and then the percentile is in AR2:AR22. So in other words, if the effect size is 0.4 in AD2 then AP 4 would look in AQ:AQ22 and say found the value in AQ19 and then return the value that appears in AR19. So my question is . . . how would I write the formula? |
#2
|
|||
|
|||
I think it is a lookup question???
That would be a VLOOKUP
So in AP, you would have.. =VLOOKUP(AD2,AQ2:AR22,2) If the AQ values are in ascending order, it will return the closest value that is less than or equal to your AD2 value. If you do not want to show a valid result without an exact match, add ,0 to the end of the vlookup above. "LOC" wrote: I have a range of numbers - effect sizes AD2:AD47. In another column AP, I want write a formula that will look up the percentile conversion within the same worksheet. The percentile conversion list the estimate effect size in Column A - AQ2:AQ22 and then the percentile is in AR2:AR22. So in other words, if the effect size is 0.4 in AD2 then AP 4 would look in AQ:AQ22 and say found the value in AQ19 and then return the value that appears in AR19. So my question is . . . how would I write the formula? |
#3
|
|||
|
|||
I think it is a lookup question???
Thank you - I flipped my data to be ascending and it works like a charm. I
could not fill it as it changed the array but I was able to quickly fix the array in the filled cells so it returned the correct data. WOW! I am impressed. Thanks again "Sean Timmons" wrote: That would be a VLOOKUP So in AP, you would have.. =VLOOKUP(AD2,AQ2:AR22,2) If the AQ values are in ascending order, it will return the closest value that is less than or equal to your AD2 value. If you do not want to show a valid result without an exact match, add ,0 to the end of the vlookup above. "LOC" wrote: I have a range of numbers - effect sizes AD2:AD47. In another column AP, I want write a formula that will look up the percentile conversion within the same worksheet. The percentile conversion list the estimate effect size in Column A - AQ2:AQ22 and then the percentile is in AR2:AR22. So in other words, if the effect size is 0.4 in AD2 then AP 4 would look in AQ:AQ22 and say found the value in AQ19 and then return the value that appears in AR19. So my question is . . . how would I write the formula? |
#4
|
|||
|
|||
I think it is a lookup question???
That's Excel for ya! :-) Happy it worked for you!
"LOC" wrote: Thank you - I flipped my data to be ascending and it works like a charm. I could not fill it as it changed the array but I was able to quickly fix the array in the filled cells so it returned the correct data. WOW! I am impressed. Thanks again "Sean Timmons" wrote: That would be a VLOOKUP So in AP, you would have.. =VLOOKUP(AD2,AQ2:AR22,2) If the AQ values are in ascending order, it will return the closest value that is less than or equal to your AD2 value. If you do not want to show a valid result without an exact match, add ,0 to the end of the vlookup above. "LOC" wrote: I have a range of numbers - effect sizes AD2:AD47. In another column AP, I want write a formula that will look up the percentile conversion within the same worksheet. The percentile conversion list the estimate effect size in Column A - AQ2:AQ22 and then the percentile is in AR2:AR22. So in other words, if the effect size is 0.4 in AD2 then AP 4 would look in AQ:AQ22 and say found the value in AQ19 and then return the value that appears in AR19. So my question is . . . how would I write the formula? |
Thread Tools | |
Display Modes | |
|
|