I have a formula taken from this question: Average profit per day formula in 1 single cell

Which gives me the correct calculation for average value per day. I need to make it responsive to a data validation menu where the user can choose the time period for which he/her needs to get the average from: last 24h, last 3 days, last 7 days, last month and so on.

I also need to be able to exclude the present day from the average calculation because the first few hours of the day always make the average go down.

I have this formula for the average calculation which gives me the correct average (except that it is not excluding the present day):

=INDEX(AVERAGE(QUERY(IFNA(QUERY(
 INDEX(SORTN(SORT({INT(B3:B), D3:D}, ROW(B3:B), ), 9^9, 2, 1, 1),,2),  "offset 1", )-
 INDEX(SORTN(SORT({INT(B3:B), D3:D}, ROW(B3:B), ), 9^9, 2, 1, 1),,2)), "offset 1", )))

Then I have this formula as an example of an average that is responsive to a data validation menu with the time period. But here the calculation for the average is wrong for this use case. I can't figure out how to combine the 2 solutions:

=ARRAYFORMULA( AVERAGE(QUERY({IFERROR(DATEVALUE(B3:B)),D3:D}, 
 "select Col2 
  where Col2 is not null 
  and Col1 <= "&INT(MAX(B3:B))&"
  and Col1 >  "&INT(MAX(B3:B))-(
IFERROR(
          VLOOKUP(
           SUBSTITUTE(F2," ",""),
           {"24HOURS",1;
            "2DAYS",2;
            "3DAYS",3; 
            "7DAYS",7; 
            "2WEEKS",14; 
            "1MONTH",30;
            "3MONTHS",90; 
            "6MONTHS",180; 
            "1YEAR",365;
            "2YEARS",730; 
            "3YEARS",1095},
           2,FALSE))
)-1, 0)))

Is such a thing possible? Any suggestions?

My file: https://docs.google.com/spreadsheets/d/1ExXtmQ8nyuV1o_UtabVJ-TifIbORItFMWjtN6ZlruWc/edit?usp=sharing

CodePudding user response：

use:

=INDEX(AVERAGE(QUERY(IFNA(QUERY(
 INDEX(SORTN(QUERY(SORT({INT(B3:B), D3:D}, ROW(B3:B), ), "where Col1 <> "&TODAY()&"and Col1 >= "&TODAY()-VLOOKUP(SUBSTITUTE(F2, " ", ), 
{"24HOURS",1; "2DAYS",2; "3DAYS",3; "7DAYS",7; "2WEEKS",14; "1MONTH",30; "3MONTHS",90; "6MONTHS",180; "1YEAR",365; "2YEARS",730; "3YEARS",1095}, 2, )), 9^9, 2, 1, 1),,2),  "offset 1", )-
 INDEX(SORTN(QUERY(SORT({INT(B3:B), D3:D}, ROW(B3:B), ), "where Col1 <> "&TODAY()&"and Col1 >= "&TODAY()-VLOOKUP(SUBSTITUTE(F2, " ", ), 
{"24HOURS",1; "2DAYS",2; "3DAYS",3; "7DAYS",7; "2WEEKS",14; "1MONTH",30; "3MONTHS",90; "6MONTHS",180; "1YEAR",365; "2YEARS",730; "3YEARS",1095}, 2, )), 9^9, 2, 1, 1),,2)), "offset 1", )))