Get bottom average score based on month

Firasath · Apr 3, 2023

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)

frabulator · Apr 3, 2023

A possible solution is to replace the INDIRECT function with INDEX function to create a dynamic range based on the results.

You can modify your formula as follows:

VBA Code:

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

The formula uses the INDEX function to dynamically create a range based on the filtered results.

The formula assumes that the data is in columns A to AH and that the score values are in the column AH.

-----

Without seeing the data and filter conditions, it's difficult to determine the exact issue. However, one possibility is that the FILTER function is not returning the correct range of cells for the calculation of the average score.

You can this by trying to use the VLOOKUP function instead of the INDIRECT function to retrieve the the last cell of the range. The VLOOKUP function can help you find the correct cells even when the data is filtered.

Here's an example

Code:

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

In this formula, you use VLOOKUP to find the Quartile-1 value and the last cell of the range and the column number for the "Overall Score" header.

Fluff · Apr 3, 2023

Can you post some sample data.

Firasath · Apr 4, 2023

frabulator said:
A possible solution is to replace the INDIRECT function with INDEX function to create a dynamic range based on the results.

You can modify your formula as follows:

VBA Code:

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

The formula uses the INDEX function to dynamically create a range based on the filtered results.

The formula assumes that the data is in columns A to AH and that the score values are in the column AH.

-----

Without seeing the data and filter conditions, it's difficult to determine the exact issue. However, one possibility is that the FILTER function is not returning the correct range of cells for the calculation of the average score.

You can this by trying to use the VLOOKUP function instead of the INDIRECT function to retrieve the the last cell of the range. The VLOOKUP function can help you find the correct cells even when the data is filtered.

Here's an example

Code:

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

In this formula, you use VLOOKUP to find the Quartile-1 value and the last cell of the range and the column number for the "Overall Score" header.

Thanks for your response.

I tried above formula but is is giving error stating something "Are you trying to type formula", i am pasting some data below with your suggested formula. Appreciate to check and update the formula accordingly.

Book6

A

B

C

D

E

F

1

Month

Overall Score

Jul-22

Aug-22

Feb-23

2

Jul-22

76.56

=LET(m,$A:$A,s,$B:$B,f,FILTER($B:$B,(m>=D$1)*(m<=EOMONTH(D$1,0))),q1,VLOOKUP(QUARTILE.INC(f,1),s,1,FALSE), ls,VLOOKUP("Overall Score",A$1:B$1,MATCH("Overall Score",$A$1:$B$1,0),FALSE), AVERAGE(INDIRECT((ADDRESS(MATCH(q1,s,0),MATCH(ls,A$1:B$1,0),1))):INDIRECT(ADDRESS(MAX(FILTER(f,ROW(s))),MAX(FILTER(f,COLUMN(s))),1))))

3

Jul-22

75.00

4

Jul-22

75.00

5

Jul-22

75.00

6

Jul-22

75.00

7

Jul-22

68.75

8

Jul-22

68.75

9

Jul-22

68.75

10

Jul-22

68.75

11

Jul-22

68.75

12

Jul-22

68.75

13

Jul-22

64.84

14

Jul-22

62.50

15

Jul-22

60.94

16

Jul-22

60.94

17

Jul-22

60.94

18

Jul-22

59.90

19

Jul-22

57.03

20

Jul-22

53.13

21

Jul-22

53.13

22

Jul-22

53.13

23

Jul-22

53.13

24

Jul-22

53.13

25

Jul-22

52.52

26

Jul-22

49.22

27

Jul-22

49.22

28

Jul-22

49.22

29

Jul-22

49.22

30

Jul-22

48.61

31

Jul-22

45.31

32

Jul-22

45.31

33

Jul-22

45.31

34

Jul-22

45.31

35

Jul-22

44.70

36

Jul-22

41.41

37

Jul-22

41.41

38

Jul-22

41.41

39

Jul-22

41.41

40

Jul-22

41.41

41

Jul-22

41.41

42

Jul-22

41.41

43

Jul-22

41.41

44

Jul-22

39.38

45

Jul-22

37.76

46

Jul-22

37.50

47

Jul-22

37.50

48

Jul-22

37.50

49

Jul-22

37.50

50

Jul-22

37.50

51

Jul-22

37.50

52

Jul-22

37.50

53

Jul-22

37.50

54

Jul-22

37.50

55

Jul-22

37.50

56

Jul-22

37.50

57

Jul-22

37.50

58

Jul-22

37.50

59

Jul-22

37.50

60

Jul-22

37.50

61

Jul-22

37.50

62

Jul-22

37.50

63

Jul-22

37.50

64

Jul-22

37.50

65

Jul-22

37.50

66

Jul-22

37.50

67

Jul-22

37.50

68

Jul-22

37.50

69

Jul-22

37.50

70

Jul-22

37.50

71

Jul-22

36.89

72

Jul-22

31.25

73

Jul-22

31.25

74

Jul-22

31.25

75

Jul-22

30.28

76

Jul-22

30.00

77

Jul-22

28.65

78

Jul-22

25.17

79

Jul-22

23.44

80

Jul-22

21.27

81

Jul-22

21.27

82

Jul-22

21.27

83

Jul-22

20.66

84

Jul-22

19.37

85

Jul-22

0.00

86

Jul-22

0.00

87

Jul-22

0.00

88

Jul-22

0.00

89

Aug-22

82.03

90

Aug-22

68.75

91

Aug-22

68.75

92

Aug-22

68.75

93

Aug-22

64.84

