How to average a row of cells only for months that have passed?

Hi Tech Nut,

You could do this.

Add another row to Table 1 to calculate the month number.

Formula in B2 (and fill to M2) =MONTH(B1)

Formula in O3 =AVERAGEIF(B2:M2,"<"&Today::$B$2,B3:M3)

Formula in the Today table B1 =TODAY

Formula in the Today table B2 =MONTH(B1)

Regards,

Ian.

Update: Neglected to Preserve Row for the range reference to row 1 (dates).

=AVERAGEIF(C$1:N$1,"<="&TODAY()−DAY(TODAY()),C2:N2)

Regards,

Barry

AVERAGE IF can be your friend here.

The month names in your header row must be Date and Time values for the first day of that month, but can be formatted to display only the month name.

If your table includes values for only one year, and starts on the first of the first month, you can use AVERAGEIF

=AVERAGEIF(C1:N1,"<="&TODAY()−DAY(TODAY()),C2:N2)

TODAY()-DAY(TODAY() returns the date for the last day of the previous month.

Regards,

Barry