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? |
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? |
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? |
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? |
All times are GMT +1. The time now is 03:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com