94

Aug-22

61.53

95

Aug-22

60.94

96

Aug-22

60.94

97

Aug-22

60.94

98

Aug-22

60.63

99

Aug-22

57.03

100

Aug-22

57.03

101

Aug-22

53.72

102

Aug-22

53.13

103

Aug-22

53.13

104

Aug-22

50.00

105

Aug-22

50.00

106

Aug-22

50.00

107

Aug-22

49.22

108

Aug-22

49.22

109

Aug-22

49.22

110

Aug-22

49.22

111

Aug-22

49.22

112

Aug-22

45.31

113

Aug-22

45.31

114

Aug-22

41.88

115

Aug-22

41.41

116

Aug-22

41.41

117

Aug-22

41.41

118

Aug-22

37.50

119

Aug-22

37.50

120

Aug-22

37.50

121

Aug-22

37.50

122

Aug-22

37.50

123

Aug-22

37.50

124

Aug-22

37.50

125

Aug-22

37.50

126

Aug-22

37.50

127

Aug-22

37.50

128

Aug-22

37.50

129

Aug-22

37.50

130

Aug-22

37.50

131

Aug-22

37.50

132

Aug-22

37.50

133

Aug-22

37.50

134

Aug-22

37.50

135

Aug-22

37.50

136

Aug-22

37.50

137

Aug-22

37.50

138

Aug-22

37.50

139

Aug-22

37.50

140

Aug-22

37.50

141

Aug-22

37.50

142

Aug-22

37.50

143

Aug-22

37.50

144

Aug-22

37.50

145

Aug-22

37.50

146

Aug-22

37.50

147

Aug-22

37.50

148

Aug-22

37.50

149

Aug-22

37.50

150

Aug-22

37.50

151

Aug-22

37.50

152

Aug-22

34.79

153

Aug-22

32.86

154

Aug-22

30.28

155

Aug-22

27.34

156

Aug-22

26.68

157

Aug-22

21.88

158

Aug-22

20.00

159

Aug-22

20.00

160

Aug-22

15.02

161

Aug-22

12.25

162

Aug-22

6.51

163

Aug-22

5.03

164

Aug-22

5.03

165

Aug-22

0.00

166

Aug-22

0.00

167

Aug-22

0.00

168

Feb-23

93.75

169

Feb-23

93.75

170

Feb-23

93.75

171

Feb-23

93.75

172

Feb-23

93.75

173

Feb-23

93.75

174

Feb-23

93.75

175

Feb-23

93.75

176

Feb-23

93.75

177

Feb-23

93.75

178

Feb-23

93.75

179

Feb-23

91.04

180

Feb-23

89.84

181

Feb-23

89.84

182

Feb-23

89.84

183

Feb-23

89.11

184

Feb-23

85.94

185

Feb-23

85.94

186

Feb-23

85.94

187

Feb-23

82.03

188

Feb-23

82.03

189

Feb-23

82.03

190

Feb-23

80.36

191

Feb-23

78.13

192

Feb-23

78.13

193

Feb-23

78.13

194

Feb-23

77.52

195

Feb-23

77.52

196

Feb-23

77.52

197

Feb-23

75.94

198

Feb-23

74.22

199

Feb-23

74.22

200

Feb-23

74.22

201

Feb-23

71.88

202

Feb-23

71.43

203

Feb-23

70.31

204

Feb-23

70.31

205

Feb-23

66.41

206

Feb-23

66.41

207

Feb-23

66.41

208

Feb-23

66.41

209

Feb-23

65.80

210

Feb-23

62.50

211

Feb-23

62.50

212

Feb-23

62.50

213

Feb-23

62.50

214

Feb-23

62.50

215

Feb-23

62.50

216

Feb-23

62.50

217

Feb-23

62.50

218

Feb-23

62.50

219

Feb-23

62.50

220

Feb-23

62.50

221

Feb-23

62.50

222

Feb-23

62.50

223

Feb-23

62.50

224

Feb-23

62.50

225

Feb-23

62.50

226

Feb-23

62.50

227

Feb-23

62.50

228

Feb-23

62.50

229

Feb-23

62.50

230

Feb-23

62.50

231

Feb-23

62.50

232

Feb-23

62.50

233

Feb-23

62.50

234

Feb-23

62.50

235

Feb-23

62.50

236

Feb-23

62.50

237

Feb-23

62.50

238

Feb-23

62.50

239

Feb-23

62.50

240

Feb-23

62.50

241

Feb-23

62.50

242

Feb-23

62.50

243

Feb-23

62.50

244

Feb-23

62.50

245

Feb-23

62.50

246

Feb-23

62.50

247

Feb-23

62.50

248

Feb-23

62.50

249

Feb-23

62.50

250

Feb-23

62.50

251

Feb-23

62.50

252

Feb-23

62.50

253

Feb-23

62.50

254

Feb-23

62.50

255

Feb-23

62.50

256

Feb-23

62.50

257

Feb-23

62.50

258

Feb-23

62.20

259

Feb-23

61.02

260

Feb-23

60.12

261

Feb-23

59.79

262

Feb-23

59.79

263

Feb-23

57.98

264

Feb-23

57.86

265

Feb-23

56.25

266

Feb-23

56.25

267

Feb-23

54.38

268

Feb-23

54.38

269

Feb-23

54.38

270

Feb-23

50.00

271

Feb-23

50.00

272

Feb-23

50.00

273

Feb-23

50.00

274

Feb-23

48.44

275

Feb-23

46.27

276

Feb-23

