Excel AverageIf function Criteria error

Question

I have a data source listing months from January-11 to December-14. I'm trying to find the Average numeric data input for each of those months. I'm using the AverageIf function with criteria of "January-11" and "February-11" and "March-11" up to "December-14".

Problem: the resulting data is showing me averages for the month of "February-11", but there is no corresponding data for "February-11" in my source data. Why and how could the AverageIf function be showing me data for 'February-11' when there is not source data for 'February-11'. Any ideas why?

Example: Excel Formula bar =AVERAGEIF($B$13:$B$14562,"February-11",$G$13:$G$14562) for example reasons, Column B is Month Column G is GreenInv

I've tried changing the formulas to use wild cards...

=AVERAGEIF($C$24:$C$14562,"February???",$D$24:$D$14562)

still no good!

Stumped! Appreciate help!

Workbook:

Month RedInv BlueInv GreenInv OrangeInv BlackInv

01/28/11 0 15 4 1062 0

01/28/11 0 8 2 1051 0

11/01/11 0 -699 6 50 56

11/20/11 0 -712 2 21 26

11/16/12 0 0 5 439 439

11/16/12 0 0 5 439 439

11/16/12 0 0 1 435 438

At least seeing some sample data (that has the same problem) would help. (In trying to post the data, you may even find your own mistake!) — Xynariz
– Xynariz, Commented May 6, 2014 at 16:08
can you upload your workbook using any fileuploader (e.g. dropbox.com)? — Dmitry Pavliv
– Dmitry Pavliv, Commented May 6, 2014 at 16:38

Dmitry Pavliv · Accepted Answer · 2014-05-06 19:44:03Z

0

As I mentioned in comments, you're searching string "February-11" in range $C$24:$C$14562, however your range contains dates with number format mmmm-yy. You should use something like this instead:

=AVERAGEIFS($D$24:$D$14562,$C$24:$C$14562,"<"&DATE(2011,3,1),
                           $C$24:$C$14562,">"‌&DATE(2011,1,31))

answered May 6, 2014 at 19:44

Dmitry Pavliv

35.9k13 gold badges85 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel AverageIf function Criteria error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related