Finding average of data for certain criteria

[Zyrusi]

Hello

I hope someone can help me out. I'm trying to make a report that finds out the average meantimetofix (on data sheet) depending on the month/year and priority.
I would also like to make the formulas dynamic since the user should be able to add data into the data sheet and the excel to workout the average.

So the data should only be used if it has a closed date too, if the data doesn't this should not be used to work out the average.

I have attached a spreadsheet that will make these clear.

[JohnTopley]

Which Month/Year date: opening or closing?

[Glenn Kennedy]

Assuming it's opening.. change the headers to Sev - 1 etc and use this, copied across and down:

=IFERROR(AVERAGEIFS(DATA!$E:$E,DATA!$C:$C,Report!B$2,DATA!$F:$F,"<>"&"",DATA!$B:$B,">="&EOMONTH($A3,-1)+1,DATA!$B:$B,"<="&EOMONTH($A3,0)),"")

[Zyrusi]

Assuming it's opening.. change the headers to Sev - 1 etc and use this, copied across and down:

=IFERROR(AVERAGEIFS(DATA!$E:$E,DATA!$C:$C,Report!B$2,DATA!$F:$F,"<>"&"",DATA!$B:$B,">="&EOMONTH($A3,-1)+1,DATA!$B:$B,"<="&EOMONTH($A3,0)),"")

Hi Glenn

Thank you so much... Could you explain how the formula works please? Also does this ignore if the data doesn't have a closed date?

Many thanks

[Glenn Kennedy]

It could be made a fair bit simpler if you used a more rational way of collecting the data for column A. The first date is 22/12/2015 (not very convenient as it isn't the 1st of the month) and all the rest are 1 month later.

However....

=IFERROR(AVERAGEIFS(DATA!$E:$E,DATA!$C:$C,Report!B$2,DATA!$F:$F,"<>"&"",DATA!$B:$B,">="&EOMONTH($A3,-1)+1,DATA!$B:$B,"<="&EOMONTH($A3,0)),"")

Red: return the average of these data
Light Green: when the severity matches B2, AND
Dark blue: when the closing date is anything other than a blank, AND
Orange: when the start date is greater than 1st of month of column A (see note below), AND
Cyan: when the start date is LESS than the end of the month of column A.
Balck: if you get an error as a result, return a blank.

Note:

EOMONTH($A3,-1)+1

so for 22/11/2015, your first date (formatted to look like "December 2015"...

return the end of the PREVIOUS month (red) plus 1 day (blue), which will (of course) be 1st November 2015.



You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.