46.27

277

Feb-23

46.27

278

Feb-23

44.53

279

Feb-23

42.79

280

Feb-23

38.89

281

Feb-23

36.12

282

Feb-23

34.42

283

Feb-23

30.03

284

Feb-23

30.03

285

Feb-23

30.00

286

Feb-23

30.00

287

Feb-23

28.91

288

Feb-23

25.00

289

Feb-23

25.00

290

Feb-23

25.00

291

Feb-23

25.00

292

Feb-23

25.00

293

Feb-23

25.00

Sheet1

Firasath · Apr 4, 2023

Fluff said:
Can you post some sample data.

Hi Fluff,

Posted the sample data for your kind inputs.

Fluff · Apr 4, 2023

Thanks for that.
Is this what you want?

Fluff.xlsm

A

B

C

D

E

F

1

Month

Overall Score

01/07/2022

01/08/2022

01/02/2023

2

01/07/2022

76.5625

30.286007

30.513889

41.695175

3

01/07/2022

75

4

01/07/2022

75

5

01/07/2022

75

6

01/07/2022

75

7

01/07/2022

68.75

8

01/07/2022

68.75

9

01/07/2022

68.75

10

01/07/2022

68.75

11

01/07/2022

68.75

12

01/07/2022

68.75

13

01/07/2022

64.84375

14

01/07/2022

62.5

15

01/07/2022

60.9375

16

01/07/2022

60.9375

17

01/07/2022

60.9375

18

01/07/2022

59.8952184

19

01/07/2022

57.03125

20

01/07/2022

53.125

21

01/07/2022

53.125

22

01/07/2022

53.125

23

01/07/2022

53.125

24

01/07/2022

53.125

25

01/07/2022

52.5162338

26

01/07/2022

49.21875

27

01/07/2022

49.21875

28

01/07/2022

49.21875

29

01/07/2022

49.21875

30

01/07/2022

48.6099838

31

01/07/2022

45.3125

32

01/07/2022

45.3125

33

01/07/2022

45.3125

34

01/07/2022

45.3125

35

01/07/2022

44.7037338

36

01/07/2022

41.40625

37

01/07/2022

41.40625

38

01/07/2022

41.40625

39

01/07/2022

41.40625

40

01/07/2022

41.40625

41

01/07/2022

41.40625

42

01/07/2022

41.40625

43

01/07/2022

41.40625

44

01/07/2022

39.375

45

01/07/2022

37.7582645

46

01/07/2022

37.5

47

01/07/2022

37.5

48

01/07/2022

37.5

49

01/07/2022

37.5

50

01/07/2022

37.5

51

01/07/2022

37.5

52

01/07/2022

37.5

53

01/07/2022

37.5

54

01/07/2022

37.5

55

01/07/2022

37.5

56

01/07/2022

37.5

57

01/07/2022

37.5

58

01/07/2022

37.5

59

01/07/2022

37.5

60

01/07/2022

37.5

61

01/07/2022

37.5

62

01/07/2022

37.5

63

01/07/2022

37.5

64

01/07/2022

37.5

65

01/07/2022

37.5

66

01/07/2022

37.5

67

01/07/2022

37.5

68

01/07/2022

37.5

69

01/07/2022

37.5

70

01/07/2022

37.5

71

01/07/2022

36.8912338

72

01/07/2022

31.25

73

01/07/2022

31.25

74

01/07/2022

31.25

75

01/07/2022

30.2849928

76

01/07/2022

30

77

01/07/2022

28.6452184

78

01/07/2022

25.1724838

79

01/07/2022

23.4375

80

01/07/2022

21.2662338

81

01/07/2022

21.2662338

82

01/07/2022

21.2662338

83

01/07/2022

20.6574675

84

01/07/2022

19.3747101

85

01/07/2022

0

86

01/07/2022

0

87

01/07/2022

0

88

01/07/2022

0

89

01/08/2022

82.03125

90

01/08/2022

68.75

91

01/08/2022

68.75

92

01/08/2022

68.75

93

01/08/2022

64.84375

94

01/08/2022

61.5349928

95

01/08/2022

60.9375

96

01/08/2022

60.9375

97

01/08/2022

60.9375

98

01/08/2022

60.6331169

99

01/08/2022

57.03125

100

01/08/2022

57.03125

101

01/08/2022

53.7224928

102

01/08/2022

53.125

103

01/08/2022

53.125

104

01/08/2022

50

105

01/08/2022

50

106

01/08/2022

50

107

01/08/2022

49.21875

108

01/08/2022

49.21875

109

01/08/2022

49.21875

110

01/08/2022

49.21875

111

01/08/2022

49.21875

112

01/08/2022

45.3125

113

01/08/2022

45.3125

114

01/08/2022

41.875

115

01/08/2022

41.40625

116

01/08/2022

41.40625

117

01/08/2022

41.40625

118

01/08/2022

37.5

119

01/08/2022

37.5

120

01/08/2022

37.5

121

01/08/2022

37.5

122

01/08/2022

37.5

123

01/08/2022

37.5

124

01/08/2022

37.5

125

01/08/2022

37.5

126

01/08/2022

37.5

127

01/08/2022

37.5

128

01/08/2022

37.5

129

01/08/2022

37.5

130

01/08/2022

37.5

131

01/08/2022

37.5

132

01/08/2022

37.5

133

01/08/2022

37.5

134

01/08/2022

37.5

135

01/08/2022

37.5

136

01/08/2022

37.5

137

01/08/2022

37.5

138

