ok.. basically i have this spreadsheet for calculating outage statistics for different areas of trouble call escalation.

what i want this particular cell to do is lookup the total outage time per issue per escalation level and come up with an average outage time per escalation level.

any ideas?

 

what kind of stats do you have in there? how's it set up?

 

pretty much it's just trouble ticket open, resolution and total down time with a pulldown for each section in the escalation chain.

rows

 

i can do it with 3 cells for each level. i'm sure there's a better way to do it though.

i used sumif and countif. use sumif to add up the total time depending on the level, and then countif to figure out how many of each level there is. then divide the sumif cell by the countif cell to get the average.

 

ok, did it all in one cell. this is basically the formula:

=(SUMIF(A2:A9,1,B2:B9))/(COUNTIF(A2:A9,1))
=(SUMIF(range_to_test, what_to_look_for, cell_to_look_in, range_to_add))/(COUNTIF(range_to_count, what_to_look_for))

the ones are levels (i had different numbers for the different levels, you put what you need). let me know if you need more info than that.


*edit to explain it a little*

 

that'll do donkey.. that'll do

thanks meng

 

got it in one cell.....

=SUMIF($D$4:$D$84,"value",$K$4:$K$84)/COUNTIF($D$4:$D$84,"value")

again thanks

 

that's what i said.



you're welcome.