Sep 20 2021 05:50 AM
I want to find the average of apples and bananas for the last 4 days, from the raw DATA table, without making a Pivot table or a helper table. SAMPLE FILE ATTACHED
Is it possible using some combination of Average and Sumifs?
I don't want Pivot because it needs to be refreshed and I don't want that for this project.
I don't want helper table, because actual Data is too big and this would just make it slow.
@Peter Bartholomew @Sergei Baklan @Detlef Lewin @lori_m @Hans Vogelaar @Riny_van_Eekelen
Sep 20 2021 07:37 AM
For Apple, the following array formula confirmed with Ctrl+Shift+Enter:
=ROUND(SUMIF(Data[Date],">"&$C$18,Data[Apple])/SUM(--(FREQUENCY(IF(Data[Date]>$C$18,MATCH(Data[Date],Data[Date],0)),ROW(Data[Date])-ROW(A2)+1)>0)),0)
or the following ordinary formula:
=ROUND(SUMIF(Data[Date],">"&$C$18,Data[Apple])/SUM(--(LEN(UNIQUE(FILTER(Data[Apple],Data[Apple]>$C$18,"")))>0)),0)
Change Apple to Bananas for the other one.
Sep 20 2021 08:23 AM
Sep 20 2021 08:29 AM
Sep 20 2021 08:29 AM
IMHO, the easiest way is to keep helper table in hided sheet (or add data to data model and use cube formulas in the dashboard).
Sep 20 2021 10:03 AM
SolutionSince the OP uses 365
= LET(
filteredDates, LARGE(UNIQUE(Data[Date]), {1;2;3;4}),
total, SUM(SUMIFS( Data[Apple], Data[Date], filteredDates)),
days, COUNT(filteredDates),
total/days)
should provide the average daily count over the final 4 days or fewer if 4 do not appear. MAX is simpler and only requires the use of MAX on the 3rd line. For a single formula to produce figures for Apples and Bananas the beta release function MAP could be used
= MAP(Data[[#Headers],[Apple]:[Bananas]],
LAMBDA(fruit,
LET(
target,XLOOKUP(fruit,Data[#Headers],Data),
filteredDates,LARGE(UNIQUE(Data[Date]),{1;2;3;4}),
total, SUM(SUMIFS(target,Data[Date],filteredDates)),
days, COUNT(filteredDates),
total/days)
)
)
Maybe this represents overkill for counting bananas?
Sep 21 2021 03:46 AM
Sep 20 2021 10:03 AM
SolutionSince the OP uses 365
= LET(
filteredDates, LARGE(UNIQUE(Data[Date]), {1;2;3;4}),
total, SUM(SUMIFS( Data[Apple], Data[Date], filteredDates)),
days, COUNT(filteredDates),
total/days)
should provide the average daily count over the final 4 days or fewer if 4 do not appear. MAX is simpler and only requires the use of MAX on the 3rd line. For a single formula to produce figures for Apples and Bananas the beta release function MAP could be used
= MAP(Data[[#Headers],[Apple]:[Bananas]],
LAMBDA(fruit,
LET(
target,XLOOKUP(fruit,Data[#Headers],Data),
filteredDates,LARGE(UNIQUE(Data[Date]),{1;2;3;4}),
total, SUM(SUMIFS(target,Data[Date],filteredDates)),
days, COUNT(filteredDates),
total/days)
)
)
Maybe this represents overkill for counting bananas?