01/08/2022

37.5

139

01/08/2022

37.5

140

01/08/2022

37.5

141

01/08/2022

37.5

142

01/08/2022

37.5

143

01/08/2022

37.5

144

01/08/2022

37.5

145

01/08/2022

37.5

146

01/08/2022

37.5

147

01/08/2022

37.5

148

01/08/2022

37.5

149

01/08/2022

37.5

150

01/08/2022

37.5

151

01/08/2022

37.5

152

01/08/2022

34.7943723

153

01/08/2022

32.8617811

154

01/08/2022

30.2849928

155

01/08/2022

27.34375

156

01/08/2022

26.6774892

157

01/08/2022

21.875

158

01/08/2022

20

159

01/08/2022

20

160

01/08/2022

15.0224775

161

01/08/2022

12.2474747

162

01/08/2022

6.50826446

163

01/08/2022

5.03246753

164

01/08/2022

5.03246753

165

01/08/2022

0

166

01/08/2022

0

167

01/08/2022

0

168

01/02/2023

93.75

169

01/02/2023

93.75

170

01/02/2023

93.75

171

01/02/2023

93.75

172

01/02/2023

93.75

173

01/02/2023

93.75

174

01/02/2023

93.75

175

01/02/2023

93.75

176

01/02/2023

93.75

177

01/02/2023

93.75

178

01/02/2023

93.75

179

01/02/2023

91.0443723

180

01/02/2023

89.84375

181

01/02/2023

89.84375

182

01/02/2023

89.84375

183

01/02/2023

89.1117811

184

01/02/2023

85.9375

185

01/02/2023

85.9375

186

01/02/2023

85.9375

187

01/02/2023

82.03125

188

01/02/2023

82.03125

189

01/02/2023

82.03125

190

01/02/2023

80.3571429

191

01/02/2023

78.125

192

01/02/2023

78.125

193

01/02/2023

78.125

194

01/02/2023

77.5162338

195

01/02/2023

77.5162338

196

01/02/2023

77.5162338

197

01/02/2023

75.9435877

198

01/02/2023

74.21875

199

01/02/2023

74.21875

200

01/02/2023

74.21875

201

01/02/2023

71.875

202

01/02/2023

71.4285714

203

01/02/2023

70.3125

204

01/02/2023

70.3125

205

01/02/2023

66.40625

206

01/02/2023

66.40625

207

01/02/2023

66.40625

208

01/02/2023

66.40625

209

01/02/2023

65.7974838

210

01/02/2023

62.5

211

01/02/2023

62.5

212

01/02/2023

62.5

213

01/02/2023

62.5

214

01/02/2023

62.5

215

01/02/2023

62.5

216

01/02/2023

62.5

217

01/02/2023

62.5

218

01/02/2023

62.5

219

01/02/2023

62.5

220

01/02/2023

62.5

221

01/02/2023

62.5

222

01/02/2023

62.5

223

01/02/2023

62.5

224

01/02/2023

62.5

225

01/02/2023

62.5

226

01/02/2023

62.5

227

01/02/2023

62.5

228

01/02/2023

62.5

229

01/02/2023

62.5

230

01/02/2023

62.5

231

01/02/2023

62.5

232

01/02/2023

62.5

233

01/02/2023

62.5

234

01/02/2023

62.5

235

01/02/2023

62.5

236

01/02/2023

62.5

237

01/02/2023

62.5

238

01/02/2023

62.5

239

01/02/2023

62.5

240

01/02/2023

62.5

241

01/02/2023

62.5

242

01/02/2023

62.5

243

01/02/2023

62.5

244

01/02/2023

62.5

245

01/02/2023

62.5

246

01/02/2023

62.5

247

01/02/2023

62.5

248

01/02/2023

62.5

249

01/02/2023

62.5

250

01/02/2023

62.5

251

01/02/2023

62.5

252

01/02/2023

62.5

253

01/02/2023

62.5

254

01/02/2023

62.5

255

01/02/2023

62.5

256

01/02/2023

62.5

257

01/02/2023

62.5

258

01/02/2023

62.1956169

259

01/02/2023

61.0242031

260

01/02/2023

60.1229128

261

01/02/2023

59.7943723

262

01/02/2023

59.7943723

263

01/02/2023

57.9849838

264

01/02/2023

57.8617811

265

01/02/2023

56.25

266

01/02/2023

56.25

267

01/02/2023

54.3831169

268

01/02/2023

54.3831169

269

01/02/2023

54.3831169

270

01/02/2023

50

271

01/02/2023

50

272

01/02/2023

50

273

01/02/2023

50

274

01/02/2023

48.4375

275

01/02/2023

46.2662338

276

01/02/2023

46.2662338

277

01/02/2023

46.2662338

278

01/02/2023

44.53125

279

01/02/2023

42.7934991

280

01/02/2023

38.8872491

281

01/02/2023

36.1201299

282

01/02/2023

34.4155844

283

01/02/2023

30.0324675

284

01/02/2023

30.0324675

285

01/02/2023

30

286

01/02/2023

30

287

01/02/2023

28.90625

288

01/02/2023

25

289

01/02/2023

25

290

01/02/2023

25

291

01/02/2023

25

292

01/02/2023

25

293

01/02/2023

25

Lists

Cell Formulas
Range	Formula
D2:F2	D2	=LET(m,$A2:$A500,s,$B2:$B500,f,FILTER(s,(m>=D$1)*(m<=EOMONTH(D$1,0))),AVERAGE(DROP(f,MATCH(QUARTILE.INC(f,1),f,0))))

