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

Discuss the spreadsheet application

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

Postby opog » Wed Aug 14, 2019 3:26 am

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.

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

Postby RusselB » Wed Aug 14, 2019 5:10 am

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.
User avatar
RusselB
Moderator
 
Posts: 5853
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: How to average specific number of values backwards?

Postby opog » Wed Aug 14, 2019 5:55 am

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?

Postby Zizi64 » Wed Aug 14, 2019 6:55 am

Please upload an .ods file format real sample file here.
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.
User avatar
Zizi64
Volunteer
 
Posts: 8766
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to average specific number of values backwards?

Postby opog » Wed Aug 14, 2019 7:04 am

Here is a simplified sheet
Attachments
work weeks.ods
(13.64 KiB) Downloaded 16 times
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?

Postby MrProgrammer » Wed Aug 14, 2019 8:05 pm

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
(20.96 KiB) Downloaded 12 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
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).
User avatar
MrProgrammer
Moderator
 
Posts: 3954
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: How to average specific number of values backwards?

Postby opog » Thu Aug 15, 2019 3:23 am

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


Return to Calc

Who is online

Users browsing this forum: No registered users and 6 guests