Is it possible for excel to extrapolate missing data from a curve graph?

[born2dive00]

Ok I dont know if this is even possible. I have a curve graph with temperature and pressure variables, ranging from 32'F to 500'F, currently with 1 degree of separation. It will have psi from 0 to 250 with a 1 psi separation, However I need to make the curve with .01 degrees F and .01 psi of separation.

Needless to say, this would be a massive undertaking, so what I am wondering is can Excel extrapolate the missing data from the curve on a graph. i.e. known variable 1'F, and 2'f at 1psi, can it extrapolate the 1.01 1.02 1.03 etc to 2 for me???

I really dont want to manually enter in an estimate 1,250,000,000 data points, and no unfortunately there is no formula (at least that I can find that I can use to calculate each individual value on demand.

Please let me know and thank you

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.

[Pete_UK]

It's called interpolation if it is within the body of data, and linear interpolation is quite straightforward in principle. Imagine you want to calculate the value midway between two points on the x axis - you just take half of the difference in corresponding y values and add it to the first y value. In your case you will take 1/10th of the difference for each increment. If the increment in the x-axis is small, then linear interpolation can be quite accurate. However, given the number of calculations you claim that you need to do, it might be better to consider using a macro to do this, rather than formulae.

It would also help if you attached a sample Excel workbook - see the yellow banner at the top of the screen.

Hope this helps.

Pete

[born2dive00]

The initial spread sheet is incomplete, I still have data points to put in to make the curve, however here is a picture with some of the pKw plotted points. the data in between the plotted points is what I would like to extract once the excel sheet is finished.

[Glenn Kennedy]

That is a picture. We asked for a sheet.

[born2dive00]

The problem is this is a curve graph, and not linear, It MUST find the values on the curve, I get what you are saying connect the dots, so say your start value is 0 and your end value is 100, and if you divide it linearly in 3 equal parts you get each value of 33.333, However as this is a curved graph and any variation will cause compounded problems in the calculations at a later time. So i must find a way to get the values off the curve in a non linear way.

Ideas?

[Pete_UK]

If you have an equation for the curve, then you should be able to calculate any point on it.

Pete

[born2dive00]

The work sheet is a work in progress I have to add another 350 data points for a complete curve.... Ergo i put in the photo to illustrate what i am attempting to do.

[born2dive00]

Yes this would be ideal However there is no formula that i have been able to find out there that covers the pH of water at different pressures and tempratures. the only calculator i have found was this one http://www.energy.psu.edu/elc/kwcalculator.html . I have searched and searched, What the formula is I have NO clue.

if you click the top radio button in the imput option, T-P:pkw at given t-p and enter the temp and bar, it will kick back a pKw value (second box down on out put) for that temp and pressure.

If you have an equation for the curve, then you should be able to calculate any point on it.

Pete

[born2dive00]

Sorry I have said this wrong, If i put in the temp of say 130'c i want to kick back the pKw value where the temp line and the curve intersect.

[Pete_UK]

You are just drip-feeding us in dribs and drabs on what your problem is and what you are trying to achieve, so I'll duck out until things become a bit clearer.

Pete

[born2dive00]

Pete if you go to this website you will see what I am attempting to do, http://www.energy.psu.edu/elc/kwcalculator.html If you click the radio button t-p:pKw at a given t-P and enter any temprature in C at any bar pressure, in the outputs it will give you a pKw value for that pressure and temp.

NOW given that I can not find any formula out there that will give me the pH of water when the pKw, pKa, pkB, Kw, Ka, Kb values are UNKNOWN especially when taking into consideration the temprature and pressure variables.

Since I have no formula, I was going to manually use the above calculator and create a table of pressure and temp with the pKw value their calculator gives.

Now since the resulting pKw values are in a curve like the one above post, I am asking if excel can calculate the missing pKw values on the curve where the temp line intersects the curve.

I am not trying to drip feed anyone, it is just that my table is not yet finished, 250 more 'F to go.

You are just drip-feeding us in dribs and drabs on what your problem is and what you are trying to achieve, so I'll duck out until things become a bit clearer.

Pete

[born2dive00]

Pete unless you know of a way to get this website to work on an excel sheet, I have no formula...

You are just drip-feeding us in dribs and drabs on what your problem is and what you are trying to achieve, so I'll duck out until things become a bit clearer.

Pete

[Glenn Kennedy]

The curve does not necessarily need to be complete. We can establish the principle, as long as you have sufficient points to define the approximate overall shape of the curve. The exact parameters making up the regression curve can then be calculated and used to interpolate points as needed. Indeed, a rough draft might be preferable, as it will tell us all if what you want to do is feasible or not. You can add on the remaining points at your leisure.

[Glenn Kennedy]

Points at 10°C intervals will do fine to get us started.

[MrShorty]

Did you follow the links to the references in your calculator link? References 1 and 3 are both dead links, but reference 2 (http://www.iapws.org/relguide/Ionization.pdf ) is a short paper with an equation for pK as a function of temperature only and a second equation with pK as a function of temperature and density. Of course, density is a function of temperature and pressure which we can calculate if we have an equation of state that we like for water.

Here's what I see needing to happen before we get to the point of programming the calculation in the spreadsheet:
1) We need to identify the temperature and pressure range we need to cover so we can make sure that our equations will adequately cover the range we want.
2) We need to identify the EOS (or similar) we want to use for calculating density as a function of T and P.
3) Then we can feed temperature and density into the equation given in the IAPWS paper to calculate pK.

