| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can something like this be accomplished?
I have a workbook with two sheets.
Sheet 2 has data like: pt R5 R6 R7 R8 R9 010 76.0 58.1 51.3 45.8 37.5 020 81.9 61.9 54.7 49.3 44.1 030 95.0 90.0 87.5 85.0 82.9 040 70.2 57.3 51.0 45.8 41.6 etc.................... Sheet 1 contains for example a pt of 020 and a factor of 60.0. Is there anyway to pull the R value the would be either , < or = the factor in the row of 020? Say for example the factor of 60.0 in this case would return a R value of "<R6". TIA -- kr |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can something like this be accomplished?
It looks like you're looking for the closest match. If so, assuming
that on Sheet 2, A1:F5 contains the data, and that on Sheet1, A2 contains 020, and B2 contains 60, try... =INDEX(Sheet2!B1:F1,MATCH(MIN(ABS(INDEX(Sheet2!B2: F5,MATCH(A2,Sheet2!A2:A 5,0),0)-B2)),ABS(INDEX(Sheet2!B2:F5,MATCH(A2,Sheet2!A2:A5, 0),0)-B2),0)) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , kr wrote: I have a workbook with two sheets. Sheet 2 has data like: pt R5 R6 R7 R8 R9 010 76.0 58.1 51.3 45.8 37.5 020 81.9 61.9 54.7 49.3 44.1 030 95.0 90.0 87.5 85.0 82.9 040 70.2 57.3 51.0 45.8 41.6 etc.................... Sheet 1 contains for example a pt of 020 and a factor of 60.0. Is there anyway to pull the R value the would be either , < or = the factor in the row of 020? Say for example the factor of 60.0 in this case would return a R value of "<R6". TIA |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can something like this be accomplished?
Awesome! Thank You!!!!!!!!!!!!!!!!!!
-- kr "Domenic" wrote: It looks like you're looking for the closest match. If so, assuming that on Sheet 2, A1:F5 contains the data, and that on Sheet1, A2 contains 020, and B2 contains 60, try... =INDEX(Sheet2!B1:F1,MATCH(MIN(ABS(INDEX(Sheet2!B2: F5,MATCH(A2,Sheet2!A2:A 5,0),0)-B2)),ABS(INDEX(Sheet2!B2:F5,MATCH(A2,Sheet2!A2:A5, 0),0)-B2),0)) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , kr wrote: I have a workbook with two sheets. Sheet 2 has data like: pt R5 R6 R7 R8 R9 010 76.0 58.1 51.3 45.8 37.5 020 81.9 61.9 54.7 49.3 44.1 030 95.0 90.0 87.5 85.0 82.9 040 70.2 57.3 51.0 45.8 41.6 etc.................... Sheet 1 contains for example a pt of 020 and a factor of 60.0. Is there anyway to pull the R value the would be either , < or = the factor in the row of 020? Say for example the factor of 60.0 in this case would return a R value of "<R6". TIA |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
