 SOLVED

# 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.
@Peter Bartholomew @Sergei Baklan @Detlef Lewin @lori_m @Hans Vogelaar @Riny_van_Eekelen 6 Replies

# Re: Can I average the data in the table when the dates are not grouped? Without using Pivot Table?

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.

# Re: Can I average the data in the table when the dates are not grouped? Without using Pivot Table?

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.

# Re: Can I average the data in the table when the dates are not grouped? Without using Pivot Table?

Here you go. I can't think of a single formula for the maximum per day yet.

# Re: Can I average the data in the table when the dates are not grouped? Without using Pivot Table?

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).

best response confirmed by Nishkarsh31 (Contributor)
Solution

# Re: Can I average the data in the table when the dates are not grouped? Without using Pivot Table?

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(
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? # Re: Can I average the data in the table when the dates are not grouped? Without using Pivot Table?

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 @Sergei Baklan

But thank you so much for this. Much appreciated.