Perhaps an approach like that will work for you?

If you decide to stick with the "use another calculator to calculate several points and interpolate from there", are you limited to Excel? Gnumeric is a spreadsheet that has a built in INTERPOLATION() function that makes short work of interpolation problems. If that were allowed, that would simplify programming this kind of approach.

[born2dive00]

Ok guys here are the data points on the sheet that when graphed out will create the curve

Column C is the curves for the pressure, and row 19 is for the temperatures above freezing.
Now because 1 atm = 15 psi, the supplied calculator wont go below 15 psi. the problem is, doesnt account for elevation change, where the atmospheric pressure is reduced.

Ergo this data above the yellow line is missing, and needs to be inferred here as the .01 seperation

in summary I need to calculate the values of temps with a .01 degree separation, and the psi also with a .01 separation. each row or column will be its own curve

let me know what you all think

[born2dive00]

Here is an updated sheet with the curves, now even though the pressure curve looks like a strait line, when I calculated the values, they are on an ever so slight curve.

the Temp curve is much greater.

[MrShorty]

A resource that may support this -- if you are allowed to use outside tools not of your own making for something like this. I don't use this one myself (preferring NIST's REFProp), but this is a free download with an Excel addin that has several UDFs for calculating various properties of water http://www.cheresources.com/content/...ater-and-steam If you decided you want to use the equation in the iapws reference to the calculation, this addin would provide an easy way to compute density as a function of T and P, which could then go into Eq (2) of the ionization document. IF you are allowed to use something preprogrammed by someone else for this.

At this point, I would suggest that we need to decide which approach is going to be easier/preferable. It's going to take some work to build a table of reference values from the internet calculator, then build an interpolation algorithm in Excel (or find a spreadsheet/programming language with a build in interpolation function). It will also take some work to enter and debug entering Eq (2) into a spreadsheet (especially if you insist on doing it all in a single cell). Personally, I would usually prefer to program Eq (2) into the spreadsheet rather than build an interpolation algorithm. But the final decision on that is up to you.

What is the end goal of this project? To be able to calculate pK at given T and P? Is it necessary to generate curves in charts?

[born2dive00]

Hey Mr. Shorty glad to see you again. I went to this website and was able to download the excel calculator supplied to the IWA. http://www.wsp.ru/en/download.asp

The excel sheet has an addin, which i have no idea what it is or does.

I prefer to use no third party addins for the simple reason if they change servers or the data access is lost or compromised.

The error of .0000001 in the KW values here and now can lead to an error of .5pH or greater when additiinal acids or bases are reacted, (up to 10 acids and 10 bases)

The problem on the IWA or the WPS website calculators, i dont know how they are getting the pKw values on the EMS website.

Any ideas how i can find the correct formula so I dont have to deal with a massive table and interpolations.

By the way i never knew that excel could do this nor do i know how to set rhis up as this will be my first foye into this relm so any and all help you guys can give me is great.

A
What is the end goal of this project? To be able to calculate pK at given T and P? Is it necessary to generate curves in charts?

[born2dive00]

