Help with Average/if function

[jedibrown]

Good afternoon,

I am trying to see if it's possible to create an average/if type formula.

Basically I have 2 columns of reference, E and J. E is the date closed and J is the date logged. I want to look and see the average amount of days open per month.

For instance, If the date in column E is within January, I want it to then look in column J and calculate how many days it's been open for, then work out an average from the other rows that have also got dates in column E within January.

I hope this makes sense

Thanks!!

 

[Andrew Poulsom]

You can use a formula like:

=AVERAGE(IF(MONTH(E1:E8)=1,E1:E8-J1:J8))

confirmed with Ctrl+Shift+Enter.

 

[barry houdini]

This "array formula" should work for you for January dates in 2012

=AVERAGE(IF(TEXT(E2:E100,"mmm-yyyy")="jan-2012",E2:E100-J2:J100))

confirmed with CTRL+SHIFT+ENTER

Assumes up to 100 rows of data, extend if required

[edit, essentially the same as Andrew's suggestion except it will distinguish between January dates in different years if required]

 

[jedibrown]

Thanks for this - but it only appears to work when it's entered in the same row as that month. for instance, january (month 1) only works when put on the end of the row with january in column E.

I was hoping to report this on a different sheet if I can. Do you know if that's possible?

Thanks

 

[barry houdini]

Sounds like your are not confirming with CTRL+SHIFT+ENTER

To do that put the formula in a cell, press F2 key to select formula then hold down CTRL and SHIFT keys and press ENTER. If done correctly then { and } will appear around the formula in the formula bar

To use on another sheet, assuming you have the data in a sheet called Data then just add Data! to each reference like this

=AVERAGE(IF(TEXT(Data!E2:E100,"mmm-yyyy")="jan-2012",Data!E2:E100-Data!J2:J100))

 

[jedibrown]

Brilliants - works perfectly thank you.

One last query - could I add another variable to this? I only want it to count the ones with "complete" in column G?

could I add this in?

Thanks again

 

[jedibrown]

Brilliants - works perfectly thank you.

One last query - could I add another variable to this? I only want it to count the ones with "complete" in column G?

could I add this in?

Thanks again

 

[Andrew Poulsom]

Sure:

=AVERAGE(IF(TEXT(Data!E2:E100,"mmm-yyyy")="jan-2012",IF(Data!G2:G100="complete",Data!E2:E100-Data!J2:J100)))

 

[jedibrown]

Perfect - thank you very much!