Firasath · Apr 5, 2023

Fluff said:
Thanks for that.
Is this what you want?

Fluff.xlsm
A B C D E F
1 Month Overall Score 01/07/2022 01/08/2022 01/02/2023
2 01/07/2022 76.5625 30.286007 30.513889 41.695175
3 01/07/2022 75
4 01/07/2022 75
5 01/07/2022 75
6 01/07/2022 75
7 01/07/2022 68.75
8 01/07/2022 68.75
9 01/07/2022 68.75
10 01/07/2022 68.75
11 01/07/2022 68.75
12 01/07/2022 68.75
13 01/07/2022 64.84375
14 01/07/2022 62.5
15 01/07/2022 60.9375
16 01/07/2022 60.9375
17 01/07/2022 60.9375
18 01/07/2022 59.8952184
19 01/07/2022 57.03125
20 01/07/2022 53.125
21 01/07/2022 53.125
22 01/07/2022 53.125
23 01/07/2022 53.125
24 01/07/2022 53.125
25 01/07/2022 52.5162338
26 01/07/2022 49.21875
27 01/07/2022 49.21875
28 01/07/2022 49.21875
29 01/07/2022 49.21875
30 01/07/2022 48.6099838
31 01/07/2022 45.3125
32 01/07/2022 45.3125
33 01/07/2022 45.3125
34 01/07/2022 45.3125
35 01/07/2022 44.7037338
36 01/07/2022 41.40625
37 01/07/2022 41.40625
38 01/07/2022 41.40625
39 01/07/2022 41.40625
40 01/07/2022 41.40625
41 01/07/2022 41.40625
42 01/07/2022 41.40625
43 01/07/2022 41.40625
44 01/07/2022 39.375
45 01/07/2022 37.7582645
46 01/07/2022 37.5
47 01/07/2022 37.5
48 01/07/2022 37.5
49 01/07/2022 37.5
50 01/07/2022 37.5
51 01/07/2022 37.5
52 01/07/2022 37.5
53 01/07/2022 37.5
54 01/07/2022 37.5
55 01/07/2022 37.5
56 01/07/2022 37.5
57 01/07/2022 37.5
58 01/07/2022 37.5
59 01/07/2022 37.5
60 01/07/2022 37.5
61 01/07/2022 37.5
62 01/07/2022 37.5
63 01/07/2022 37.5
64 01/07/2022 37.5
65 01/07/2022 37.5
66 01/07/2022 37.5
67 01/07/2022 37.5
68 01/07/2022 37.5
69 01/07/2022 37.5
70 01/07/2022 37.5
71 01/07/2022 36.8912338
72 01/07/2022 31.25
73 01/07/2022 31.25
74 01/07/2022 31.25
75 01/07/2022 30.2849928
76 01/07/2022 30
77 01/07/2022 28.6452184
78 01/07/2022 25.1724838
79 01/07/2022 23.4375
80 01/07/2022 21.2662338
81 01/07/2022 21.2662338
82 01/07/2022 21.2662338
83 01/07/2022 20.6574675
84 01/07/2022 19.3747101
85 01/07/2022 0
86 01/07/2022 0
87 01/07/2022 0
88 01/07/2022 0
89 01/08/2022 82.03125
90 01/08/2022 68.75
91 01/08/2022 68.75
92 01/08/2022 68.75
93 01/08/2022 64.84375
94 01/08/2022 61.5349928
95 01/08/2022 60.9375
96 01/08/2022 60.9375
97 01/08/2022 60.9375
98 01/08/2022 60.6331169
99 01/08/2022 57.03125
100 01/08/2022 57.03125
101 01/08/2022 53.7224928
102 01/08/2022 53.125
103 01/08/2022 53.125
104 01/08/2022 50
105 01/08/2022 50
106 01/08/2022 50
107 01/08/2022 49.21875
108 01/08/2022 49.21875
109 01/08/2022 49.21875
110 01/08/2022 49.21875
111 01/08/2022 49.21875
112 01/08/2022 45.3125
113 01/08/2022 45.3125
114 01/08/2022 41.875
115 01/08/2022 41.40625
116 01/08/2022 41.40625
117 01/08/2022 41.40625
118 01/08/2022 37.5
119 01/08/2022 37.5
120 01/08/2022 37.5
121 01/08/2022 37.5
122 01/08/2022 37.5
123 01/08/2022 37.5
124 01/08/2022 37.5
125 01/08/2022 37.5
126 01/08/2022 37.5
127 01/08/2022 37.5
128 01/08/2022 37.5
129 01/08/2022 37.5
130 01/08/2022 37.5
131 01/08/2022 37.5
132 01/08/2022 37.5
133 01/08/2022 37.5
134 01/08/2022 37.5
135 01/08/2022 37.5
136 01/08/2022 37.5
137 01/08/2022 37.5
138 01/08/2022 37.5
139 01/08/2022 37.5
140 01/08/2022 37.5
141 01/08/2022 37.5
142 01/08/2022 37.5
143 01/08/2022 37.5
144 01/08/2022 37.5
145 01/08/2022 37.5
146 01/08/2022 37.5
147 01/08/2022 37.5
148 01/08/2022 37.5
149 01/08/2022 37.5
150 01/08/2022 37.5
151 01/08/2022 37.5
152 01/08/2022 34.7943723
153 01/08/2022 32.8617811
154 01/08/2022 30.2849928
155 01/08/2022 27.34375
156 01/08/2022 26.6774892
157 01/08/2022 21.875
158 01/08/2022 20
159 01/08/2022 20
160 01/08/2022 15.0224775
161 01/08/2022 12.2474747
162 01/08/2022 6.50826446
163 01/08/2022 5.03246753
164 01/08/2022 5.03246753
165 01/08/2022 0
166 01/08/2022 0
167 01/08/2022 0
168 01/02/2023 93.75
169 01/02/2023 93.75
170 01/02/2023 93.75
171 01/02/2023 93.75
172 01/02/2023 93.75
173 01/02/2023 93.75
174 01/02/2023 93.75
175 01/02/2023 93.75
176 01/02/2023 93.75
177 01/02/2023 93.75
178 01/02/2023 93.75
179 01/02/2023 91.0443723
180 01/02/2023 89.84375
181 01/02/2023 89.84375
182 01/02/2023 89.84375
183 01/02/2023 89.1117811
184 01/02/2023 85.9375
185 01/02/2023 85.9375
186 01/02/2023 85.9375
187 01/02/2023 82.03125
188 01/02/2023 82.03125
189 01/02/2023 82.03125
190 01/02/2023 80.3571429
191 01/02/2023 78.125
192 01/02/2023 78.125
193 01/02/2023 78.125
194 01/02/2023 77.5162338
195 01/02/2023 77.5162338
196 01/02/2023 77.5162338
197 01/02/2023 75.9435877
198 01/02/2023 74.21875
199 01/02/2023 74.21875
200 01/02/2023 74.21875
201 01/02/2023 71.875
202 01/02/2023 71.4285714
203 01/02/2023 70.3125
204 01/02/2023 70.3125
205 01/02/2023 66.40625
206 01/02/2023 66.40625
207 01/02/2023 66.40625
208 01/02/2023 66.40625
209 01/02/2023 65.7974838
210 01/02/2023 62.5
211 01/02/2023 62.5
212 01/02/2023 62.5
213 01/02/2023 62.5
214 01/02/2023 62.5
215 01/02/2023 62.5
216 01/02/2023 62.5
217 01/02/2023 62.5
218 01/02/2023 62.5
219 01/02/2023 62.5
220 01/02/2023 62.5
221 01/02/2023 62.5
222 01/02/2023 62.5
223 01/02/2023 62.5
224 01/02/2023 62.5
225 01/02/2023 62.5
226 01/02/2023 62.5
227 01/02/2023 62.5
228 01/02/2023 62.5
229 01/02/2023 62.5
230 01/02/2023 62.5
231 01/02/2023 62.5
232 01/02/2023 62.5
233 01/02/2023 62.5
234 01/02/2023 62.5
235 01/02/2023 62.5
236 01/02/2023 62.5
237 01/02/2023 62.5
238 01/02/2023 62.5
239 01/02/2023 62.5
240 01/02/2023 62.5
241 01/02/2023 62.5
242 01/02/2023 62.5
243 01/02/2023 62.5
244 01/02/2023 62.5
245 01/02/2023 62.5
246 01/02/2023 62.5
247 01/02/2023 62.5
248 01/02/2023 62.5
249 01/02/2023 62.5
250 01/02/2023 62.5
251 01/02/2023 62.5
252 01/02/2023 62.5
253 01/02/2023 62.5
254 01/02/2023 62.5
255 01/02/2023 62.5
256 01/02/2023 62.5
257 01/02/2023 62.5
258 01/02/2023 62.1956169
259 01/02/2023 61.0242031
260 01/02/2023 60.1229128
261 01/02/2023 59.7943723
262 01/02/2023 59.7943723
263 01/02/2023 57.9849838
264 01/02/2023 57.8617811
265 01/02/2023 56.25
266 01/02/2023 56.25
267 01/02/2023 54.3831169
268 01/02/2023 54.3831169
269 01/02/2023 54.3831169
270 01/02/2023 50
271 01/02/2023 50
272 01/02/2023 50
273 01/02/2023 50
274 01/02/2023 48.4375
275 01/02/2023 46.2662338
276 01/02/2023 46.2662338
277 01/02/2023 46.2662338
278 01/02/2023 44.53125
279 01/02/2023 42.7934991
280 01/02/2023 38.8872491
281 01/02/2023 36.1201299
282 01/02/2023 34.4155844
283 01/02/2023 30.0324675
284 01/02/2023 30.0324675
285 01/02/2023 30
286 01/02/2023 30
287 01/02/2023 28.90625
288 01/02/2023 25
289 01/02/2023 25
290 01/02/2023 25
291 01/02/2023 25
292 01/02/2023 25
293 01/02/2023 25
Lists
Cell Formulas
Range Formula
D2:F2 D2 =LET(m,$A2:$A500,s,$B2:$B500,f,FILTER(s,(m>=D$1)*(m<=EOMONTH(D$1,0))),AVERAGE(DROP(f,MATCH(QUARTILE.INC(f,1),f,0))))

