Curve fitting

JenniferMurphy

Well-known Member
I have been experimenting with brewing tea at different temperatures. My usual practice is to heat water in a Pyrex in the microwave. By trial and error, I found that it takes about 3-4 minutes to bring 16 oz to a boil. But what if I want water at 180°?

So I took a few readings. Here are the results:

 C/R D E F G H 3 Time Water Temperature 4 (Min) (oz) Start End °/min 5 3:00 16 66.7 179.5 37.6000 6 2:30 16 70.0 168.2 39.2800 7 2:00 16 72.0 153.7 40.8500 8 1:30 16 69.1 136.9 45.2000 9 1:00 16 66.7 116.4 49.7000 10 68.9 Average

<tbody>
</tbody>

As expected, the hotter the water gets, the more slowly the temperature increases.

I would like to come up with an equation based on this data that will allow me to calculate how many minutes to set the microwave for to get a specific temperature, like 180°.

The function would need to have a horizontal asymptote at 212°. For my data, it is more like 208°. By the time I get my little digital thermometer in the beaker, it has lost a few degrees.

I can't seem to come up with a way to fit a curve to this data. Can anyone help?

Thanks

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Kenneth Hobson

Well-known Member
It would be better to determine what your microwave wattage is set to and then use the specific heat of water. Of course that only applies to "pure" water but should be close enough to do your computations.

Kenneth Hobson

Well-known Member
You can use a scatter plot and add trendlines to see what R^2 is closest to 1. A two degree polynomial gives you R^2 of 1 for x=minutes and y=temperature.
y = -3.406.6 * x^2 + 1.323.8 * x + 67.24. Plug in 180 for y and solve for x. One can use the solver to do that or solve as a quatratic equation. Other ways are possible too.

JenniferMurphy

Well-known Member
It would be better to determine what your microwave wattage is set to and then use the specific heat of water. Of course that only applies to "pure" water but should be close enough to do your computations.

Sure, but I wanted to use the opportunity to (re)learn how to do some curve fitting.

JenniferMurphy

Well-known Member
You can use a scatter plot and add trendlines to see what R^2 is closest to 1. A two degree polynomial gives you R^2 of 1 for x=minutes and y=temperature.
y = -3.406.6 * x^2 + 1.323.8 * x + 67.24. Plug in 180 for y and solve for x. One can use the solver to do that or solve as a quatratic equation. Other ways are possible too.

The coefficients in your equation have too mane decimal points. I tried it after removing each one, but it didn't do the trick.

How did you arrive at that equation?

It seems to me that the equation should be some sort of exponential, no?

shg

MrExcel MVP
If the water were heated in an insulated, closed container, it would increase in temp linearly until boiling at local pressure, where it would remain. I don't think that's an asymptote, it's an abrupt change in slope.

I expect the departure from linearity is from energy loss due to heating the container, from blackbody radiation of the container, and (mostly) from convection. If you throw a lid on it, I'd bet it remains reasonably linear across a pretty broad range of temps.

Kenneth Hobson

Well-known Member
The coefficients are derived by the fit. Have you never plotted data and used the Trend Lines? e.g. Trendline in Excel - EASY Excel Tutorial

As I explained, a two degree polynomial was the best fit. Of course when you round the coefficients, you compound error and reduce R^2. An exponential fit was actually the worst fit but as fits go, I consider any R^2 of 0.90 or better a very good fit. Exponential also had an R^2 of 1. Polynomial fits are easy to determine in VBA.

That plot only applies to your data which assumes certain conditions like your average "room temperature", 68.9F, and the power of your microwave, and volume of water, and your type of water.

It is easy enough to solve that equation by the Add-In Solver. I may work up one or more VBA direct solutions if that appeals to you.

Last edited:

JenniferMurphy

Well-known Member
If the water were heated in an insulated, closed container, it would increase in temp linearly until boiling at local pressure, where it would remain. I don't think that's an asymptote, it's an abrupt change in slope.

If the container were perfectly insulated and if it were perfectly closed, then yes, you are probably right. But that is not the experiment I am running. I want to know about how long to set the microwave for in an open Pyrex container. I'm pretty sire in those conditions, it's an exponential decay curve with an asymptote at about 212.

I expect the departure from linearity is from energy loss due to heating the container, from blackbody radiation of the container, and (mostly) from convection. If you throw a lid on it, I'd bet it remains reasonably linear across a pretty broad range of temps.

That, and probably other factors. But you keep trying to change the experiment. I want an equation for the conditions I have, not some ideal or theoretical conditions that I will never have.

JenniferMurphy

Well-known Member
Naturally, someone has written a paper on the subject:

Modelling microwave heating

This is interesting, but does not appear to be relevant to my problem unless I am interested in working with Maxwell's equations. It was those equations, along with general relativity and quantum mechanics, that got me to transfer out of physics in college.

1,191,528
Messages
5,987,112
Members
440,080
Latest member
drhorn4908

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back