| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup
I am looking for either a macro or a function that will return a certain value.
ie: % With in Limits test 100 99 98 97 96 ....... 1 0.9 0.8 0.6 0.3 0.1 2 1.8 1.7 1.5 1.3 1.0 3 2.9 2.8 2.4 2.2 2.1 Test #2 find number 1.5 Return value 98 I hope someone can help me. Eric |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
lookup
With your posted data list in A1:F4
For an exact match... Try this: A8: (Test to use....eg 2) A9: (value to find...eg 1.5) This formula returns the top row score associated with the A9 score for the A8 test. =INDEX(A1:F1,MATCH(A9,OFFSET(A1:F1,MATCH(A8,A2:A4, 0),),0)) NOTE: if the A9 value is not found....the formula returns an error. Does that help? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Eric" wrote in message ... I am looking for either a macro or a function that will return a certain value. ie: % With in Limits test 100 99 98 97 96 ....... 1 0.9 0.8 0.6 0.3 0.1 2 1.8 1.7 1.5 1.3 1.0 3 2.9 2.8 2.4 2.2 2.1 Test #2 find number 1.5 Return value 98 I hope someone can help me. Eric |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
lookup
it does help but if it isn't an exact match I still need it to return a
value. between the two numbers. ie: if it's between 98 and 99 I need it to mathmatically calculate if it would be 98.something...... Eric "Ron Coderre" wrote: With your posted data list in A1:F4 For an exact match... Try this: A8: (Test to use....eg 2) A9: (value to find...eg 1.5) This formula returns the top row score associated with the A9 score for the A8 test. =INDEX(A1:F1,MATCH(A9,OFFSET(A1:F1,MATCH(A8,A2:A4, 0),),0)) NOTE: if the A9 value is not found....the formula returns an error. Does that help? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Eric" wrote in message ... I am looking for either a macro or a function that will return a certain value. ie: % With in Limits test 100 99 98 97 96 ....... 1 0.9 0.8 0.6 0.3 0.1 2 1.8 1.7 1.5 1.3 1.0 3 2.9 2.8 2.4 2.2 2.1 Test #2 find number 1.5 Return value 98 I hope someone can help me. Eric |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
lookup
OK...I understand.....
You want to search a particular test for a certain value. If that exact value is found, then return the associated top row value. If that exact value is not found, then you want to interpolate the top row values. Again...using your posted data list in A1:F4 and A8: (Test to use....eg 2) A9: (value to find...eg 1.5) Try this ARRAY FORMULA, committed with CTRL+SHIFT+ENTER, instead of just ENTER: A10: =INDEX(A1:F1,MATCH(A9,OFFSET(A1:F1,MATCH(A8,A2:A4, 0),),-1))- (INDEX(OFFSET(B1:F1,MATCH(A8,A2:A4,0),),MATCH(A9,O FFSET(B1:F1, MATCH(A8,A2:A4,0),),-1))-A9)/SUM(OFFSET(INDEX(B3:F3,MATCH(A9, OFFSET(B1:F1,MATCH(A8,A2:A4,0),),-1)),,,1,2)*{1,-1}) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Eric" wrote in message ... it does help but if it isn't an exact match I still need it to return a value. between the two numbers. ie: if it's between 98 and 99 I need it to mathmatically calculate if it would be 98.something...... Eric "Ron Coderre" wrote: With your posted data list in A1:F4 For an exact match... Try this: A8: (Test to use....eg 2) A9: (value to find...eg 1.5) This formula returns the top row score associated with the A9 score for the A8 test. =INDEX(A1:F1,MATCH(A9,OFFSET(A1:F1,MATCH(A8,A2:A4, 0),),0)) NOTE: if the A9 value is not found....the formula returns an error. Does that help? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Eric" wrote in message ... I am looking for either a macro or a function that will return a certain value. ie: % With in Limits test 100 99 98 97 96 ....... 1 0.9 0.8 0.6 0.3 0.1 2 1.8 1.7 1.5 1.3 1.0 3 2.9 2.8 2.4 2.2 2.1 Test #2 find number 1.5 Return value 98 I hope someone can help me. Eric |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
lookup
Ron,
This formula isn't working......I Can't get it to work using Alt+Shift+Enter or by just using enter. Can you help? Please "Ron Coderre" wrote: OK...I understand..... You want to search a particular test for a certain value. If that exact value is found, then return the associated top row value. If that exact value is not found, then you want to interpolate the top row values. Again...using your posted data list in A1:F4 and A8: (Test to use....eg 2) A9: (value to find...eg 1.5) Try this ARRAY FORMULA, committed with CTRL+SHIFT+ENTER, instead of just ENTER: A10: =INDEX(A1:F1,MATCH(A9,OFFSET(A1:F1,MATCH(A8,A2:A4, 0),),-1))- (INDEX(OFFSET(B1:F1,MATCH(A8,A2:A4,0),),MATCH(A9,O FFSET(B1:F1, MATCH(A8,A2:A4,0),),-1))-A9)/SUM(OFFSET(INDEX(B3:F3,MATCH(A9, OFFSET(B1:F1,MATCH(A8,A2:A4,0),),-1)),,,1,2)*{1,-1}) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Eric" wrote in message ... it does help but if it isn't an exact match I still need it to return a value. between the two numbers. ie: if it's between 98 and 99 I need it to mathmatically calculate if it would be 98.something...... Eric "Ron Coderre" wrote: With your posted data list in A1:F4 For an exact match... Try this: A8: (Test to use....eg 2) A9: (value to find...eg 1.5) This formula returns the top row score associated with the A9 score for the A8 test. =INDEX(A1:F1,MATCH(A9,OFFSET(A1:F1,MATCH(A8,A2:A4, 0),),0)) NOTE: if the A9 value is not found....the formula returns an error. Does that help? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Eric" wrote in message ... I am looking for either a macro or a function that will return a certain value. ie: % With in Limits test 100 99 98 97 96 ....... 1 0.9 0.8 0.6 0.3 0.1 2 1.8 1.7 1.5 1.3 1.0 3 2.9 2.8 2.4 2.2 2.1 Test #2 find number 1.5 Return value 98 I hope someone can help me. Eric |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
lookup
That should be.......CTRL+Shift+Enter
Not ALT+Shift+Enter Does that make it work? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Eric" wrote in message ... Ron, This formula isn't working......I Can't get it to work using Alt+Shift+Enter or by just using enter. Can you help? Please "Ron Coderre" wrote: OK...I understand..... You want to search a particular test for a certain value. If that exact value is found, then return the associated top row value. If that exact value is not found, then you want to interpolate the top row values. Again...using your posted data list in A1:F4 and A8: (Test to use....eg 2) A9: (value to find...eg 1.5) Try this ARRAY FORMULA, committed with CTRL+SHIFT+ENTER, instead of just ENTER: A10: =INDEX(A1:F1,MATCH(A9,OFFSET(A1:F1,MATCH(A8,A2:A4, 0),),-1))- (INDEX(OFFSET(B1:F1,MATCH(A8,A2:A4,0),),MATCH(A9,O FFSET(B1:F1, MATCH(A8,A2:A4,0),),-1))-A9)/SUM(OFFSET(INDEX(B3:F3,MATCH(A9, OFFSET(B1:F1,MATCH(A8,A2:A4,0),),-1)),,,1,2)*{1,-1}) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Eric" wrote in message ... it does help but if it isn't an exact match I still need it to return a value. between the two numbers. ie: if it's between 98 and 99 I need it to mathmatically calculate if it would be 98.something...... Eric "Ron Coderre" wrote: With your posted data list in A1:F4 For an exact match... Try this: A8: (Test to use....eg 2) A9: (value to find...eg 1.5) This formula returns the top row score associated with the A9 score for the A8 test. =INDEX(A1:F1,MATCH(A9,OFFSET(A1:F1,MATCH(A8,A2:A4, 0),),0)) NOTE: if the A9 value is not found....the formula returns an error. Does that help? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Eric" wrote in message ... I am looking for either a macro or a function that will return a certain value. ie: % With in Limits test 100 99 98 97 96 ....... 1 0.9 0.8 0.6 0.3 0.1 2 1.8 1.7 1.5 1.3 1.0 3 2.9 2.8 2.4 2.2 2.1 Test #2 find number 1.5 Return value 98 I hope someone can help me. Eric |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
lookup
No....It was a type O
Sorry....either way it doesn't work Eric "Ron Coderre" wrote: That should be.......CTRL+Shift+Enter Not ALT+Shift+Enter Does that make it work? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Eric" wrote in message ... Ron, This formula isn't working......I Can't get it to work using Alt+Shift+Enter or by just using enter. Can you help? Please "Ron Coderre" wrote: OK...I understand..... You want to search a particular test for a certain value. If that exact value is found, then return the associated top row value. If that exact value is not found, then you want to interpolate the top row values. Again...using your posted data list in A1:F4 and A8: (Test to use....eg 2) A9: (value to find...eg 1.5) Try this ARRAY FORMULA, committed with CTRL+SHIFT+ENTER, instead of just ENTER: A10: =INDEX(A1:F1,MATCH(A9,OFFSET(A1:F1,MATCH(A8,A2:A4, 0),),-1))- (INDEX(OFFSET(B1:F1,MATCH(A8,A2:A4,0),),MATCH(A9,O FFSET(B1:F1, MATCH(A8,A2:A4,0),),-1))-A9)/SUM(OFFSET(INDEX(B3:F3,MATCH(A9, OFFSET(B1:F1,MATCH(A8,A2:A4,0),),-1)),,,1,2)*{1,-1}) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Eric" wrote in message ... it does help but if it isn't an exact match I still need it to return a value. between the two numbers. ie: if it's between 98 and 99 I need it to mathmatically calculate if it would be 98.something...... Eric "Ron Coderre" wrote: With your posted data list in A1:F4 For an exact match... Try this: A8: (Test to use....eg 2) A9: (value to find...eg 1.5) This formula returns the top row score associated with the A9 score for the A8 test. =INDEX(A1:F1,MATCH(A9,OFFSET(A1:F1,MATCH(A8,A2:A4, 0),),0)) NOTE: if the A9 value is not found....the formula returns an error. Does that help? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Eric" wrote in message ... I am looking for either a macro or a function that will return a certain value. ie: % With in Limits test 100 99 98 97 96 ....... 1 0.9 0.8 0.6 0.3 0.1 2 1.8 1.7 1.5 1.3 1.0 3 2.9 2.8 2.4 2.2 2.1 Test #2 find number 1.5 Return value 98 I hope someone can help me. Eric |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
lookup
Hey Ron,
I was wondering if you had any other solutions to the problem since the formula isn't working? Please let me know what you think might be the problem. Every time I run the formula I get a Div/0 or a num error. Any help would be appreciated. Thank you ERic "Eric" wrote: No....It was a type O Sorry....either way it doesn't work Eric "Ron Coderre" wrote: That should be.......CTRL+Shift+Enter Not ALT+Shift+Enter Does that make it work? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Eric" wrote in message ... Ron, This formula isn't working......I Can't get it to work using Alt+Shift+Enter or by just using enter. Can you help? Please "Ron Coderre" wrote: OK...I understand..... You want to search a particular test for a certain value. If that exact value is found, then return the associated top row value. If that exact value is not found, then you want to interpolate the top row values. Again...using your posted data list in A1:F4 and A8: (Test to use....eg 2) A9: (value to find...eg 1.5) Try this ARRAY FORMULA, committed with CTRL+SHIFT+ENTER, instead of just ENTER: A10: =INDEX(A1:F1,MATCH(A9,OFFSET(A1:F1,MATCH(A8,A2:A4, 0),),-1))- (INDEX(OFFSET(B1:F1,MATCH(A8,A2:A4,0),),MATCH(A9,O FFSET(B1:F1, MATCH(A8,A2:A4,0),),-1))-A9)/SUM(OFFSET(INDEX(B3:F3,MATCH(A9, OFFSET(B1:F1,MATCH(A8,A2:A4,0),),-1)),,,1,2)*{1,-1}) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Eric" wrote in message ... it does help but if it isn't an exact match I still need it to return a value. between the two numbers. ie: if it's between 98 and 99 I need it to mathmatically calculate if it would be 98.something...... Eric "Ron Coderre" wrote: With your posted data list in A1:F4 For an exact match... Try this: A8: (Test to use....eg 2) A9: (value to find...eg 1.5) This formula returns the top row score associated with the A9 score for the A8 test. =INDEX(A1:F1,MATCH(A9,OFFSET(A1:F1,MATCH(A8,A2:A4, 0),),0)) NOTE: if the A9 value is not found....the formula returns an error. Does that help? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Eric" wrote in message ... I am looking for either a macro or a function that will return a certain value. ie: % With in Limits test 100 99 98 97 96 ....... 1 0.9 0.8 0.6 0.3 0.1 2 1.8 1.7 1.5 1.3 1.0 3 2.9 2.8 2.4 2.2 2.1 Test #2 find number 1.5 Return value 98 I hope someone can help me. Eric |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
lookup
Hi, Eric
I put a working model of the formula at this link: http://www.savefile.com/files/1264101 Please open that file (contains no macros) and let me know if it works for you. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Eric" wrote in message ... Hey Ron, I was wondering if you had any other solutions to the problem since the formula isn't working? Please let me know what you think might be the problem. Every time I run the formula I get a Div/0 or a num error. Any help would be appreciated. Thank you ERic "Eric" wrote: No....It was a type O Sorry....either way it doesn't work Eric "Ron Coderre" wrote: That should be.......CTRL+Shift+Enter Not ALT+Shift+Enter Does that make it work? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Eric" wrote in message ... Ron, This formula isn't working......I Can't get it to work using Alt+Shift+Enter or by just using enter. Can you help? Please "Ron Coderre" wrote: OK...I understand..... You want to search a particular test for a certain value. If that exact value is found, then return the associated top row value. If that exact value is not found, then you want to interpolate the top row values. Again...using your posted data list in A1:F4 and A8: (Test to use....eg 2) A9: (value to find...eg 1.5) Try this ARRAY FORMULA, committed with CTRL+SHIFT+ENTER, instead of just ENTER: A10: =INDEX(A1:F1,MATCH(A9,OFFSET(A1:F1,MATCH(A8,A2:A4, 0),),-1))- (INDEX(OFFSET(B1:F1,MATCH(A8,A2:A4,0),),MATCH(A9,O FFSET(B1:F1, MATCH(A8,A2:A4,0),),-1))-A9)/SUM(OFFSET(INDEX(B3:F3,MATCH(A9, OFFSET(B1:F1,MATCH(A8,A2:A4,0),),-1)),,,1,2)*{1,-1}) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Eric" wrote in message ... it does help but if it isn't an exact match I still need it to return a value. between the two numbers. ie: if it's between 98 and 99 I need it to mathmatically calculate if it would be 98.something...... Eric "Ron Coderre" wrote: With your posted data list in A1:F4 For an exact match... Try this: A8: (Test to use....eg 2) A9: (value to find...eg 1.5) This formula returns the top row score associated with the A9 score for the A8 test. =INDEX(A1:F1,MATCH(A9,OFFSET(A1:F1,MATCH(A8,A2:A4, 0),),0)) NOTE: if the A9 value is not found....the formula returns an error. Does that help? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Eric" wrote in message ... I am looking for either a macro or a function that will return a certain value. ie: % With in Limits test 100 99 98 97 96 ....... 1 0.9 0.8 0.6 0.3 0.1 2 1.8 1.7 1.5 1.3 1.0 3 2.9 2.8 2.4 2.2 2.1 Test #2 find number 1.5 Return value 98 I hope someone can help me. Eric |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
lookup
Eric
I revisited the formula I posted and just thunked myself on the head! THIS much shorter, regular formula seems to do what you indicated: A10: =FORECAST(A9,OFFSET(B1:F1,,MATCH(A9,OFFSET(B1:F1, MATCH(A8,A2:A4,0),),-1)-1,1,2),OFFSET(B1:F1,MATCH(A8,A2:A4,0), MATCH(A9,OFFSET(B1:F1,MATCH(A8,A2:A4,0),),-1)-1,1,2)) Also, I updated the file I posted to reflect that change: http://www.savefile.com/files/1264101 I hope that helps. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Ron Coderre" wrote in message ... Hi, Eric I put a working model of the formula at this link: http://www.savefile.com/files/1264101 Please open that file (contains no macros) and let me know if it works for you. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Eric" wrote in message ... Hey Ron, I was wondering if you had any other solutions to the problem since the formula isn't working? Please let me know what you think might be the problem. Every time I run the formula I get a Div/0 or a num error. Any help would be appreciated. Thank you ERic "Eric" wrote: No....It was a type O Sorry....either way it doesn't work Eric "Ron Coderre" wrote: That should be.......CTRL+Shift+Enter Not ALT+Shift+Enter Does that make it work? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Eric" wrote in message ... Ron, This formula isn't working......I Can't get it to work using Alt+Shift+Enter or by just using enter. Can you help? Please "Ron Coderre" wrote: OK...I understand..... You want to search a particular test for a certain value. If that exact value is found, then return the associated top row value. If that exact value is not found, then you want to interpolate the top row values. Again...using your posted data list in A1:F4 and A8: (Test to use....eg 2) A9: (value to find...eg 1.5) Try this ARRAY FORMULA, committed with CTRL+SHIFT+ENTER, instead of just ENTER: A10: =INDEX(A1:F1,MATCH(A9,OFFSET(A1:F1,MATCH(A8,A2:A4, 0),),-1))- (INDEX(OFFSET(B1:F1,MATCH(A8,A2:A4,0),),MATCH(A9,O FFSET(B1:F1, MATCH(A8,A2:A4,0),),-1))-A9)/SUM(OFFSET(INDEX(B3:F3,MATCH(A9, OFFSET(B1:F1,MATCH(A8,A2:A4,0),),-1)),,,1,2)*{1,-1}) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Eric" wrote in message ... it does help but if it isn't an exact match I still need it to return a value. between the two numbers. ie: if it's between 98 and 99 I need it to mathmatically calculate if it would be 98.something...... Eric "Ron Coderre" wrote: With your posted data list in A1:F4 For an exact match... Try this: A8: (Test to use....eg 2) A9: (value to find...eg 1.5) This formula returns the top row score associated with the A9 score for the A8 test. =INDEX(A1:F1,MATCH(A9,OFFSET(A1:F1,MATCH(A8,A2:A4, 0),),0)) NOTE: if the A9 value is not found....the formula returns an error. Does that help? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Eric" wrote in message ... I am looking for either a macro or a function that will return a certain value. ie: % With in Limits test 100 99 98 97 96 ....... 1 0.9 0.8 0.6 0.3 0.1 2 1.8 1.7 1.5 1.3 1.0 3 2.9 2.8 2.4 2.2 2.1 Test #2 find number 1.5 Return value 98 I hope someone can help me. Eric |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
lookup
Well....this is the latest in a series of final solutions :\
Lori (of these forums) used another approach in a recent post that I like much better. Adapted for your situation (and implemented in the file I made available), the formula is: =PERCENTILE(B1:F1,PERCENTRANK(OFFSET(B1:F1,MATCH(A 8,A2:A4,0),,,),A9,20)) http://www.savefile.com/files/1264101 -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Ron Coderre" wrote in message ... Eric I revisited the formula I posted and just thunked myself on the head! THIS much shorter, regular formula seems to do what you indicated: A10: =FORECAST(A9,OFFSET(B1:F1,,MATCH(A9,OFFSET(B1:F1, MATCH(A8,A2:A4,0),),-1)-1,1,2),OFFSET(B1:F1,MATCH(A8,A2:A4,0), MATCH(A9,OFFSET(B1:F1,MATCH(A8,A2:A4,0),),-1)-1,1,2)) Also, I updated the file I posted to reflect that change: http://www.savefile.com/files/1264101 I hope that helps. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Ron Coderre" wrote in message ... Hi, Eric I put a working model of the formula at this link: http://www.savefile.com/files/1264101 Please open that file (contains no macros) and let me know if it works for you. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Eric" wrote in message ... Hey Ron, I was wondering if you had any other solutions to the problem since the formula isn't working? Please let me know what you think might be the problem. Every time I run the formula I get a Div/0 or a num error. Any help would be appreciated. Thank you ERic "Eric" wrote: No....It was a type O Sorry....either way it doesn't work Eric "Ron Coderre" wrote: That should be.......CTRL+Shift+Enter Not ALT+Shift+Enter Does that make it work? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Eric" wrote in message ... Ron, This formula isn't working......I Can't get it to work using Alt+Shift+Enter or by just using enter. Can you help? Please "Ron Coderre" wrote: OK...I understand..... You want to search a particular test for a certain value. If that exact value is found, then return the associated top row value. If that exact value is not found, then you want to interpolate the top row values. Again...using your posted data list in A1:F4 and A8: (Test to use....eg 2) A9: (value to find...eg 1.5) Try this ARRAY FORMULA, committed with CTRL+SHIFT+ENTER, instead of just ENTER: A10: =INDEX(A1:F1,MATCH(A9,OFFSET(A1:F1,MATCH(A8,A2:A4, 0),),-1))- (INDEX(OFFSET(B1:F1,MATCH(A8,A2:A4,0),),MATCH(A9,O FFSET(B1:F1, MATCH(A8,A2:A4,0),),-1))-A9)/SUM(OFFSET(INDEX(B3:F3,MATCH(A9, OFFSET(B1:F1,MATCH(A8,A2:A4,0),),-1)),,,1,2)*{1,-1}) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Eric" wrote in message ... it does help but if it isn't an exact match I still need it to return a value. between the two numbers. ie: if it's between 98 and 99 I need it to mathmatically calculate if it would be 98.something...... Eric "Ron Coderre" wrote: With your posted data list in A1:F4 For an exact match... Try this: A8: (Test to use....eg 2) A9: (value to find...eg 1.5) This formula returns the top row score associated with the A9 score for the A8 test. =INDEX(A1:F1,MATCH(A9,OFFSET(A1:F1,MATCH(A8,A2:A4, 0),),0)) NOTE: if the A9 value is not found....the formula returns an error. Does that help? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Eric" wrote in message ... I am looking for either a macro or a function that will return a certain value. ie: % With in Limits test 100 99 98 97 96 ....... 1 0.9 0.8 0.6 0.3 0.1 2 1.8 1.7 1.5 1.3 1.0 3 2.9 2.8 2.4 2.2 2.1 Test #2 find number 1.5 Return value 98 I hope someone can help me. Eric |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lookup help. lookup result based on data in 2 columns | Excel Worksheet Functions | |||
| LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
| Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
| Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
| Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
Linear Mode
