Formula for manipulating temperature ranges difference

[damon_l]

Good Day,

I'm busy with quite a complex energy savings engineering calculation in Excel.

I have two columns with temperature ranges.

I want to a develop a formula that will say if the difference between the two temperature ranges is less than 2 then leave the value as is and if not then subtract a specific amount so the result will be a difference of 2 then populate a new cell with whatever that value was that was deleted.

An example to show what I'm looking for.

Assume column A has a temp of 70 F and Column B has a temp of 78, by having Column C subtract the two values to get the difference in this instance the difference is 8. I need to subtract 6 from the 8 to get a 2 but I also wish to populate another cell with the 6 as I need this value for the 2nd part of the calculation. The end result will be a different value for each cell inputted as these temperature ranges fluctuate.

Is there a way to automate this as a I have few thousand cells to go through with the same calculation.

Maybe there's an easier way to go about the above?

Thanks
Regards
Damon

 

[SteveO59L]

you will need to use code, are you ok with that?

 

[etaf]

Excel Workbook
	A	B	C
5			Value to be 2 difference
6	70	78	6
7	70	78	6
8	85	78	5
9	70	78	6
10	70	65	3
11	70	78	6
12	70	19	49
13	70	88	16
14	70	85	13
15	70	78	6
Sheet2

 

[Special-K99]

Temperatures in A1 and B1

in C1
=ABS(A1-B1)

in D1
=IF(ABS(A1-B1)<2,0,MAX(A1,B1)-MIN(A1,B1)-2)

C1 is the difference between the two temparatures
D1 is either the difference minus 2 (in your example 6) or 0 if the difference is less than 2.

 

[thisoldman]

I don't know if the sign of the difference is significant.

Does this help?

[TABLE="class: grid"]
<tbody>[TR]
[TD]°1[/TD]
[TD]°2[/TD]
[TD]Diff set to <=2[/TD]
[TD]Diff If[/TD]
[/TR]
[TR]
[TD="align: right"]70[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]76[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]72[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]70[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]70[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

Formula in C2:
=(ABS(B2-A2)>=2)*(ABS(B2-A2)-2)

Formula in D2:
=(ABS(B2-A2)>=2)*ABS(B2-A2)

 

[damon_l]

Thanks Wayne, that formula works.

I can now use the output of column C to work out the next part of the equation!