What i am attempting to do is create a pH calculator that takes into consideration the environmental factors such as trmp, elevation, pressure (open and closed system hence psi going up to 250psi) as well as the CO2 and SO2 saturation.

Accurate enough to sense a 1mg acid / base change in ph in 1000ml of in this case water. Used to reverse engineer compounds

A
What is the end goal of this project? To be able to calculate pK at given T and P? Is it necessary to generate curves in charts?

[MrShorty]

I prefer to use no third party addins for the simple reason if they change servers or the data access is lost or compromised.

I can't speak for the wsp add-in, but all the other add-ins I have used have all been fully self-contained. In my experience, you don't need to worry about having an add-in "break" because someone shuts down a server or something. It's fine to avoid using 3rd party add-ins, it just means that you must come up with your own implementation for all of the calculations that are involved. In this case, that means that where a model like this might ask for density or chemical potential or whatever, you will need to be able to calculate those quantities with suitable accuracy.

If it helps, I found the first reference to the Penn State calculator: https://srd.nist.gov/JPCRD/jpcrd696.pdf It looks like they reduce this down to 2 models. Their model II looks almost exactly like the model given in Penn State's reference 2. Model I is a bit different (using chemical potential instead of density), but one should still be able to implement in Excel (if one has a way to calculate chemical potential and other quantities required).

You're talking about needing to make some fairly accurate/precise calculations (something that can see 1 mg acid change per 1000 mL of solution). Ref. 1 notes that the standard deviation between model I and model II is 0.05 log units and the standard deviation in the regression for both models (relative to experiment) is 0.16 log units. I'm no expert on error propagation and such, but there may be value in considering whether these (or any model) can provide the precision that you need.

Any ideas how i can find the correct formula so I dont have to deal with a massive table and interpolations.

I'm no expert on literature searches, but it feels like this is becoming a literature search project. I might suggest contacting Penn State and see if you can find someone there who can talk to you about which model they are implementing in the web calculator and something about how they are implementing it. I would also suggest getting a hold of the reference to the CRC handbook (Penn State's reference 3) and see what (if any) new information it adds to the understanding of how water ionization is calculated and what models are used. From there, you can then decide which model you are going to use and can then talk about how to implement that model.

One additional thought on using a linear (or other) interpolation model. Where you are looking for very accurate/precise calculations, one may need to consider whether an interpolation algorithm can be accurate enough. This becomes more of an error analysis than an Excel programming question, but it's probably worth thinking about if you still want to consider an interpolation approach.

[born2dive00]

Here is a simplified graph showing the temp to Kw curve for only 1 atm / 1.01325 bar

The vertical axis is the Kw values and the lower line is the different temps with 5 degrees C of separation.

Now for starts I need to see if there is a way to obtain the Kw values where the curve meets a designated temp, for a simple example if I have 12.2'C i would get a Kw value of about 4.441296482388946

is there any way I can get the values accurately off the graph line. It seems that excel will let me only do .02 of a degree seperation.

I have checked carefully if i can get an accuracy to .00001 off the graph this will suffice to eliminate compounded calculation errors.

[born2dive00]

Mr Shorty, I have been through all the documentation and links, that have been supplied, None explain how the pKw values are calculated on the Penn state calculator. http://www.energy.psu.edu/elc/kwcalculator.html
All of the others seem to give me everything but the values I need.

I will contact Penn state to day to see if i can get through to them, but I am sitting at an 8 hour difference. so will see.

.

[Glenn Kennedy]

This is pretty much there!! I fitted the data with a 6th order polynomial and extracted EXACT calues of the regression parameters, using LINEST. I included a columns showing the deviation between calculated and provided values (x10 ^ %).



How does it look to you? Let's get it as good as is needed, and then we can address the explanations.

[born2dive00]

Hey Glen for a start it is looking very good, is there anyway we can tighten it up further I would really really like to get the deviation of .0000000001 if at all possible. currently we are sitting at .00001 which is usable but not idea due to the compounding effects.

If this is any insight, the current in chemistry for one acid the standard is paltry .01 which amounts to a variance of 10 mg on the first reaction, which when by the time you add 10 or 20 different acids or bases increases the variance by 100 to 200 mg per liter or more which can create a deivation of more than +/- 1.2 on the over all ph value. Now considering that a modern digital pH meter can calculate to .0001 on the pH scale especially between the value of 6 and 8 where the water disassociation constant plays a huge role the closer you get to 7 you can see how important it is.

