Forum Discussion
Can I average the data in the table when the dates are not grouped? Without using Pivot Table?
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.
PeterBartholomew1 SergeiBaklan Detlef_Lewin lori_m HansVogelaar Riny_van_Eekelen
Since 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?
6 Replies
Since 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?
- I do have Microsoft 365, but I still don't have Lambda or map.
So I'll try the first option.
Also, will be reconsidering power query for the same as suggested by SergeiBaklan
But thank you so much for this. Much appreciated.
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.
- This isn't working in the way I applied it, can you attach the excel file you worked this upon?
Also, is there a way I can find the max apple sold within the four dates instead of average?
Through one formula?
Include that too if you can.
Thank you.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).
