Solved: Problem with averaging netdays with criteria

[gemsera]

Hi All,

Pls see attached workbook example.
I have a database with quite alot of data, and I would like to find out the Average amount of Workdays taken to close specific types of faults.

For instance.
How many days on average did it take to clear Security faults? (Security is under Flags) So that would be the average of 9 and 76.

Or another example.
How many days on average did it take to clear ASX faults? (ASX is a product), so this would be average between 10 and 76.

Pls note, the days to resolve already uses the networkdays to calculate time.

I really do hope someone can help, its something very difficult to search for perhaps I was terming it wrong but I couldnt find anything on google or on here.

Thank you very much.

[Bob Phillips]

The workbook would help.

[gemsera]

wow you are too quick, I forgot it, and edited immediately :P

[Bob Phillips]

I don't understand where the numbers in Q come from, but do these array formulae do what you want

=AVERAGE(IF(ISNUMBER(SEARCH("security",O2:O200)),Q2:Q200))

=AVERAGE(IF(J2:AJ200="ASX",Q2:Q200))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.When editing the formula, it must again be array-entered.

[gemsera]

sorry, my fault on the numbers in q. they were just random junk as the rest of the data was made up as well. should have calculated it instead like a good girl.
The security one works, thank you.
In relation to the ASX one, there is other data in that field. Can I use *ASX* in order to capture all of them?

I also have another column which has either a number or n/a. How would I use that as a condition?
Thanks for such a prompt response.

[Bob Phillips]

No, you can't use *ASX* in this type of formula, use a format as per my first, using ISNUMBER(SEARCH(... as the condition.

For the new one, just use ISNUMBER as the condition, numbers will pass, #N/A or text will fail, so the days associated with those will not get counted.

[Bob Phillips]

Actually, there is a more simple method that might be more meaningful to you

=SUMIF(O:O,"*security*",Q:Q)/COUNTIF(O:O,"*security*")

[gemsera]

It all works now, with some tweaking :)
Thank you very much!

[gemsera]

Ok, so the boss has asked for some more detailed information.

What I need now is:

The average time between date 1(column X), and date 2(column y), IF it is Phase 1, IF it is Priority A, IF it is product ADS. This is utilising more data than the previous spreadsheet.

I dont have a column of average numbers for this, would prefer if excel can average it into one formula. If need be I can add it. any thoughts?

[Bob Phillips]

=AVERAGE(IF(J2:J200="ADS",X2:X200-Y2:Y200))

still an array formula

[gemsera]

yes but now I have three variables. ?

[Bob Phillips]

So, just extend it

=AVERAGE(IF((J2:J200="ADS")*(phase_range=1)*(priority_range="A"),X2:X200-Y2:Y200))

[gemsera]

I wasnt sure if that was allowed. thanks :)

[Bob Phillips]

Well, someone else might mind, but I don't :-)

[gemsera]

:)

Ok so I have done this, and its giving out some weird answers. Such as Div/0 and -19826.521 and -40 (40 seems right, not sure about the minus part).

The formula is:
=AVERAGE(IF((SPIR!AL2:AL148="Phase 1")*(SPIR!G2:G148="A")*(SPIR!K2:K148="Sonus-ASX")*(SPIR!I2:I148="Closed"),SPIR!Y2:Y148-SPIR!AI2:AI148))

Could this just be weird data? I have checked for blank fields, which they shouldnt be if they meet the criteria of closed.

[Bob Phillips]

How abou the workbook so we can see this wierdness?

[gemsera]

Uh, ill take out the identifying stuff... wont be long :)

[gemsera]

I had to take quite a bit of data out - as the file was 3mb. and had to zip as well :)

thank you again!

[Bob Phillips]

e4:

=IF(SUMPRODUCT((SPIR!$AL$2:$AL$148=$D$3)*(SPIR!$G$2:$G$148=E$3)
*(ISNUMBER(SEARCH($D4,SPIR!$K$2:$K$148)))*(SPIR!$I$2:$I$148="Closed")),AVERAGE(IF(
(SPIR!$AL$2:$AL$148=$D$3)*(SPIR!$G$2:$G$148=E$3)
*(ISNUMBER(SEARCH($D4,SPIR!$K$2:$K$148)))*(SPIR!$I$2:$I$148="Closed"),
SPIR!$Y$2:$Y$148-SPIR!$AI$2:$AI$148)),"")

array formula as ever.

[gemsera]

wow, sorry but could you explain a little?

[Bob Phillips]

It is basically what you had, but I made it more flexible by referencing the lookup values in cells, I added the SEARCH(ISNUMBER bit I mentioned before to find values contained in cells, and I added a test so that a mismatch gave blank, not #DIV/0

[gemsera]

did you test it, cause its only giving me blanks? sorry to sound rude, im just confused is all :)

[Bob Phillips]

Yes, it gave me lots of blanks too because there was very little matching data, but I got some results.

You may also not have noticed that the post added an embedded space in the AVERAGE, remove that.

[gemsera]

ok, great, thank you once again for all your help :)