Need formulas to automatically figure out last row of data to include in calculation

[mnyankee]

I am needing my formulas to figure out what the last row of data is to include in the calculation. The data will always start in row 2 but the end row can change every time the source data is refreshed. I found the following on this site: =IFERROR(SUM(INDIRECT("F2:F" & MAX((F:F<>"")*(ROW((F:F))))),0) but am not sure how to incorporate it into my formulas: =AVERAGE(IF((WEEKDAY($A$2:$A$35,2)>5)*($A$2:$A$35<>""),F2:F35)) or if it is the simplest approach to take. Any help from you gurus is much appreciated!

 

[arthurbr]

Why not make your range an Excel Table and use it as source for your formula ?

 

[James006]

Hi,

If you need a formula to locate the Last Used Non-Empty Row :

=SUMPRODUCT(MAX((ROW(A1:A20))*(A1:A20<>"")))

 

[kevin9999]

Maybe this?

test.xls
	A	B	C	D	E	F
1	Day					Value
2	31-Mar		38.66667			48
3	1-Apr					50
4	2-Apr					37
5	3-Apr					40
6	4-Apr					43
7	5-Apr					50
8	6-Apr					42
9	7-Apr					36
10	8-Apr					11
11	9-Apr					48
12	10-Apr					32
13	11-Apr					21
14	12-Apr					45
15	13-Apr					42
16	14-Apr					24
17	15-Apr					38
18	16-Apr					48
19	17-Apr					25
20	18-Apr					10
21	19-Apr					22
22	20-Apr					16
Sheet3

Cell Formulas
Range	Formula
C2	C2	=AVERAGE(IF((WEEKDAY(INDIRECT("A2:A"&MAX((A:A<>"")*(ROW(A:A)))),2)>5*(INDIRECT("A2:A"&MAX((A:A<>"")*(ROW(A:A))))<>"")),INDIRECT("F2:F"&MAX((F:F<>"")*(ROW(F:F))))))

 

[fjns]

Hi, see the linked file for a possible solution...

The formula used in the table:
=AVERAGE(IF(IFERROR((WEEKDAY(A:A,2)>5)*(A:A<>""),0),F:F))

Average.xlsx

 

[Fluff]

How about

=AVERAGE(FILTER(F2:F10000,(A2:A10000<>"")*(WEEKDAY(A2:A10000,2)>5)))

 

[fjns]

You can try also this formula:
=AVERAGE(FILTER(F:F,IFERROR((WEEKDAY(A:A,2)>5)*(A:A<>""),0)))

 

[Fluff]

It's always best to avoid using whole column references, especially with array formulae.