Analysing large data sets

[10Gallen]

Hi all,
I have a 62 year rainfall record on which a number of calculations are performed. The data set is used to model the performance of a set of ponds to determine spill occurrence, make-up volumes etc.
Is there a way to perform a sort of conditional sumif based on the dates? For example to pull out rainfall events summary by month / year.
One other useful function would be to return a table or listing of the dates where spills / run dry occurred. Sorting the data based on values can be done, but requires a cut'n'paste each time as all the internal dependancies get ruined. Can anyone provide me with some pointers for better result reporting?
Thanks
GAllen

 

[MortenA]

Have you tried pivot tables? They are suited for summarizing and grouping/treating large arrays of data

Best Regards

Morten

 

[chrisbullough]

I agree that pivot tables might be your best bet, but you may also find that array functions have more flexibility. There is a general discussion in thread770-6411, and I addressed a similar problem to your own (as far as I can tell) in thread770-62429.

The downside to array functions is that they are fairly memory intensive.

Regards

 

[10Gallen]

Thanks guys, I'll have a closer look at the arrays & offset function, as I have not used these before. I just couldn't get the pivot tables to give me what I wanted.
It's not all bad tho', a friend made a little C++ proggy which did the trick, although the workings are doubly incomprehensible. <sigh>...more book learnin' req'd.
GAllen