Average a range of cells in VBA

[jojasary]

OK this might sound simple, but how can I make an average, in VBA of a range of cells?

I want to average cells b2 to b24

thanks

 

[footoo]

The following code finds today's date in row 7, and calculates the average in row 11 from column D to the column containing today's date.
It is assumed that cells containing 0 should be included in the average.

Sub Avg()
Dim col As Variant, myresult!
col = Application.Match(CLng(Date), Rows(7), 0)
If Not IsError(col) Then
    myresult = Application.Average(Range([D11], Cells(11, col)))
Else
    MsgBox "Today's date not found in row 11"
    Exit Sub
End If
End Sub

 

[Dj Jack]

The following code finds today's date in row 7, and calculates the average in row 11 from column D to the column containing today's date.
It is assumed that cells containing 0 should be included in the average.

Sub Avg()
Dim col As Variant, myresult!
col = Application.Match(CLng(Date), Rows(7), 0)
If Not IsError(col) Then
    myresult = Application.Average(Range([D11], Cells(11, col)))
Else
    MsgBox "Today's date not found in row 11"
    Exit Sub
End If
End Sub

That was great!! Thanks

But let me explain better....

I have this Table in a worksheet and i need to make an average in "b3" and "b5" the range change daily. the info located after today in those rows need to be diferent like this exapmle

	A	B	C	D	E	F	G	H	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
1				27/08/2018	28/06/2018	29/06/2018	30/06/2018	01/07/2018	02/07/2018	03/07/2018	04/07/2018	05/07/2018	06/07/2018	07/07/2018	08/07/2018	09/07/2018	10/07/2018	11/07/2018	12/07/2018	13/07/2018	14/07/2018	15/07/2018	16/07/2018	17/07/2018	18/07/2018	19/07/2018	20/07/2018	21/07/2018	22/07/2018	23/07/2018	24/07/2018	25/07/2018	26/07/2018	27/07/2018	28/07/2018	29/07/2018	30/07/2018
2	Total Pool	577		564	570	575	560	554	580	596	604	597	615	620	645	610	580	567	577
3	Estimated Daily input	=dynamic range average from D3 to "today"		=D4*D5	=E4*E5	156	172	180	180	164	180	156	180	168	164	180	180	180	180	=b3	=b3	=b3	=b3	=b3	=b3	=b3	=b3	=b3	=b3	=b3	=b3	=b3	=b3	=b3	=b3	=b3	=b3
4	Productivity Quote x Tech	4		4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4
5	Number of Techs	=dynamic range average from D3 to "today"		45	44	39	43	45	45	41	40	39	45	42	41	45	45	45	45	=d5	=d5	=d5	=d5	=d5	=d5	=d5	=d5	=d5	=d5	=d5	=d5	=d5	=d5	=d5	=d5	=d5	=d5

<tbody>
</tbody>

Thanks for help...

 

[footoo]

I thought you wanted a VBA solution.

In B3 :

=AVERAGE(D3:OFFSET(D3,0,MATCH(TODAY(),1:1,0)-3)) In B5 : =AVERAGE(D5:OFFSET(D5,0,MATCH(TODAY(),1:1,0)-3)) <tbody> </tbody>

<tbody>
</tbody>

 

[Dj Jack]

footoo, thanks it was work !! only i replace the part of my code qith this formula and the macro wroks great! thanks again