Excel Spreadsheet formula for Pressure/Temperature conversion for refrigerants

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.

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.

Can you upload some sample data in an excel file? Someone can help you formulate a lookup table to achieve your objective.

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.

One option is to interpolate between two values. If you are willing to go in that direction then upload sample data.

Another option is to do regression.

If you want "The" formula then you really should have asked this question in Maths and Sciences.

Thanks for the help.
Attached you find a sample workbook for the refrigerant R32
R32-V2.xlsx

You can use this formula..Assuming you enter KPA in I8..

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

Select allOpen in new window

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

Saurabh..
R32-V2.xlsx

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)

Yeah..What answer you are looking for now when you enter 732??

Saurabh...

+/-0.8°C

And what is the logic for that?? How you come up with this number??

Saurabh...

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?

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...

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

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.

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...

@DanCh99

Indeed, this will be accurate enough.
I just don't know how write all this different functions in one VBA code.

is it essential that it's VBA, not just a formula?

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

Select allOpen 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

Thanks, it works perfect!