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

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

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.

Last edited by opog on Thu Aug 15, 2019 4:12 am, edited 1 time in total.
OpenOffice 4.1.1 Windows 7
opog

Posts: 20
Joined: Sun Dec 29, 2013 11:26 am

### Re: How to average specific number of values backwards?

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.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 5853
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: How to average specific number of values backwards?

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.
OpenOffice 4.1.1 Windows 7
opog

Posts: 20
Joined: Sun Dec 29, 2013 11:26 am

### Re: How to average specific number of values backwards?

Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.0; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.

Zizi64
Volunteer

Posts: 8766
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: How to average specific number of values backwards?

Here is a simplified sheet
Attachments
work weeks.ods
OpenOffice 4.1.1 Windows 7
opog

Posts: 20
Joined: Sun Dec 29, 2013 11:26 am

### Re: How to average specific number of values backwards?

opog wrote: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

[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
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).

MrProgrammer
Moderator

Posts: 3954
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

### Re: How to average specific number of values backwards?

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.
OpenOffice 4.1.1 Windows 7
opog

Posts: 20
Joined: Sun Dec 29, 2013 11:26 am