Re: gnumeric spreadsheet newbie questions

[Oliver Burnett-Hall <olly burnett-hall co uk>]

Dave,

I'd do this in two steps.

1) In column C calculate the hour value for each reading. In cell C2
enter =DATE(YEAR($A2),MONTH($A2),DAY($A2))+TIME(HOUR($A2),0,0) and
copy it down the column.

2) In column D sum all the readings that match the hour value for the
current reading. Enter this in cell D2 then copy it down the column
=SUMIF($C$2:$C$30000,$C2,$B$2:$B$30000).

I haven't tested any of this, but I hope it's close enough to get you
started. You'll need to tweak the formulas to cover the ranges of
cells with readings (or, even better, define named ranges for these if
you know how to do that).

- olly


On 8 December 2017 at 00:29, Dave Stevens <geek uniserve com> wrote:
> I have some spreadsheets with about 30K rows each. In column A is a
> time and date like this - 2017-11-01 00:01:16 UTC. The time increments
> monotonically for a month then that's all the rows.
>
> I'd like to sum the readings for each hour, The number of rows per hour
> varies a bit with uneven intervals. So in a general sense:
>
> set sum to 0
> While hour is unchanged do:
>
>         add readings in column B
> end;
>
> display hourly total in column C
>
> and iterate over all rows until a blank row is found (EOF)
>
> If there's some function to do this or some part of this I'd appreciate
> a reference, or [perhaps hand-holding off-line.] The F1 help in my copy
> of gnumeric 1.12.28 gives an error message.
>
> Dave
>
>
> --
> In modern fantasy (literary or governmental), killing people is the
> usual solution to the so-called war between good and evil. My books are
> not conceived in terms of such a war, and offer no simple answers to
> simplistic questions.
>
> ----- Ursula Le Guin
> _______________________________________________
> gnumeric-list mailing list
> gnumeric-list gnome org
> https://mail.gnome.org/mailman/listinfo/gnumeric-list