# Excel Spreadsheet formula for Pressure/Temperature conversion for refrigerants

Dear all,

I'm searching for the Pressure/Temperature conversion formula's for multiple refrigerants.
I googled it already for  more than 5 hours but can not find what i'm looking for, all i can find are app's or add-in's that doesn't work.

Does somebody here know of existing spreadsheets to convert refrigerant pressure into temperature?

###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Business Systems Analyst , ex-Senior Application EngineerCommented:
probably you can refer to this URL and do a reverse conversion based on the values provided?

Refrigerant Pressure - Temperature Chart

sorry as not so knowing the multiple types of refrigerant.
Author Commented:
This is indeed the data that i want, but i need the formula to calculate it.
Because i have to insert it into a tool i'm creating in excel.
EngineerCommented:
Author Commented:
I'm searching for the formula's to do the calculation, because when i use the data from conversion tables then excel will not give a temperature value when the user enters a pressure value in between 2 given values in the table.
EngineerCommented:
One option is to interpolate between two values. If you are willing to go in that direction then upload sample data.
EngineerCommented:
Another option is to do regression.

If you want "The" formula then you really should have asked this question in Maths and Sciences.
Author Commented:
Thanks for the help.
Attached you find a sample workbook for the refrigerant R32
R32-V2.xlsx
Commented:
You can use this formula..Assuming you enter KPA in I8..

``````=IFERROR(LOOKUP(I7,B2:B51,A2:A51),"-")
``````

Enclosed is your workbook for reference where you changed KPA and it will show celcius accordingly..

Saurabh..
R32-V2.xlsx
Author Commented:
Thanks,

Is there a way to calculate the temperature between 2 given values?
Because it's possible that the user measures a pressure between the 2 given values.

For example
Given values:   713kpa = 0°C and 767kpa = 2°C
Measured value 732Kpa (With this formula the temperature mensioned stays 0°C)
Commented:
Yeah..What answer you are looking for now when you enter 732??

Saurabh...
Author Commented:
+/-0.8°C
Commented:
And what is the logic for that?? How you come up with this number??

Saurabh...
Author Commented:
Now I guessed because I do not have a formula to calculate it

713kpa = 0°C
732kpa= +/-0.8°C
767kpa = 2°C

Doesn't excel has a function that when we entre a range of values like in the sample workbook, that it can create a curve (like in a chart) based on the given values.
And that we can consult the values in between based on the curve created?
Commented:
Excel has bunch of formulas but you need to write the logic for the formula and thats what i'm asking you..

Because if i calculate manually..

767-713= 54

Now if i divide :- 2/54 =0.37

Now if i so further- =732-713 = 19

19*0.37 -= 0.7'C

Thats why now asking what is the calculation or logic to for this??

Saurabh...
EngineerCommented:
Using regression you can use these formulas to get one from the other within a reasonable precision

=0.001868*A2^3+0.3217*A2^2+25.869*A2+711.94

= -3.8992E-19*B2^6 + 0.0000000000000051289*B2^5 - 0.000000000026867*B2^4 + 0.000000072331*B2^3 - 0.00011061*B2^2 + 0.11791*B2 - 47.782
IT ManagerCommented:
Your best bet is to follow as Saurab suggests, which is working on the assumption that there's effectively a straight (not curved) line between the 2 data points in question - ie between 0 and 2, in your example.

If you can tolerate this assumption, his logic on finding how far your sample point is between the 2 pressures, and then using this ratio to find out how far it is between the 2 related temperatures will work.

If all you're doing at present is manually reading temps from the curve on the chart, i'd say this was more than accurate enough.  However, if you're calculating the next Mars mission for NASA, we may need to think again...

You should be able to do this with INDEX and MATCH to find the nearest pressures above and below your inputted value.
IT ManagerCommented:
by the way, I don't follow your "+/-" in the example above.  I get +0.7 (compared to your 0.8) and that's it...
Author Commented:
@DanCh99

Indeed, this will be accurate enough.
I just don't know how write all this different functions in one VBA code.
IT ManagerCommented:
is it essential that it's VBA, not just a formula?
Author Commented:
Hi DanCh99,

I tried to write the VBA code but i receive always an error about the Match function.
FYI - It's the first time that i use the Index and Match function.

Can you have a look at what's wrong with the code i wrote?

This is the code i wrote:
``````Dim PresFind As String
Dim a As Double, b As Double, c As Double, d As Double, e As Double, f As Double, g As Double, h As Double
Dim R410aRange1 As Range, p As Range, p1 As Range, p2 As Range

Private Sub TextBox1_Change()

Set R410aRange1 = Sheet2.Range("I4:I203")

PresFind = Me.TextBox1.Value

Set p = R410aRange1.Find(PresFind, LookIn:=xlValues, LookAt:=xlWhole)

If Not p Is Nothing Then
Me.TextBox3.Value = p.Offset(0, 1).Value
Else
p1 = Application.WorksheetFunction.Index(R410aRange1, Application.WorksheetFunction.Match(PresFind, R410aRange1, 1))
p2 = Application.WorksheetFunction.Index(R410aRange1, Application.WorksheetFunction.Match(PresFind, R410aRange1, -1))

a = p1.Value
b = p2.Value
c = b - a
d = 2 / c
e = p1.Offset(0, 1).Value
f = p2.Offset(0, 1).Value
g = e - d
h = g * d

Me.TextBox3.Value = PresFind + h
End If

End Sub
``````
You find also my test workbook attached.

If you have remarks about how i write code's they are always welcome, because i'm not very experienced in VBA and I’m learning by trial and error ;-)

R410a-v2.xlsm
IT ManagerCommented:
Here's how it could be done with formulae...  first row breaks it down.  2nd row does it all in 1 cell (cells G3 to M3 could be deleted, and it would still work).

Try a sample pressure of 235 and see what you get.
M--Personal-ee---pressure-INDEX-MAT.xlsx

Experts Exchange Solution brought to you by