Non-continuous Averages

Hello,
Can anyone help me with making some changes to a AVERAGE formula?

I need a average formula that includes the values of price every four hours over the last five instances,current price included.

Below is formula I've been working with. I'm thinking adding an Offset or IsNumber might be the best way to go because it'll allow me to skip between the prices and only factor in the prices I want.

That's the gist of what I'm trying to do, be able to include any combination of numbers in a average formula without the numbers having to be in continuous order.

Basic Average Formula:

Please Login or Register  to view this content.

Please see attached spreadsheet for a better understanding.
Thanking you for taking the time to read this post and for any and all help you can provide.

Hi Artiststevens,

I couldn't get INDIRECT to work generating parts of an array, so I used this and it seems to work with your sample data.

In D19:

=IF(MOD(HOUR(A19),4)=0,AVERAGE(INDIRECT("B"&ROW()-16),INDIRECT("B"&ROW()-12),INDIRECT("B"&ROW()-8),INDIRECT("B"&ROW()-4),B19),AVERAGE(INDIRECT("B"&ROW()-12-MOD(HOUR(A19),4)),INDIRECT("B"&ROW()-8-MOD(HOUR(A19),4)),INDIRECT("B"&ROW()-4-MOD(HOUR(A19),4)),INDIRECT("B"&ROW()-MOD(HOUR(A19),4)),B19))

Well, you couldn't start averaging 6 numbers in row 19 if your values started in row 3, of course (3, 7, 11, 15, 19). You would just need to add another section to each formula as shown in red below (I think!):

=IF(MOD(HOUR(A20),4)=0,AVERAGE(INDIRECT("B"&ROW()-20),INDIRECT("B"&ROW()-16),INDIRECT("B"&ROW()-12),INDIRECT("B"&ROW()-8),INDIRECT("B"&ROW()-4),B20),AVERAGE(INDIRECT("B"&ROW()-16-MOD(HOUR(A20),4)),INDIRECT("B"&ROW()-12-MOD(HOUR(A20),4)),INDIRECT("B"&ROW()-8-MOD(HOUR(A20),4)),INDIRECT("B"&ROW()-4-MOD(HOUR(A20),4)),INDIRECT("B"&ROW()-MOD(HOUR(A20),4)),B20))

Hi, I'm in here late but,

Did you think of simply ctrl clicking on the cells you wanted to average, then give the selected cells a range name. Then do an average using the name?

See attached.

The formulas I've provided thus far expect the data to be spaced evenly (it uses the time value in the current row, but once that is calculated it then averages cells that are each 4 rows apart. In your latest sample some are two rows apart while others are four. I could create a similar formula that would take that EXACT setup into account, but if anything changed, or you tried dragging it down even 1 row it would fail. It would be a one cell formula, essentially. It would be easier to just manually type in

=AVERAGE(B3,B5,B7,B11,B15)

And to Marvin's point - yes, that would make sense in the last example, although it would still be easier to just use the cell addresses in that case. Using names would likely not work in the earlier example as it is more of a rolling average as you drag the formula down.

This sounds like a stock price problem. If you have real dates do you also have symbols?
I'm really thinking about Pivot Tables. They can do Sums as well as averages and NO FORMULAS at all.

I also do a water meter spreadsheet. About 170 meters that spit out their readings 4 times a day. I simply suck in the csv data to the bottom of my table, Pivot Table it out and Easy Peasy averages, etc.

To see if your data works, I'd need a better look at what it really looks like. Better sample?

I'll try to break down my original formula for you so you hopefully understand how it works:

=IF(MOD(HOUR(A19),4)=0,

This checks to see if the hour in A19 divided by 4 leaves a remainder of 0. MOD(12,4) equals 0, while MOD(13,4) equals 1 since 13 divided by 4 is "3 remainder 1". So if it's true that this test results in 0, perform the next (TRUE) part of the IF function.
AVERAGE(INDIRECT("B"&ROW()-16),INDIRECT("B"&ROW()-12),INDIRECT("B"&ROW()-8),INDIRECT("B"&ROW()-4),B19),

This can be broken down into:

AVERAGE(
....INDIRECT("B"&ROW()-16),
....INDIRECT("B"&ROW()-12),
....INDIRECT("B"&ROW()-8),
....INDIRECT("B"&ROW()-4),
....B19,

The ROW() functions within the formula simply return the current row that the formula resides in. So if this formula is in D19, ROW() returns 19. INDIRECT allows you to combine strings and numbers to create a valid cell, worksheet or workbook reference (amongst other things, probably).

INDIRECT("B"&ROW()-16) becomes
INDIRECT("B"&19-16 which becomes
INDIRECT("B"&3) which becomes
INDIRECT("B3") which becomes simply B3 (or $B$3).

The same then applies to the -12, -8, -4 sections, for which you get 19-12 (7), 19-8 (11) and 19-4 (15). This creates cell references B7, B11 and B15. Finally average those four "created" cell references with B19.

If the result of the IF test was FALSE (the hour is not divisible by 4 with 0 remainder, e.g. 13:00, 14:00, 15:00, 17:00, etc.) then the second AVERAGE function is performed. It is similar to the first, but doesn't have to go back as far since it is the average of the past four major times and the current time. This is why it starts at -12, then -8, -4, etc. It also subtracts the REMAINDER of the hour in the current row divided by 4 to get the correct rows. So when the hour in A20 is 13:00 (13/4 = 12 remainder 1) it finds the earliest row needed in the average by subtracting 20-12-1 (ROW()-12-MOD(HOUR(A20),4)).

That's a lot to swallow probably.

AVERAGE(
....INDIRECT("B"&ROW()-12-MOD(HOUR(A20),4)),
....INDIRECT("B"&ROW()-8-MOD(HOUR(A20),4)),
....INDIRECT("B"&ROW()-4-MOD(HOUR(A20),4)),
....INDIRECT("B"&ROW()-MOD(HOUR(A20),4))
....,B20))

INDIRECT("B"&ROW()-12-MOD(HOUR(A20),4)), becomes
INDIRECT("B"&20-12-MOD(13,4)) which becomes
INDIRECT("B"&20-12-1)) which becomes
INDIRECT("B"&7)) which becomes
INDIRECT("B7") which is just B7 (or $B$7)

Whew.. hopefully that made sense for you

FWIW, you might find you can adapt the FORECAST approach from your prior thread:

Please Login or Register  to view this content.

above is for 5 items obviously and per linked thread we know that the hour values only exist every 4 cells (ie 13:00 etc doesn't actually exist in your real file)

Hi DO,
Following my instructions of "One Test..." I tried your CSE above on the file. It gave a different Average. I believe Blank cells in A and B give a zero which is averaged in.

I study your CSEs, hoping someday to make my own. See attached.

As mentioned the CSE assumes consistent spacing - per OP's other thread - this is seemingly not reality here and so the suggestion should be disregarded (my bad)

The below might work for inconsistent gaps:

Please Login or Register  to view this content.

the 4 in the LARGE represents n-1 where n is data points required (current record is always included so is removed from n)