vlookup or other?

[Guest]

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

 

[David Biddulph]

=IF(ABS(C2-37)<ABS(D2-37),D2,IF(ABS(D2-37)<ABS(C2-37),C2,MIN(C2,D2)))