[Solved] How to average specific number of values backwards?

[opog]

I'm trying to create a moving average of the last work day of each week. So is there a formula that I can change how many values that it will average backwards even if there are blank spaces and some work weeks are longer than others? For example say I decided to average the last 4 ending work days and the search range could be something like I395 back to I355 but the formula in cell K395 would return the answer of: 125.25 (the average of 125, 126, 124, and 126) since these are the last 4 values going backwards starting at cell I395. This answer would be the same all they way up to K391. If the formula was entered in K390 if would recalculate a different answer.
Of course some weeks have fewer work days because of holidays so if I were to use a simple average formula it would throw the accuracy off for long calculations and day to day calculations.

So the formula in just simplified words would be something like this: In the range of I395:I355, Average the last 4 visible values starting at cell I395

Is there a formula that can do this? Any help would be very appreciated.



I used the "=IF(AND" formula for column I and column J.

[RusselB]

Take a look at the AVERAGEIF function.

Slightly off topic, giving the functions used in your formula for columns I & J tells us next to nothing. At minimum the full formula would be needed.
Better yet, attach a copy of your spreadsheet to this topic so we have the ability to see what you have done and how your data is organized.

[opog]

A column is the date, H uses the =WEEKDAY(A194;1) formula pulled from the date, and E column is just the data column.

I column is this: =IF(AND(H194>H195);E194;"") So it just looks at whether the weekday number below is smaller, if it is then data from E is shown otherwise it gives a blank ""

J column is this: =IF(AND(I193=E193);(I193);(J193)) This just looks at whether the I column is equal to the data column from E, if is then it equals I, if not then it lists the J cell that is one cell above.


But like I mentioned I need a formula that can count and average back a given number of values so I can create a end of work week moving average.

Thanks.

[Zizi64]

Please upload an .ods file format real sample file here.

[opog]

Here is a simplified sheet

[MrProgrammer]

I'm trying to create a moving average of the last work day of each week. So is there a formula that I can change how many values that it will average backwards even if there are blank spaces and some work weeks are longer than others?

Create a column (L in my attachment) to keep track of the weeks. Then you can use SUMPRODUCT to add the last four week values and divide by four. But I have used the variable MovingAverageLength instead of the constant four. See Insert → Names → Define.

Edit: Attachment replaced 2019-08-14 19:11 UTC to extend formula to row 2

201908141253.ods

(20.96 KiB) Downloaded 96 times

[Tutorial] The SUMPRODUCT function

Your "dates" in column A were not dates, but text. Read about View → Value Highlighting in the product information. Text is black, numbers/dates/times blue, formulas green. In my attachment, I converted your text to dates with [Tutorial] Text to Columns. Turn off Value Highlighting when you're not using it.

I simplified formulas like =IF(AND(H6>H7;H6>H7);E6;"") in I6 to =IF(H6>H7;E6;"") and =IF(AND(I6=E6);(I6);(J6)) in J7 to =IF(I6=E6;I6;J6), removing unneeded parentheses and unnecessary use of the AND function.

You could use AVERAGEIF or AVERAGEIFS instead of SUMPRODUCT, but the latter function is more versatile and it's better to learn how to use it.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[opog]

Thanks very much, that works great! Edit: I think figured out what I was about to ask, I just put the number of weeks I want to average in place of "MovingAverageLength" since I need multiple but different length moving averages in different columns. I'm going to try and learn some over time from those good links you posted also.