Incrementing a Cell Reference By x

Question

I am trying to average a large data set within Excel where there is data by 30 minute periods for each day of the month. I have a header for 'start date' and 'Start time' which displays 00:00:00 - 23:30:00 for each day, with the data in question in columns to the right. I am trying to develop a formula which averages data which concerns 00:00:00 - 11:30:00 for each day and 12:00:00 - 23:30:00 for each day (AM:PM).

I have one header for AM values and another for PM values, and Im looking to average both these elements for each day of the month

I have used the following formula which does work:

=AVERAGE(OFFSET(C6,24,0,-24))

This formula capture the range from 00:00:00 - 11:30:00 and averages it like I want, but when I drag the formula down to the next cell, C6 increments to C7, but I want it to increment by 48 each time, meaning C6 should become C54. This means when I drag it down each time it will average the AM values for 02/01/2019 and so on.

Is there any way the cell reference in the above formula can increment by 48 instead of 1 each time it is dragged down into another cell?

Sample Data

Expected output

Answer 1

I made up a dataset kind of like yours:

The formula I've used in F4 to obtain the average of values, when date is '01/01/2019' and time is AM, is AVERAGEIFS:

AVERAGEIFS function

My formula is:

=AVERAGEIFS($C$6:$C$101;$A$6:$A$101;E4;$B$6:$B$101;"<"&0,5)

You can use it and just drag down. As you can see in the image above, the formula returns 27,07

The formula will work only with AM because the criteria is "<"&0,5. In the column where you want to do the average of PM times, we would use the same, but changing criteria to ">="&0,5, this means:

=AVERAGEIFS($C$6:$C$101;$A$6:$A$101;E4;$B$6:$B$101;">="&0,5)

Hope you can adapt this to your needs.

NOTE: Because your data is in a Pivot Table (you never mentioned that), if the Pivot Table changes, you'll neeed to adapt the formula. A solution would be using ranges from row 6 until last one, so the formula will take always all the rows. Remember to not show total row or it may affect the result.

OPTION 2: In case you can add an extra column to original data (not in the Pivot Table), maybe this can help a lot.

I've used same data than before, but I added an extra column, named AM/PM with a formula on it:

=IF(B6<0,5;"AM";"PM")

It looks like this:

Then I created a Pivot Table based on it, with a configuration:

1. Field Start date to section of rows.
2. Field AM/PM to columns.
3. Field VALUE to values section, but instead of suming up, I did average operation.

I get this on my Pivot Table:

As you can see, AM for 01/01/2019 is 27,07 (the oher numbers are based on random numbers I made up).

Hope this can help.

Answer 2

If you absolutely must go with the AVERAGE(OFFSET()) solution I would have a 'helper' cell (in this case J6) with 1 in it, then J7 have =J6+48, then the main formula could be

=AVERAGE(OFFSET($C$6,J6,0,24))

Then when you copy the formula down, you'll also need to copy down from J7. The Rows argument is purpose built for what you want to do. Going to efforts to change the reference cell defies this purpose.

Edited because I missed the AM/PM split:

I think, however, AVERAGEIFS() will fit your requirement better. This takes the average of all values in column C where the date in column A matches that in cell G6 (Change this to wherever your output is). This will avoid errors should any of your dates have any half hour periods missing.

=AVERAGEIFS($C:$C,$A:$A,G6,$B:$B,"<"&TIMEVALUE("12:00:00"))