![]() Then you can easily spot the terms that look like the slope and intercept, and use the functions to get the coefficients necessary to fit the curve. The trick, with hyperbolic functions as well as any other type of curve-fitting, is to linearize the equation, or make it look like the equation of a straight line. #CURVE FITTING IN EXCEL FOR MAC HOW TO#I hope this post has helped you learn how to do hyperbolic curve fitting in Excel. (Did you really think I’d write a post about it if it didn’t? )) Wrapping Up If we did everything correctly, we should be able to plot a hyperbolic curve using the m and k values obtained above and it should fit the original data. Ok, now it’s time for the moment of truth. Then, we can calculate k, which is equal to the slope times m. Next, we can calculate the intercept, 1/m:įinally, we can calculate the values of k and m.įirst we’ll calculate m, which equals 1 divided by the intercept: Since we’re concerned about the linear form of the equation, known_y’s is the calculated column containing the 1/y values, and known_x’s is the 1/x values. Alternatively, you can right click, select add trendline, and then fit vs. This thread proposes some equations to try if you go this route. Calculating the Slope and Interceptįirst, we’ll calculate the slope, k/m, of the data with the slope function: Basically, you want to calculate the curves value at each concentration, then use solver to minimise the sum of squared errors vs. We could use the LINEST function to get both at once, or we could use the SLOPE and INTERCEPT functions to obtain the values separately.įor this exercise, SLOPE and INTERCEPT are more straightforward, so let’s use them. ![]() There are a couple of different ways we could go about getting the best-fit slope and intercept from this data. ![]() So, the term k/m is now the slope of this equation and 1/m is the intercept. Remember, we’ve linearized the hyperbolic equation into the form: (How about that? It’s almost like I planned it that way. Just as a quick check, we can plot these two new columns (E and F) on a chart and see that the relationship between them is indeed linear. When the formulas are filled down, we get the following: We need to create two new columns in our spreadsheet – one for values of 1/x and another for the values of 1/y. ![]() That means we need to get it in a form that looks like the equation of a line:īy taking each side to the power of -1 and doing a little bit of rearranging, we get the linear form of the hyperbolic equation:įantastic! Now what? Hyperbolic Curve Fitting in Excel We can use the SLOPE and INTERCEPT functions to get the values of m and k that best fit the hyperbolic equation to the data, but first we need to “linearize” the equation. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |