Absolute Difference between Fixed number and Variable numbers

DragonWood

Board Regular
Joined
Oct 17, 2010
Messages
97
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
Code:
=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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Does this work?

Code:
=MAX(ABS(C4-20),ABS(C5-20)ABS(E4-20),ABS(E5-20))
 
Upvote 0
Hi,

Here's another way:


Excel 2010
CDEJ
422.819.2
521.815.5
64.5
Sheet5
Cell Formulas
RangeFormula
J6=MAX(MAX(C4:C5)-20,20-MIN(E4:E5))
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top