Thanks Fluff. I amended the formula as I was getting #N/A error for few months. With the below formula, I'm getting correct results from Sep-20 onwards but for the month of Feb-23 the result isn't matching. Couldn't figure out the reason for the same. Please support.

Book9

A

B

C

D

E

F

G

1

Month

Overall Score

Feb-23

2

Feb-23

93.75

41.70

** Score should be 42.24

3

Feb-23

93.75

4

Feb-23

93.75

Quartile-1 resut:

59.79437

5

Feb-23

93.75

Starting Cell Reference

B95

6

Feb-23

93.75

Avg. Req. for 33 rows

42.24

7

Feb-23

93.75

Avg. Req. for 32 rows

41.70

8

Feb-23

93.75

9

Feb-23

93.75

10

Feb-23

93.75

11

Feb-23

93.75

12

Feb-23

93.75

13

Feb-23

91.04437229

14

Feb-23

89.84375

15

Feb-23

89.84375

16

Feb-23

89.84375

17

Feb-23

89.11178108

18

Feb-23

85.9375

19

Feb-23

85.9375

20

Feb-23

85.9375

21

Feb-23

82.03125

22

Feb-23

82.03125

23

Feb-23

82.03125

24

Feb-23

80.35714286

25

Feb-23

78.125

26

Feb-23

