Smoothing Data

I don't understand what you mean with "smooth the data"

What I see is: you try to get average value for each row with condition "HIGH" and "LOW",
if that what you want, you can use this formula:

on column BW "AVG.HIGH"

Please Login or Register  to view this content.

on column BZ :"AVG.LOW"

Please Login or Register  to view this content.

Note : the result will be different from your data, because in the end of your formula on "AVG.HIGH", your last column is "BS5+BT5",
not "BS5+BU5", and on "AVG.LOW" you skip column "AZ" too.


Regards,
SDCh

Hi.

I know this doesnt answer your question, but instead of that big =sum()/35 formula you are using - which by the way has an error in HIGH at the end (BT instead of BU), try using this instead (I am assuming you are using 2003)...
=SUMIF($C$3:$BV$3,"High",C5:BV5)/COUNTIF($C$3:$BV$3,"High")
if you have 2007> use...
=AVERAGEIFS(C5:BV5,$C$3:$BV$3,"High").

Now, what do you mean...you are trying to smooth the data? Perhaps a moving 3-day (or more?) average? In row 7, use this, copied down...
=SUMIF($C$3:$BV$3,"High",C5:BV7)/COUNTIF($C$3:$BV$3,"High")

I attach the file, so you can check which one you want to use, I already add version that FDibbins said if you use Excel 2003.
I also check your formula on last 2 row.


Regards
SDCh


Note : the attach file using Excel 2010.

As I see it, part of this question (if not most of it) isn't really about Excel at all, but more a question for a climatologist/meteorologist/other atmospheric scientist. What you are computing is a simple arithmetic average of the daily high temperatures. For data that is as noisy as weather data, they apparently use some additional analysis beyond a simple arithmetic average. Perhaps after taking the arithmetic average for each date, they then do a curve fit across the month to get the final "averages." I really don't know. If I use the =TREND() function (http://office.microsoft.com/en-us/ma...081.aspx?CTT=1 Usually I would prefer to use the LINEST() function to get the coefficients of the trendline http://office.microsoft.com/en-us/ex...005209155.aspx) to get a linear fit across the month of February, I get values from 39 on Feb 1 to 45 on Feb 29. So maybe this is how they do it, but using data from all 12 months of the year in a single fit. Again, I'm no climate scientist, so I really don't know how climate scientists smooth temperature data.

This is one of those questions where, if we knew how climate scientists come up with their averages, we could probably easily program it into Excel. Trying to determine how they obtained the average is a much more difficult problem. Are you approaching this problem as a "student" of climatology trying to learn how they do it? Or as someone who wants to challenge the way they do it? In any case, it seems to me that, at this point in the problem, we would benefit greatly from a short course in climatology to understand how temperature data are smoothed.

Did you expect to understand it? The math they are using is fairly advanced (college level upper class to graduate level). I'm not sure how many of us on here have enough math background to coach you through the math they are using, especially if you lack the math background to understand it. What parts of it do you not understand that you feel you need to understand?

At its most basic, they appear to be doing as I suggested they might - take the arithmetic averages, then curve fit those averages into an equation that will generate a smooth average for each day of the year. Also as I suspected, the equation that they are using to do this rigorously is fairly complex.

So I come back to my previous question -- what is your goal or intention here? Are you really out to recreate the calculations they describe in this paper? I believe that, in most cases, understanding the math is a necessary prerequisite to being able to program those equations into a spreadsheet. In this case, it will be absolutely essential to have a solid understanding of the math, because it is quite involved.

Or is this something for your own personal use, where you will settle for some simple math that will allow you to do something with these data?

Or are you looking to do something I'm not anticipating?

if was that simple why did they use a massive formula? or did they think ah lets look really clever and confuse everybody

As martin said, if there was a really simple equivalent, they would use the simpler equation.

The hardest part of a problem like this really isn't about Excel at all. The hardest part is trying to decide what kind of equation to use to fit the data to. The first part of simplifying this is to decide what kind of simpler equation we want to use -- recognizing that we will not be able to exactly recreate what these guys are describing in their paper.

The simplest approach might be something that breaks the year up into months, and regress a curve for each month. As I noted in post #8, one could easily use a straight line to fit February's data and be within a few degrees of the more difficult model. If I were generalizing this to other months, I would probably use a 2nd order polynomial rather than a straight line -- especially for months like January and July when one would expect T to go through a max/min. Again, this kind of model is very empirical and not something that will be publishable or really represent what is going on with temperature. It will also require many more parameters than the more theoretical model. The generic implementation of this approach might look like:

1) Take arithmetic average of temperatures for each date over multiple years.
2) Regress a polynomial curve for the month from these data. In Excel, I would use the LINEST() function (see help file above) to perform this regression.
3) Computer smoothed temperatures from the curve to make sure the calculated values are reasonable.

It should be emphasized that this is not intended to replace the more rigorous models like that presented in the paper. It is a quick and easy way to do these calculations in Excel without needing to go through the higher level math and programming needed to implement the kind of model presented in the paper. Depending on your requirements, it may be adequate, though, for your needs.

Perhaps an example of how you tried it? In what way did it not work -- incorrect results? failed to return both slope and intercept? error value?

I'm not in a position to "do it for you." Basic use of the LINEST() function is to create two columns (for a straight line fit) - one column for the y data and one column for the x data. Here's the example from the help file linked to above:

A B
Known y Known x
1 0
9 4
5 2
7 3
Formula Formula
=LINEST(A2:A5,B2:B5,,FALSE)

Note The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the range A7:B7 starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the single result is 2.

When entered as an array, the slope (2) and the y-intercept (1) are returned.

I will emphasize the bit at the end -- LINEST() is an array formula. Two cells (for straight line fits) must be selected when entering the function, and the function must be confirmed with ctrl-shift-enter. Perhaps if you explain specifically how LINEST() is not working for you, we can help you understand what you are doing wrong.