Slopes/rates on a non-linear curve

**stefanbg** · 01-27-2009, 04:45 AM

Hi all

I am a biochemist so excuse me if I do not use the correct terms.

I have a practical problem with a set of data that I am receiving through one of my experiments. I am logging the production of biogas as a function of time. The production is not linear but increases and decreases (slowly) depending on the parameters I vary.

My flow meter logs accumulated gas production, however it would be much more informative for me to be able to display the gas production rate (as a function of time) in a graph.

My thought is that this can be done by looking at the slope of the curve as a function of time (the differential to the curve), but I dont want to do this by simply subtracting two adjacent data points as this would be too inaccurate.

Does anyone know of a way to do this? Is it possible to get excel to take the slope of a curve for any point on that curve, based on (for example) an approximated curve fit equation.

Regards

Stefan BG, Denmark

**Fishhooky** · 01-28-2009, 08:33 PM

Hi Stefan,

I have had need to address this problem as well. I've posted an example worksheet of my solution, we'll have to see if it works for your data. The formula used to solve this problem is quite long, so hopefully it works for you because it's a pain to edit. Basically, the formula fits a 3rd order polynomial to the data over a select range and from this interpolates to find other points, and also is able to find the slope. You need to insert your time values into column A, your measured accumulated gas measurements in column B, and then give values for the times of interest you wish to know the slope for in column D. Interpolated points are given in column E and slopes in column F. You can click and drag down columns E and F to increase for your total samples. The only other thing you have to worry about is cell G2. By increasing this number you increase the smoothness of your fit. 4 seems to be a good number, but if you want smoother increase this number. Depending on how rough your data points are decreasing this number may not give a very good fit.

Hope this works for you. Let me know if you have any trouble.

**shg** · 01-28-2009, 08:51 PM

Welcome to the forum.

You have no better data than the sequential differences of cumulative production; that IS your data.

You can apply polynomial fit to the differences. If hourly data was in A1:B24, then you could apply (say) a third-order fit via this array formula spread out over four cells:

=LINEST(B2:B19-B1:B18, A1:A18^{1,2,3}, , TRUE)

If you want a curve that intersect all the data points, you could use a cubic spline, which is what Excel does for a smooth curve. That's harder, but there is an add-in (free, I believe) that does it.

Or you could do an exponential, power series, or log fit, or use any other orthogonal basis functions you wanted. The choice depends on what you know about the nature of the underlying phenomenon.

**furanosa2000** · 08-06-2014, 09:56 PM

Hi Fishhooky,

I am also a biochemist working on microorganisms growth. I wonder if your formula can be also applied for growth curve and substrate consumption also. Do you have any e mail? I may need to acknowledge you if your spreadsheet can be used for my experiment. Just for your information, this is my data looks like (also attached):

Time OD Substrate
0 0.019 173.11
6 0.057 165.35
12 0.355 161.91
18 1.342 154.57
24 2.214 144.98
30 2.674 125.37
36 2.962 122.12
48 3.445 121.78
60 3.685 119.49
72 3.794 121.40
84 3.858 91.94
96 4.095 69.78

**nahyan** · 05-04-2018, 06:56 AM

Hi,

I have two queries related to that. The worksheet will work on the system that is unsteady state right?
Also what are the units of colums > time ? gas production ?
I can use my own units regardless ? like time in mins and production is actually diffusion which is in moles/litre?

An instance response will be appreciated

**nahyan** · 05-04-2018, 07:37 AM

Originally Posted by Fishhooky

Hi Stefan,

I have had need to address this problem as well. I've posted an example worksheet of my solution, we'll have to see if it works for your data. The formula used to solve this problem is quite long, so hopefully it works for you because it's a pain to edit. Basically, the formula fits a 3rd order polynomial to the data over a select range and from this interpolates to find other points, and also is able to find the slope. You need to insert your time values into column A, your measured accumulated gas measurements in column B, and then give values for the times of interest you wish to know the slope for in column D. Interpolated points are given in column E and slopes in column F. You can click and drag down columns E and F to increase for your total samples. The only other thing you have to worry about is cell G2. By increasing this number you increase the smoothness of your fit. 4 seems to be a good number, but if you want smoother increase this number. Depending on how rough your data points are decreasing this number may not give a very good fit.

Hope this works for you. Let me know if you have any trouble.

Hi,

Can you please elaborate what are the units used in this spreadsheet? or i can use minutes or seconds doesnt matter? Also does this system works for unsteady state?

**JeteMc** · 05-09-2018, 09:59 AM

Hello Nahyan and Welcome to Excel Forum.
You appear to be asking a question of Fishhooky, who has not been active on this forum for almost four years. It would probably be better to start a new thread, providing a link back to this one as it appears to have relevance.
Let us know if you have any questions.

**Fishhooky** · 10-28-2020, 01:20 PM

Dear furanosa2000, not sure if you are still looking for a solution to your problem. The formula will work for your data. Private message me if you would like further information. You would paste your time points into column A and either OD or substrate into column B. Then add the time intervals you are interested in into column D and column E and F will output points and slope, respectively. You can alter the value in G2 as to how many points you want to smooth over.

**Fishhooky** · 10-28-2020, 01:22 PM

Whatever units you put into column A will be the same as what time intervals of interest you enter into column D. Essentially, units do not matter. The formula is designed to interpolate between points, so it will work for whatever data you give it.