Excel 2013 AVERAGEIFS using COLUMN() reference

Question

I am trying to use in a worksheet the AVERAGEIF formula to calculate a moving average based on the current month. I have this data:

         A            B      C      D      E      F         G      H       I      J      K      L      M     N
      1 2017         Jan    Feb    Mar    Apr    May       Jun    Jul     Aug    Sep    Oct    Nov    Dec   Ave
      2 Cost/Req.    $430  $432     $365   $391  $1250        $-     $-      $-      $-      $-      $-      $-    ??

and I'd like to put in N2 the average of Jan-Apr today (May) and in June for Jan-May and so forth. I tried using the COLUMN() function, but it is not working.

AVERAGEIF(B2:M2,"COLUMN()<MONTH(NOW()")

Any idea?

thanks!

Answer 1

Let's address the several angles of this question separately:

tl;dr

=AVERAGE(INDIRECT("A2:"&ADDRESS(2,MONTH(NOW())-1)))

Why does your formula not work?

The AVERAGEIF function takes criteria arguments, but they don't seem to be able to do anything very complex. I haven't been able to find a true explanation for their syntax, but examples in the online documentation don't do anything more profound than ">32", and you're trying to use entire formulas. I haven't seen any evidence that approach will work even in theory. (I'd love to be shown wrong on this!) AVERAGEIF (or AVERAGEIFS) is not the function you want.

Averaging a range

Averaging a range of values is easy. You clearly already know how to do it to even be asking the question you are! Assuming values are in A2:L2 (month headers at the top):

=AVERAGE(A2:L2)

But wait, you only wanted to average up to and not including the current month. So what we need is a way to calculate the range that goes into the basic average formula. In May, that's A2:D2. How can we calculate that?

Formula for calculating a range

OK, so the new problem is how to get a range (a reference for the AVERAGE function) from the current month. The Lookup & Reference functions will help you here. There might be a simpler way to do this, but the best I could come up with was to use INDIRECT to return a reference/range if we give it appropriate text, which is an easier problem. Now all we need is to come up with the text "A2:D2" (for May). A2 is easy enough: should be a constant. There might be a way to convert 4 (for April, the last month you want) to D (the column you want), but you could also use the ADDRESS function to calculate D2 off the row/column numbers. Note that it thinks "backwards" from normal. So A2 would be entered as ADDRESS(2,1). And in your case, you know the row, 2, and can calculate the column you want using the MONTH function you wanted, and then subtracting 1 to go back to last month. So we can get 'D2' with ADDRESS(2,MONTH(NOW())-1). Then it's just a matter of putting it together. Combining the A2 and the D2 is done with the an ampersand.

="A2:"&(ADDRESS(2,MONTH(NOW())-1))

Putting it all together

Now we have the text representation of the area we want to average. The INDIRECT function will convert it to a real range that the AVERAGE function can use. The final function then averages it all together:

=AVERAGE(INDIRECT("A2:"&ADDRESS(2,MONTH(NOW())-1)))

Some notes

If your correct range is really U192:AF192, replace the 2s in the formula with 192, and add 20 to the row parameter (since U is 20 rows to the right of A).

I'm also attaching a screenshot of how the various intermediate steps calculate. The final formula is the one on row 8. I also took it the next step of only averaging the last few months, not all year, using the same idea on row 12.

Note that this whole approach will make your average depend on when the spreadsheet is opened. If you open it up next February, the average will only be for two months, not the entire 2017 year. January will choke entirely. You might want to come up with a smarter formula to handle the month parameter.

Answer 2

The easiest way to do this would be to create an AVERAGE formula within an IF statement that checks if the month has a value input. Place this formula in U3, then drag it across the row to AF3.

=IF(ISBLANK(U2), "", AVERAGE($U$2:U2))

This formula will check if the cell is populated. If TRUE, it will run the function. The average function has an absolute reference which anchors the first column.

As you add amounts to the months in row 2, the formula will update the running average to include the new data. The results will be the average of Jan thru that month.

Answer 3

The question is hard to understand.  What I understand is

Today (in May) I’d like to know the average of Jan-Apr.  In June I’d like to know the average for Jan-May, and so forth.

This is easily handled as the average of a range with one fixed (anchored) end and one floating end.  In Cell V193 (i.e., in the Feb column, under the Cost/Req. value ($432)), enter

=AVERAGE($U192:U192)

When evaluated in V193, this is equivalent to AVERAGE(U192:U192), which is equivalent to AVERAGE(U192), which is equivalent to U192, which is the January value, $430.  But now I want you to drag/fill this to the right, to Column AF.  In W193, this will be changed to =AVERAGE($U192:V192), because the $U192 (the first argument to AVERAGE) is anchored to Column U, but the U192 (the second argument to AVERAGE) floats to be one column to the left of the current column.  And so on, up to AF193, which contains =AVERAGE($U192:AE192).