Return Average Based on an Input Constant to Define Range Length

Return Average Based on an Input Constant to Define Range Length

The attached Excel spreadsheet contains two rows of data and an additional cell at the top, "Avg Inflow Duration", representing an input. The data in column "Qin" is also input by the user.

I need the "Avg in" column to be an average of the data in column "Qin" over a range defined by the "Avg Inflow Duration" value.

For Example, if the Avg Inflow Duration = 6, I want the "Avg In" value to be the average of the current row and preceding 5 rows. For the Rows above the 6th row it would just be the average of all the data up to that point. For Example, on the 5th hour (row 10), The Avg in (C10) should be equal to the average of C6, C7, C8, C9, and C10. This should be the case for all rows at times less than the input "Avg Inflow Duration" value (D1).

Then, any hour greater than or equal to the Avg Inflow Duration, should be an average of that many values. For example, if Avg Inflow Duration = 6, then cell C20 should equal the Average of cells C20, C19, C18, C17, C16, C15.

I need this data to be dynamic because the Hour column could be shorter or longer depending on the scenario, but max length is to row 486.

Any help is greatly appreciated.

Thanks,
Myles Mc

Good afternoon,

I think this is what you're after. I've assumed that you want to return the rolling average or column B in column C (otherwise, averaging column C in column C is a circular reference).

Hope this helps,

Thank you

Interesting solution, thank you very much for this. I'm managed to get this to work in one column so that I don't need the Start and End columns you made. The formula looks like the following:

=IFERROR(AVERAGE(INDIRECT("b"&IF(E6-$D$1<$D$1,$D$1,E6-$D$1)&":b"&ROW())),"")

Thanks again!

Sorry, a correction to above formula. The formula used is:

=IF(ISNUMBER(B6),IFERROR(AVERAGE(INDIRECT("b"&IF(ROW()-$D$1<$D$1,$D$1,ROW()-$D$1)&":b"&ROW())),""),"")

Ok, I found one small error in the solution. The END row count needs to have a one added to it (shown in bold underline below). Otherwise, you would end up averaging one too many values. So the formula becomes:

=IF(ISNUMBER(B6),IFERROR(AVERAGE(INDIRECT("b"&IF(ROW()-$D$1<$D$1,$D$1,ROW()-$D$1+1)&":b"&ROW())),""),"")

It is now working flawlessly, thanks!