Once we get this tightened up, then I need to address another curve concerning pressure, but more on that later.

I really thank you for your help with this Glen, let me know if it can be tightened up more.

Brad

This is pretty much there!! I fitted the data with a 6th order polynomial and extracted EXACT calues of the regression parameters, using LINEST. I included a columns showing the deviation between calculated and provided values (x10 ^ %).



How does it look to you? Let's get it as good as is needed, and then we can address the explanations.

[born2dive00]

The one thing is the rows seem to be hidden and locked, I can not see the values there to compare them against the Penn state calculator, how do i unlock or un hide them, they are locked in blue, something i have never seen before.

This is pretty much there!! I fitted the data with a 6th order polynomial and extracted EXACT calues of the regression parameters, using LINEST. I included a columns showing the deviation between calculated and provided values (x10 ^ %).



How does it look to you? Let's get it as good as is needed, and then we can address the explanations.

[Glenn Kennedy]

Click on the filter in E25 and select all.

[Glenn Kennedy]

Well, we can increase the polynomial order and see if it makes any difference... but the smaller these numbers become the closer you get to Excel's ability to handle all these dps... floating point aritmetic starts to play an increasing role in the uncertainty.

[born2dive00]

Could we give it a try and see what we get, if we can get a respectable variation at .0000001 or better i would be happy with that.

Well, we can incrrase the polynomial order and see if it makes any difference... but the smaller these numbers become the closer you get to excels ability to handle all these dps... floating point aritmetic starts to paly an increasing role in the uncertainty.

[Glenn Kennedy]

I think if I was to increase this any further the "white noise" caused by Excel's Floating Point arithmetic would drown out any improvements.

Despite being an analytical chemist, I have forgotten all the relationships between pH and pKa, etc. However, I have a grasp of the concepts around Measurement Uncertainty... and I suspect you're asking too much from Excel.

[MrShorty]

I used EQN(2) from PSU's reference 2 (IAPWS paper) and the values I calculated were consistently 3.28E-6 log units different from the values you show in column E.

