Technical Discussion
  >> Windows Issues


Register (or login) on our website and you will not see this ad.


  Print Thread
Standard User cheshire_man
(knowledge is power) Thu 27-Feb-14 22:26:15
Print Post

Excel advice sought


[link to this post]
 
I have an Excel spreadsheet that records the overnight minimum temperature from my cheapy electronic weather station. I have almost every day's data (though not when on holiday or other multi-day absences) since late November 2010.

I overlap for period so I can see all the Winter months on one worksheet. So the data for each 13-month period from 1 November one year to 30 November the next year is on its own worksheet. There's also a summary sheet showing for each month the number of occurrences of 6 or so temperature ranges. Finally I have a worksheet showing data for all years with the intent of creating a graph to show the differences year by year.

It's this latter graph that's causing me problems. By including the data for each day or each year the graph is too cluttered, but if I miss days, e.g. only use data every 2/5/7 days or whatever, I can easily miss a short spell of unusual temperatures.

Hopefully I've managed to convey my dilemma. maybe what I'm trying to achieve is unrealistic.

I've posted the spreadsheet here, the worksheet in question is All data. I'm using Excel 2010.

I'd appreciate advice, so helpful thoughts and advice would be welcome.

Tony
We have more and more laws, and less and less enforcement
Standard User camieabz
(sensei) Fri 28-Feb-14 03:50:39
Print Post

Re: Excel advice sought


[re: cheshire_man] [link to this post]
 
Right, I've done some fiddling and have a few 'ideas' added to a new version (link via PM).

I didn't enable the macros out of force of habit, and the charts seemed to work fine anyway. I assumed the problem was more of chart formatting or data presentation into a form that is more readable.

Excel 2007 here, and the new version was saved as an xlsm file, to maintain the macros within. You can save it as an xls if necessary, but I suggest an xlsx to get the most from Excel's functionality (charts might be more swish at that level, but I'm guessing). Not to mention that the file size is half the xls version.

So...see the PM. smile
Standard User cheshire_man
(knowledge is power) Fri 28-Feb-14 07:16:30
Print Post

Re: Excel advice sought


[re: camieabz] [link to this post]
 
As I've said in my PM reply, thank you. It'll take me a bit of time to absorb it all.

Tony
We have more and more laws, and less and less enforcement


Register (or login) on our website and you will not see this ad.

Standard User philippercival
(fountain of knowledge) Fri 28-Feb-14 08:58:17
Print Post

Re: Excel advice sought


[re: camieabz] [link to this post]
 
Looking at the time of your posting, you got hooked.

http://speedtest.net/result/2459383290.png

Solar Results
Nov  143,  Dec  125,  Jan  77,  Feb 174,  Mar  210,  April 384, May 450, 
June 485, July 512, Aug 433, 
Max Day  25.2
Standard User JPL
(committed) Fri 28-Feb-14 10:51:02
Print Post

Re: Excel advice sought


[re: philippercival] [link to this post]
 
You could try smoothing the data slightly, e.g. plot as a line (not bars) the average of the last five or so values. This approach works well with data such as car fuel consumption; not sure how it would work for weather data.

JPL
Standard User camieabz
(sensei) Fri 28-Feb-14 17:28:39
Print Post

Re: Excel advice sought


[re: philippercival] [link to this post]
 
Not at all. I actually read the post around eleven, woke up later and answered it. smile
  Print Thread

Jump to