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
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