IMO, if the end goal here is to recreate (to within 1E-7 log units) the results from Penn State's calculator, you are going to have to use the exact same model they are using (and maybe even some of the intricacies of the calculation scheme they use). Assuming that the CRC reference adds no new information, I would guess at this point that they used model 1 from their first reference (the NIST reference), but I haven't tested that one. I would recommend that you review the NIST (Bandura and Lvov) reference, choose whether you want to use model I or model II, then work on programming that into the spreadsheet. Model II appears to be the easier to program (don't need to calculate chemical potential) and appears to fit the experimental data they used equally well.

If the goal is 1E-7 log units, I would steer away from polynomial or similar empirical fits. I expect it will be difficult to constrain something like a polynomial so that it agrees to within 1E-7 log units at every point and at the "untestable" points in between the tabulated points.

[born2dive00]

Hey Glenn and Mr. Shorty can you take a look at this and tell me what you think.
I extended Glens polynomial to I believe a order of 10, which gave me the following results. I was able to tighten it up to an accuracy of .0000001 1E-07 can you tell me if the calculations look correct.

I tried to extend it out to a 12th order polynomial following Glenn's lead, and I saw what you were saying, the regression started to grow to supply an error of .001 by the time it hit 99.9'

Please let me know what you think




[QUOTE=Glenn Kennedy;5458079]I think if I was to increase this any further the "white noise" caused by Excel's Floating Point arithmetic would drown out any improvements.

[MrShorty]

As consistent as the 3.28E-6 log unit error was, I decided to spot a check a few values at different T and P, and the 3.28e-6 difference is quite consistent. I'm starting to think that the PSU calculator is using EQN(2) of the IAPWS reference (same as model II in the Bandura and Lvov reference?) with a slight difference in implementation. I notice that if I use a molecular weight of 18.052 (instead of 18.05268) for water, the difference goes to less than 1E-14 log units (probably within the limits of double precision floating point that Excel uses). I don't know if that is the difference, but it might suggest something to you.

What are you finding?

Edit to add: As you observed, I don't think a higher order polynomial is going to help. We are needing a high order polynomial just to get almost the same results at one isobar. We haven't yet even tried to account for pressure/density. I'm inclined to go with model II (or maybe model I, if I were more ambitious) from the supporting reference papers and recognize that 1E-5 log units is probably better than we really know the pK of water.

[born2dive00]

Mr Shorty, can you please send me a sample of what you are talking about, I must have missed something somewhere, when I looked at the IAPWS papers as i did not see anything showing me how to get to pKw, Any insight into using a formula vs a massive huge table would be great!

Please let me know, if you send a sheet with it, I can compare it to the values of the pH calculator to see if there is sufficient change to obtain a accurate pH reading.

And thank you for all your help you guys are great!



[QUOTE=MrShorty;5458148]I used EQN(2) from PSU's reference 2 (IAPWS paper) and the values I calculated were consistently 3.28E-6 log units different from the values you show in column E.

[MrShorty]

Are we reading the same IAPWS paper (http://www.iapws.org/relguide/Ionization.pdf )? On page 3, They give EQN (2) [in two parts that also uses EQN (1)] to calculate pK. This is the equation I am using (with the values of the parameters given in Tables 1 and 2 on pages 4 and 5). It's a fairly bulky equation (I personally would not try to program it into a single cell), but, with careful attention to parentheses and signs and such, it should be fairly straightforward to program into a spreadsheet. Let me know where you run into trouble entering the formulas.

[born2dive00]

Hey Mr.Shorty.

I have read the link you sent, and yes Ihave seen this before. What I dont see is where the temp and pressure values are entered. All I see is emperical values.

Once again my chemistry and physics formulas experience is rather limited. I can follow examples shown with real numbers and lables, however the abstract theoretical side of things has always eluded me. So any help you can give me would be appreciated.

Please let me know.

Are we reading the same IAPWS paper (http://www.iapws.org/relguide/Ionization.pdf )? On page 3, They give EQN (2) [in two parts that also uses EQN (1)] to calculate pK. This is the equation I am using (with the values of the parameters given in Tables 1 and 2 on pages 4 and 5). It's a fairly bulky equation (I personally would not try to program it into a single cell), but, with careful attention to parentheses and signs and such, it should be fairly straightforward to program into a spreadsheet. Let me know where you run into trouble entering the formulas.

[Glenn Kennedy]

I can fiddle around with Excel formulae a bit... but my maths background is soooo long ago, I will gladly defer to Mr S, whose grasp of maths is light years ahead of mine!!

[MrShorty]

Page 2 has a list of symbols used, where you can see that they use T to refer to temperature (in Kelvin) and ρ to represent density (in g/mL). The equations they are using use temperature and density as inputs. If we enter 300 (K) in A1 and 0.997 (g/mL) in B1, we can, for example calculate pKGw (EQN(1)) and Q (second half of EQN(2)) and finally pK (first half of EQN(2)).

1) We might put pKG in C1 where we could enter =SERIESSUM(1/A1,0,1,$I$1:$I$4) where I1:I4 contain the gamma parameters specified in the paper.
2) We might put Q in D1 where we could enter =B1*exp($J$1+$J$2/A1+$J$3*B1^(2/3)/A1^2) j1:J4 contain the alpha parameters from the paper.
3) Then, in E1, we could enter the final formula (I'll most likely mess up the parentheses and such if I try to write it quickly). Maybe put the beta and other parameters into column K for easy referencing. That should get you a pK value very close to what the PSU calculator outputs at the same temperature and density.

T and ρ can be related to T and P through an equation of state. The PSU calculator says it is using the IAPWS-95 EOS, which, if this is the right document (http://iapws.org/relguide/IAPWS95-2018.pdf ) is a pretty ugly equation. We can use Excel to solve this, but it is going to take a bit of work to put it all together from scratch. As you and I both noted earlier, there are people who have programmed these kinds of EOS's into addins for Excel. This is where you might want to consider whether it is worth the effort to put together your own solution to a good EOS for water or use someone else's add-in. If you are worried too much about the reliability of free downloads, you should be able to find one you can pay for that should be more reliable.

Decisions around the EOS might also be a good place to bring up the question again of what temperature/pressure range are you needing to this to work for? I could see coming up with some simpler equations if we were not worried about calculations near the critical point or in the gas or supercritical phases.

It's definitely a good programming challenge. I recommend spending time with the literature to become more familiar with the published equations as well as thinking about exactly how you want to approach some of the programming challenges.