Thread: Problem with averageifs()

Hello Excel experts,

I would like to ask you for some help. I have an Excel 2007 table with two columns: month and amount. I want to get the average of the amount excluding the current month. I am using =AVERAGEIFS(MyTable[Amount], MyTable[Month], "<>"&B1), where B1 = "April 2010". However, the calculation seems to get the average wrong, as it counts the amount corresponding to April 2010. Would someone please help? I am attaching an example sheet.

As a quick workaround, I used this:

=SUMPRODUCT(MyTable[Amount],--(MyTable[Month]<>B1))/SUMPRODUCT(--(MyTable[Month]<>B1))

But I would still love to use averageifs(), as it is much more readable.

First, I have no explanation why the current formula does not work. Comparing

=B1<>A21

in your attached file delivers FALSE, by logic, this row should be excluded from the AVERAGEIFS.

But then, I never really trust dates in text format.

If you change your texts to real dates, starting with 1-Nov-2008 and incrementing the month every row, you can then use this formula in B1

=DATE(YEAR(NOW()),MONTH(NOW()),1)

and your ARVERAGEIFS will compute fine.

See attached.