Dragging formula down one row, but jumping more than one row data reference

[Cnidoraph]

Hi all,

I guess you gonna resolved this newbie issue in few lines...

I have a dataset of 4320 rows for 17 columns.
Each row is a minute. I want to average my data per hour.
If I do Average formula which encompass 60 rows, for the first hour it works. But then if I drag the formula down one row it will give me the average of the data +1min, not +60min

How can I adapt any formula to jump over 60rows each time I drag the formula down one row.

Thank a lot, it gonna be more than helpful

Raph

ps: I add an excel with one of the column and the corresponding averaging I need

[Vraag en antwoord]

Use a pivot table and group it by date and time. Or just read the yellow banner at the top.

[menem]

Please try

E2
=IFERROR(SUMIFS(B:B,A:A,">="&D2,A:A,"<"&D2+TIME(1,0,0))/COUNTIFS(A:A,">="&D2,A:A,"<"&D2+TIME(1,0,0)),"")


Regards.


Seem my formula is not what you want, sorry.

[menem]

Please try again

E2
=AVERAGE(OFFSET($B$1,(ROWS(D$1:D1)-1)*60+1,0,60))


Regards.

[Cnidoraph]

Thank a lot for your help !
But when I use the last formula: E2

=AVERAGE(OFFSET($B$1,(ROWS(D$1:D1)-1)*60+1,0,60))

excel open an Error message.

Do I miss something?

[AliGW]

What error message?

[Cnidoraph]

"Formula error" without saying anything more

[AliGW]

No - that's not possible. Excel error pop-ups are all more detailed than this. Are you using Excel or something else for this?

[Cnidoraph]

It works ! It was just a matter of language compatibility...

Great, thank a lot :D

[AliGW]

You may find this useful: https://en.excel-translator.de/translator/

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.