New project -- how to handle weather data

Years ago I developed some analysis tools to analyse telephone bills for my clients - essentially, I had a template into which I could drop the call data each month (it first had to be translated into a standard format for different suppliers), adjust the named ranges to suit the number of calls, and then the rest was all formula-driven. I had pre-defined reports on different sheets which gave me different views of the data (tables and graphs). I looked into using pivot tables, but found that they did not give me the analysis in the forms I wanted.

So, instead of deciding at the outset that you want/need to use pivot tables, ask yourself first what you want to do with the data. I presume you have temperature variations, wind speed, rain etc. as variables, so sit down and work out how you would like to analyse these things and what is the most suitable way of displaying them. You might want to compare the current day (or yesterday) with the previous day, or monthly average to date. Maybe in the future compare the current month with the same month in the previous year, or current quarter with previous quarter etc.

Once you have a clearer idea of what you want to achieve, then you can consider how you might tackle the task - functions like SUMIF(s)/AVERAGEIF(s)/SUMPRODUCT etc. and MAX/MIN as array formulae can get you a long way.

Hope this helps.

Pete