Get bottom average score based on month

[Firasath]

Hi,

I'm trying to get average score based on starting cell of Quartile-1 value and the last cell of the range. I used the same formula without applying filter on month and it worked file but when I want to retrieve the results based on a month, the formula with the filter function not working. Please find below the formula and advise where I am making mistake. The formula I am using is as follows:

=LET(m,NV!$A:$A,s,NV!$AH:$AH,f,FILTER(NV!$AH:$AH,(m>=I47)*(m<=EOMONTH(I47,0))),AVERAGE(INDIRECT((ADDRESS(MATCH(QUARTILE.INC(f,1),s,0),MATCH("Overall Score",NV!1:1,0),1))):INDIRECT(ADDRESS(MAX(FILTER(f,ROW(s))),MAX(FILTER(f,COLUMN(s))),1))))

EGS BQ.xlsb
	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
47	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23	Mar-23	Apr-23	######	Jun-23	Jul-23	Aug-23	Sep-23
48	=LET(m,NV!$A:$A,s,NV!$AH:$AH,f,FILTER(NV!$AH:$AH,(m>=I47)*(m<=EOMONTH(I47,0))),AVERAGE(INDIRECT((ADDRESS(MATCH(QUARTILE.INC(f,1),s,0),MATCH("Overall Score",NV!1:1,0),1))):INDIRECT(ADDRESS(MAX(FILTER(f,ROW(s))),MAX(FILTER(f,COLUMN(s))),1))))
BQ_Summary

Cell Formulas
Range	Formula
AM47:AO47	AM47	=IF(AM44="","",AM44)

 

[Firasath]

I meant the values in column B

No they are not result of formulas, I copied values there.

 

[Firasath]

I think, I got the reason. One of the row value, which is same as the value of Quartile-1 was not in a correct format. I tried copying pasting the value again in same cell and it gave me correct result. Though I checked the numbers before through ISNUMBER formula and all returned TRUE. I will confirm once I check the whole months data with you. Thanks.

 

[Fluff]

Glad you've sorted it & thanks for letting us know.

 

[Firasath]

Glad you've sorted it & thanks for letting us know.

Thank you very much. It was a single cell number issue, not sure why. It seems everything is okay now. Once again Thanks a lot .

 

[Firasath]

Thank you very much. It was a single cell number issue, not sure why. It seems everything is okay now. Once again Thanks a lot .

Hi Fluff,

I'm struggling to enhance this formula since yesterday to enter sort function so that it would sort by Month in Ascending order and Score by Descending order. Not sure if you can help me in this thread of I need to raise a new thread for it. Currently I need to sort different sheets manually to get the correct results. Please assist.

 

[Fluff]

You will need to start a new thread for this question. Thanks