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.