78.125

27

Feb-23

78.125

28

Feb-23

77.51623377

29

Feb-23

77.51623377

30

Feb-23

77.51623377

31

Feb-23

75.94358766

32

Feb-23

74.21875

33

Feb-23

74.21875

34

Feb-23

74.21875

35

Feb-23

71.875

36

Feb-23

71.42857143

37

Feb-23

70.3125

38

Feb-23

70.3125

39

Feb-23

66.40625

40

Feb-23

66.40625

41

Feb-23

66.40625

42

Feb-23

66.40625

43

Feb-23

65.79748377

44

Feb-23

62.5

45

Feb-23

62.5

46

Feb-23

62.5

47

Feb-23

62.5

48

Feb-23

62.5

49

Feb-23

62.5

50

Feb-23

62.5

51

Feb-23

62.5

52

Feb-23

62.5

53

Feb-23

62.5

54

Feb-23

62.5

55

Feb-23

62.5

56

Feb-23

62.5

57

Feb-23

62.5

58

Feb-23

62.5

59

Feb-23

62.5

60

Feb-23

62.5

61

Feb-23

62.5

62

Feb-23

62.5

63

Feb-23

62.5

64

Feb-23

62.5

65

Feb-23

62.5

66

Feb-23

62.5

67

Feb-23

62.5

68

Feb-23

62.5

69

Feb-23

62.5

70

Feb-23

62.5

71

Feb-23

62.5

72

Feb-23

62.5

73

Feb-23

62.5

74

Feb-23

62.5

75

Feb-23

62.5

76

Feb-23

62.5

77

Feb-23

62.5

78

Feb-23

62.5

79

Feb-23

62.5

80

Feb-23

62.5

81

Feb-23

62.5

82

Feb-23

62.5

83

Feb-23

62.5

84

Feb-23

62.5

85

Feb-23

62.5

86

Feb-23

62.5

87

Feb-23

62.5

88

Feb-23

62.5

89

Feb-23

62.5

90

Feb-23

62.5

91

Feb-23

62.5

92

Feb-23

62.19561688

93

Feb-23

61.02420307

94

Feb-23

60.1229128

95

Feb-23

59.79437229

96

Feb-23

59.79437229

97

Feb-23

57.98498377

98

Feb-23

57.86178108

99

Feb-23

56.25

100

Feb-23

56.25

101

Feb-23

54.38311688

102

Feb-23

54.38311688

103

Feb-23

54.38311688

104

Feb-23

50

105

Feb-23

50

106

Feb-23

50

107

Feb-23

50

108

Feb-23

48.4375

109

Feb-23

46.26623377

110

Feb-23

46.26623377

111

Feb-23

46.26623377

112

Feb-23

44.53125

113

Feb-23

42.79349911

114

Feb-23

38.88724911

115

Feb-23

36.12012987

116

Feb-23

34.41558442

117

Feb-23

30.03246753

118

Feb-23

30.03246753

119

Feb-23

30

120

Feb-23

30

121

Feb-23

28.90625

122

Feb-23

25

123

Feb-23

25

124

Feb-23

25

125

Feb-23

25

126

Feb-23

25

127

Feb-23

25

Sheet1

Cell Formulas
Range	Formula
D2	D2	=LET(m,A:A,s,B:B,f,FILTER(s,(m>=D1)*(m<=EOMONTH(D1,0))),IFNA(AVERAGE(DROP(f,MATCH(QUARTILE.INC(f,1),f,0)-1)),AVERAGE(DROP(f,MATCH(QUARTILE.INC(f,1),f,-1)))))
E4	E4	=QUARTILE.INC(B2:B127,1)
E6:E7	E6	=AVERAGE(B95:B127)

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$1:$B$127	D2

Firasath · Apr 5, 2023

Firasath said:
Thanks Fluff. I amended the formula as I was getting #N/A error for few months. With the below formula, I'm getting correct results from Sep-20 onwards but for the month of Feb-23 the result isn't matching. Couldn't figure out the reason for the same. Please support.

