Adding a date stipulation to an average if

[zippersweater]

Hi all,

I have an average if formula that is working well, but I'd like to add the stipulation that the year of the even must be 2012. I searched online and say a similar request answered with a suggestion of using an array.

This is what I had that worked:
=AVERAGEIF(Data!AE:AE,A23,Data!BW:BW)

Where BW is the column that has the numbers to be averaged and AE is the column that has one stipulation (must be = to A23)

The array I tried is this:

=AVERAGE(IF((YEAR(Data!BR3:BR92)=2012)*(Data!AE3:AE92=A23),Data!BW3:BW92))

The year is in BR.

Can anyone tell why this isn't working?

Thanks.

[SteveG]

Hi,

Try this,

=AVERAGE(IF(YEAR(AE2:AE9)=2012,IF(BR2:BR9=A23,$BW$2:$BW$9)))

This is an array formula and should be commited with Ctr+Shift+Enter

HTH
Steve

[dilipandey]

Hi Zippersweater,

A sample workbook would have been helpful but try below :-

{=AVERAGE(IF((Data!BR3:BR92>=DATE(2012,1,1))*(Data!BR3:BR92<=DATE(2012,12,31))*(Data!AE3:AE92=A23),(Data!BW3:BW92),""))}

Regards,
DILIPandey

<click on below 'star' if this helps>

[zippersweater]

Hi,

Try this,

=AVERAGE(IF(YEAR(AE2:AE9)=2012,IF(BR2:BR9=A23,$BW$2:$BW$9)))

This is an array formula and should be commited with Ctr+Shift+Enter

HTH
Steve

This is the same array but the columns are switched. BR is the one with the dates

[zippersweater]

Hi Zippersweater,

A sample workbook would have been helpful but try below :-

{=AVERAGE(IF((Data!BR3:BR92>=DATE(2012,1,1))*(Data!BR3:BR92<=DATE(2012,12,31))*(Data!AE3:AE92=A23),(Data!BW3:BW92),""))}

Regards,
DILIPandey

<click on below 'star' if this helps>

This gave me a divide by 0 error.

[SteveG]

Hi,

The formula I posted worked on the data I tested it on. I didn't know which column to use based on what you wrote "=AVERAGEIF(Data!AE:AE,A23,Data!BW:BW)" didn't refer to BR at all but the other did. Did you try it? Like I said, it worked for me just change to the columns you're using as needed.

Steve

[zippersweater]

Yes I tried it and I get a #value error.

[SteveG]

Did you use Ctrl+Shift+Enter? If not that would return a value error. Can you post a sample workbook?

[zippersweater]

I did use Ctrl+Shift+Enter. I can't/dont know how to post a sample, unfortunately.

[dilipandey]

Hi Zipperweater, while replying to this post, click on "Go Advanced" and look for paper clip icon to upload.. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[SteveG]

Ok. Here is an example.

AverageIF.xlsx

[zippersweater]

Thanks all, I got it working thanks to you. this worked:
=AVERAGEIFS(Data!BW:BW,Data!AE:AE,Installations!A24,Data!BR:BR,">="&DATE(2012,1,1))