Expert: Richard Rost - 7/10/2009

Question
QUESTION: Hi, wondering if you can answer the following for me. I have an Excel 2007 sheet into which I export information from my Customer Services helpdesk. The columns in this, therefore, contain various bits of information, one of which is a column for Reason (the reason for the Customer Services case)(Column O). I have another column, which counts the number of days a case has been open for (column AT), and another column that has the department that the customer services issue relates to (for example "Sales")(which is column K).

I need a formula so that I can find the average number of days open (data from column AT), but only for rows that relate to a specific department (data from column K) and only for which the Reason is NOT "credits" (data from column O).

I tried doing an averageif NOT function, but i can't get it to work.

Does your magical mind have any ideas?

ANSWER: Well, you could make another column that's based on those criteria and will either contain the VALUE or BLANK if the criteria are met. For example:

=IF(AND(K1="Department Name",O1<>"credits",AT,"")

So now this new column will have either the value from AT (number of days) or will be blank "" if the criteria is/is not met.

Now just average up the values in that column, and that should do what you want.

Let me know if you have any other questions or comments.

Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts


Cordially,

Richard Rost
599CD Computer Training
http://www.599cd.com

P.S. Also, don't forget to visit my FREE Microsoft Excel Tips & Tricks Page at http://www.599cd.com/Tips/AllExperts

P.P.S. I volunteer my time at AllExperts to help people, and I get a LOT of questions, so I can't take an hour to answer each question. If you need more DETAILED HELP, come to my TechHelp web site at http://www.599cd.com/TechHelp/AllExperts and I'll take as much time as you need to answer your question.

Out of work? Know someone who is? Get a FREE copy of my Computer Job Skills CD at http://www.599cd.com/JobSkills?key=AllExperts

---------- FOLLOW-UP ----------

QUESTION: Thanks for that - brilliant!
One other thing - how do i get it to pick out rows which simply 'contain' the word "credit" in column O ?

Cheers again!

Answer
You could use the FIND or SEARCH functions to determine if a text string (like 'credit') appears anywhere in the cell.

Let me know if you have any other questions or comments.

Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts


Cordially,

Richard Rost
599CD Computer Training
http://www.599cd.com

P.S. Also, don't forget to visit my FREE Microsoft Excel Tips & Tricks Page at http://www.599cd.com/Tips/AllExperts

P.P.S. I volunteer my time at AllExperts to help people, and I get a LOT of questions, so I can't take an hour to answer each question. If you need more DETAILED HELP, come to my TechHelp web site at http://www.599cd.com/TechHelp/AllExperts and I'll take as much time as you need to answer your question.

Out of work? Know someone who is? Get a FREE copy of my Computer Job Skills CD at http://www.599cd.com/JobSkills?key=AllExperts