Book9
A B C D E F G
1 Month Overall Score Feb-23
2 Feb-23 93.75 41.70 ** Score should be 42.24
3 Feb-23 93.75
4 Feb-23 93.75 Quartile-1 resut: 59.79437
5 Feb-23 93.75 Starting Cell Reference B95
6 Feb-23 93.75 Avg. Req. for 33 rows 42.24
7 Feb-23 93.75 Avg. Req. for 32 rows 41.70
8 Feb-23 93.75
9 Feb-23 93.75
10 Feb-23 93.75
11 Feb-23 93.75
12 Feb-23 93.75
13 Feb-23 91.04437229
14 Feb-23 89.84375
15 Feb-23 89.84375
16 Feb-23 89.84375
17 Feb-23 89.11178108
18 Feb-23 85.9375
19 Feb-23 85.9375
20 Feb-23 85.9375
21 Feb-23 82.03125
22 Feb-23 82.03125
23 Feb-23 82.03125
24 Feb-23 80.35714286
25 Feb-23 78.125
26 Feb-23 78.125
27 Feb-23 78.125
28 Feb-23 77.51623377
29 Feb-23 77.51623377
30 Feb-23 77.51623377
31 Feb-23 75.94358766
32 Feb-23 74.21875
33 Feb-23 74.21875
34 Feb-23 74.21875
35 Feb-23 71.875
36 Feb-23 71.42857143
37 Feb-23 70.3125
38 Feb-23 70.3125
39 Feb-23 66.40625
40 Feb-23 66.40625
41 Feb-23 66.40625
42 Feb-23 66.40625
43 Feb-23 65.79748377
44 Feb-23 62.5
45 Feb-23 62.5
46 Feb-23 62.5
47 Feb-23 62.5
48 Feb-23 62.5
49 Feb-23 62.5
50 Feb-23 62.5
51 Feb-23 62.5
52 Feb-23 62.5
53 Feb-23 62.5
54 Feb-23 62.5
55 Feb-23 62.5
56 Feb-23 62.5
57 Feb-23 62.5
58 Feb-23 62.5
59 Feb-23 62.5
60 Feb-23 62.5
61 Feb-23 62.5
62 Feb-23 62.5
63 Feb-23 62.5
64 Feb-23 62.5
65 Feb-23 62.5
66 Feb-23 62.5
67 Feb-23 62.5
68 Feb-23 62.5
69 Feb-23 62.5
70 Feb-23 62.5
71 Feb-23 62.5
72 Feb-23 62.5
73 Feb-23 62.5
74 Feb-23 62.5
75 Feb-23 62.5
76 Feb-23 62.5
77 Feb-23 62.5
78 Feb-23 62.5
79 Feb-23 62.5
80 Feb-23 62.5
81 Feb-23 62.5
82 Feb-23 62.5
83 Feb-23 62.5
84 Feb-23 62.5
85 Feb-23 62.5
86 Feb-23 62.5
87 Feb-23 62.5
88 Feb-23 62.5
89 Feb-23 62.5
90 Feb-23 62.5
91 Feb-23 62.5
92 Feb-23 62.19561688
93 Feb-23 61.02420307
94 Feb-23 60.1229128
95 Feb-23 59.79437229
96 Feb-23 59.79437229
97 Feb-23 57.98498377
98 Feb-23 57.86178108
99 Feb-23 56.25
100 Feb-23 56.25
101 Feb-23 54.38311688
102 Feb-23 54.38311688
103 Feb-23 54.38311688
104 Feb-23 50
105 Feb-23 50
106 Feb-23 50
107 Feb-23 50
108 Feb-23 48.4375
109 Feb-23 46.26623377
110 Feb-23 46.26623377
111 Feb-23 46.26623377
112 Feb-23 44.53125
113 Feb-23 42.79349911
114 Feb-23 38.88724911
115 Feb-23 36.12012987
116 Feb-23 34.41558442
117 Feb-23 30.03246753
118 Feb-23 30.03246753
119 Feb-23 30
120 Feb-23 30
121 Feb-23 28.90625
122 Feb-23 25
123 Feb-23 25
124 Feb-23 25
125 Feb-23 25
126 Feb-23 25
127 Feb-23 25
Sheet1
Cell Formulas
Range Formula
D2 D2 =LET(m,A:A,s,B:B,f,FILTER(s,(m>=D1)*(m<=EOMONTH(D1,0))),IFNA(AVERAGE(DROP(f,MATCH(QUARTILE.INC(f,1),f,0)-1)),AVERAGE(DROP(f,MATCH(QUARTILE.INC(f,1),f,-1)))))
E4 E4 =QUARTILE.INC(B2:B127,1)
E6:E7 E6 =AVERAGE(B95:B127)
Named Ranges
Name Refers To Cells
_FilterDatabase =Sheet1!$A$1:$B$127 D2

I used the below formula in my database, in which I got the incorret result for Feb-23 but for all other months got right results.

=LET(m,NV[Month],s,NV[Overall Score],f,FILTER(s,(m>=R30)*(m<=EOMONTH(R30,0))),IFNA(AVERAGE(DROP(f,MATCH(QUARTILE.INC(f,1),f,0)-1)),AVERAGE(DROP(f,MATCH(QUARTILE.INC(f,1),f,-1)))))

Fluff · Apr 5, 2023

When I copy the data you posted I get 42.24. Check that you don't have any further rows for Feb 23

Firasath · Apr 5, 2023

Fluff said:
When I copy the data you posted I get 42.24. Check that you don't have any further rows for Feb 23

No, I don't and I am getting 41.7 in both in my database and in fili I shared with you.

Get bottom average score based on month

Firasath

Board Regular

Excel Facts

frabulator

Active Member

Fluff

MrExcel MVP, Moderator

Firasath

Board Regular

Firasath

Board Regular

Fluff

MrExcel MVP, Moderator

Firasath

Board Regular

Firasath

Board Regular

Fluff

MrExcel MVP, Moderator

Firasath

Board Regular

Similar threads

Forum statistics

Share this page

Get bottom average score based on month

Board Regular

Excel Facts

Active Member

MrExcel MVP, Moderator

Board Regular

Board Regular

MrExcel MVP, Moderator

Board Regular

Board Regular

MrExcel MVP, Moderator

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock