I can see only 2 possible solutions:
1. A VBA script which reads 180 days data into array, and runs a 150-step 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).

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 02-jan-17 to 31-jan-17 and so on.

Attached Files

high average sales over large range_2.xlsx (16.5 KB, 11 views)