10012017, 07:05 AM  #1 
Novice
Join Date: Sep 2017
Posts: 12

laregest 30 consecutive average of values in a 180 cell range
Good morning all, I am looking to find the highest 30 day average of sales within a 180 range. I already track total sales by month, but as an example, this doesn't work as an accurate depiction. Example: first half of Aug sales were low, second half were high; first half of September sales were high, and the second were low. The period from mid august to mid September was the highest sales period. any clue how to find this info? thanks in advance. Mike 
10012017, 07:55 AM  #2 
Moderator
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 1,990

Please post a sample sheet with some data and expected results  Thx
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be) 
10012017, 09:10 AM  #3 
Novice
Join Date: Sep 2017
Posts: 12

Peco,
Thanks. Please see attached example. Highlighted cell represents the desired result. I want this to be an automated so that I can determine worst case scenario for demand of an item. Initially, I had thought that using =average(Large(range{1,2,3,4,5,...30})) would do the trick, but that will give me the average of the 30 highest days of sales, not the highest 30 days of sales....make sense? Mike 
10012017, 11:15 AM  #4 
Competent Performer
Join Date: May 2017
Posts: 116

I can see only 2 possible solutions:
1. A VBA script which reads 180 days data into array, and runs a 150step cycle, calculates an average (there is a shortcut when you save before the first value in cycle, so you can deduce it in next cycle and add a new last value instead) for every step, and when this is more than max average until this step, saves the new max value into variable(s) or another array (probably you need period start and end dates too), or into predefined Name; 2. A separate table with columns PeriodStart and PeriodEnd which are calculated for first row as (TODAY()  180 +ROW()  X) AND (TODAY()  180 + ROW()  X) + 30, where X is the number of row for 1st period. For every next row, the period shifts 1 day, and you'll have 150 rows of data in this table. In 3rd column, average for period is calculated from your data table (Using SUMIFS() formulas). And then you can calculate max average using MAX() function on column with averages (calculate it as Name  then it will be much easier to use it in formulas). 
10012017, 10:56 PM  #5 
Expert
Join Date: Oct 2015
Posts: 832

I am not sure, but you can try to take a look at row 14 in the attached file.
Cell B14 shows the average sales for first 30 days, cell C14 shows average sales for 02jan17 to 31jan17 and so on. 
10012017, 11:09 PM  #6 
Moderator
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 1,990

Not really
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be) 
10022017, 05:23 AM  #7 
Competent Performer
Join Date: May 2017
Posts: 116

My version how to do it

10022017, 06:28 AM  #8 
Novice
Join Date: Sep 2017
Posts: 12


10022017, 07:22 AM  #9 
Competent Performer
Join Date: May 2017
Posts: 116

OK, it looks like our firewall let it through!
Btw, 7Zip is free, and allows to pack and unpack various formats. Last edited by ArviLaanemets; 10022017 at 07:23 AM. Reason: Edit 
10162017, 10:12 AM  #10 
Novice
Join Date: Oct 2017
Posts: 1

If start date is 1/1/17 formula is
=LARGE((SUMIF(OFFSET($B$2,0,ROW($1:$180)ROW($1:$1),1,30),">0")/30),1) ARRAY formula is used To enter ARRAY formula Paste the formula Press F2 Press Ctrl+Shift+Enter keys together. formula will be covered with{} brackets by excel. 
10192017, 04:03 AM  #11 
Competent Performer
Join Date: Oct 2015
Posts: 140

Nonarray formula:
=MAX(INDEX(SUBTOTAL(1,(OFFSET($B$2,0,COLUMN($B$2:$ AE$2)2,1,30))),)) 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow  FUGMAN  Excel Programming  7  02052017 08:37 AM 
average formal if cell contains number and text  Michael Labuschagne  Excel  2  12082016 06:35 AM 
If value of cell A Matches a value in a Range of cells (column) then add value of cell A to cell C  rick10r  Excel  1  07052016 12:07 PM 
Filling the consecutive cell by day.  aligahk06  Excel  1  09152010 08:17 AM 
calculate average depending on dropdown values  virencm  Word VBA  0  08062010 06:10 PM 