我从这个问题中提取了一个公式：
为我提供每天平均值的正确计算。
我需要使其能够响应数据验证菜单，在该菜单中，用户可以选择他/她需要获得平均水平的时间段：最后24小时，过去3天，最后7天，上个月等等。
我还需要能够从平均计算中排除当今的时间，因为一天中的前几个小时始终使平均水平下降。
我有这个平均计算的公式，这给了我正确的平均值（除了它不排除当今的平均水平）：
=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", )))
然后，我将此公式作为平均值的一个示例，该公式对时间段的数据验证菜单有响应。但是在这里，对于此用例，平均计算是错误的。我不知道如何结合两种解决方案：
=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)))
这是可能的吗？有什么建议吗？
我的文件：
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
发布评论
评论（1）
使用：
use: