# Excel 2013 AVERAGEIFS using COLUMN() reference

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!

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.

• ojchase, lots of kudos for this elegant solution! I edited my question even though you got the column number right in your comments. it works perfectly and I learned something new. thanks! Commented May 15, 2017 at 13:41

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.

• I think question asks for formula to be placed in one cell, changing according to current date, and not separate formula for each month. Commented May 11, 2017 at 13:07
• I thought about that, but the OP doesn't make it clear. So, let's allow the OP to clarify if that is the case. Commented May 11, 2017 at 13:10
• I wasn't clear, clearly :-). I was looking for a one-cell average at the end of the row. I'm pretty aware of the column average and dynamic ranges, etc. My difficulties was about having a 'moving' range based on month of NOW() in a cell. I'll look at the solution below and report back. Thanks for the moment. Commented May 15, 2017 at 13:17

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)`.

• Thanks, but I would need a one-cell moving average. This approach is clear to me already, just not what is was looking for. Sorry if it wasn't clear upfront. Commented May 15, 2017 at 13:18