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?

Thanks in advance
ManuHuygheAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Ryan ChongBusiness 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
http://www.advantageengineering.com/fyi/289/advantageFYI289.php

sorry as not so knowing the multiple types of refrigerant.
ManuHuygheAuthor 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.
Saqib Husain, SyedEngineerCommented:
Can you upload some sample data in an excel file? Someone can help you formulate a lookup table to achieve your objective.
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

ManuHuygheAuthor 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.
Saqib Husain, SyedEngineerCommented:
One option is to interpolate between two values. If you are willing to go in that direction then upload sample data.
Saqib Husain, SyedEngineerCommented:
Another option is to do regression.

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

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

Open in new window


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

Saurabh..
R32-V2.xlsx
ManuHuygheAuthor 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)
Saurabh Singh TeotiaCommented:
Yeah..What answer you are looking for now when you enter 732??

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

Saurabh...
ManuHuygheAuthor 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?
Saurabh Singh TeotiaCommented:
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...
Saqib Husain, SyedEngineerCommented:
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
Danny ChildIT 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.
Danny ChildIT 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...
ManuHuygheAuthor Commented:
@DanCh99

Indeed, this will be accurate enough.
I just don't know how write all this different functions in one VBA code.
Danny ChildIT ManagerCommented:
is it essential that it's VBA, not just a formula?
ManuHuygheAuthor 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

Open in new window

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 ;-)

Thanks in advance.
R410a-v2.xlsm
Danny ChildIT 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

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ManuHuygheAuthor Commented:
Thanks, it works perfect!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.