Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup or other?
I want to return a value that is most distant from another value as in either
a postive or negative direction (higher or lower than fixed value. In real life, 37 degrees celsius is normal body temperature. I have 2 columns of data (Column C and D) where I have the highest and lowest body temp values of the day. But want to return whichever one is furthest from 37 degrees. So if C2 has 38.6 and D2 has 33.6, I'd want to return 33.6. If the temperatures are evenly different from 37.0 degrees (e.g. 36.0 and 38.0), then I'd want to return the lower one (though that is less critical). with respect to C2 and D2, there is no order to temp (either higher or lower of 2 temps may go first). Help is appreciated. anand |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup or other?
=IF(ABS(C2-37)<ABS(D2-37),D2,IF(ABS(D2-37)<ABS(C2-37),C2,MIN(C2,D2)))
-- David Biddulph "anand" wrote in message ... I want to return a value that is most distant from another value as in either a postive or negative direction (higher or lower than fixed value. In real life, 37 degrees celsius is normal body temperature. I have 2 columns of data (Column C and D) where I have the highest and lowest body temp values of the day. But want to return whichever one is furthest from 37 degrees. So if C2 has 38.6 and D2 has 33.6, I'd want to return 33.6. If the temperatures are evenly different from 37.0 degrees (e.g. 36.0 and 38.0), then I'd want to return the lower one (though that is less critical). with respect to C2 and D2, there is no order to temp (either higher or lower of 2 temps may go first). Help is appreciated. anand |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |