Absolute Difference between Fixed number and Variable numbers

[DragonWood]

Greetings,


I am trying to determine the absolute difference between a reference and a number.


I have a formula that I need to use to determine the specifications of a given test on temperature sensitive material.


I have a meter that records a temperature mark from two different areas every 10 minutes. I get the results as a .csv file. The .csv file includes four cells that I need to use.


Once I’ve imported the .csv into my workbook, cells C4 and C5 will contain the maximum temperature recorded at each area.
Cells E4 and E5 will contain the minimum temperature recorded.


The base number is 20. (Everything is in degrees Celcius).


My formula needs to determine what the absolute difference between the base number and the recorded numbers is.


So, I need to figure out what the actual MAX temperature was and what the actual MIN temperature was. Then determine which of these was the farthest from 20.


For example, if the values of cells are:
C4 = 22.8°C
C5 = 21.8°C
E4 = 19.2°C
E5 = 19.5°C


Then with an absolute difference of 2.8 cell C4 would be the one with the value that is the farthest away from 20. So, in this case, it would be the one I want to use. However, that could change to any of the 4 cells with each import of the temperature log. For the sake of this post, let’s put the result of that in cell J6.
The final formula I need is

=6.4+(0.3*J6)

My question is how to I get J6 to be the absolute difference between 20 and the other cells?


I hope I explained this correctly.


Thanks.

 

[JonXL]

Does this work?

=MAX(ABS(C4-20),ABS(C5-20)ABS(E4-20),ABS(E5-20))

 

[DragonWood]

You forgot the comma before the third ABS, but otherwise, yes it works. Thanks.

 

[jtakw]

Hi,

Here's another way:

Excel 2010
	C	D	E	J
4	22.8		19.2
5	21.8		15.5
6				4.5
Sheet5

Cell Formulas
Range	Formula
J6	=MAX(MAX(C4:C5)-20,20-MIN(E4:E5))

 

[JonXL]

You forgot the comma before the third ABS, but otherwise, yes it works. Thanks.

Oops.

I typed it on my phone and now it's too late to go back and fix it.

Glad it works. Thank you for the feedback.