Link to home
Start Free TrialLog in
Avatar of wrt1mea
wrt1mea

asked on

I need an average for certain criteria in Excel 2007

I am trying to find an average number of days it takes to complete a task.

Column A has the agent name
column B has the date assigned
Column C has the date completed.

I want to average up the information for say, Agent John Smith across a large range. I will be referencing the agent names by a list in a cell range so i would need it to refernce A5 for "John Smith" (I have 183 agents so I dont want to have to type their names in a formula.

Thanks!
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

Assuming you have data on data worksheet then try like this

=AVERAGE(IF(Data!A$2:A$1000=A5,Data!C$2:C$1000-Data!B$2:B$1000))

confirmed with CTRL+SHIFT+ENTER

assumes that all rows have both dates.....

regards, barry
Here's an example of the above suggestion.....

regards, barry
26823898.xlsx
Avatar of wrt1mea
wrt1mea

ASKER

what if all the rows do not have both dates? I am returning an "#N/A"
Avatar of wrt1mea

ASKER

OK, the "#N/A" error was due to me not adding another zero to the range fro A, B, & C....

I was able to correct that, but the value is coming up 0" for everything
ASKER CERTIFIED SOLUTION
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of wrt1mea

ASKER

=AVERAGEIFS(Data!C$2:C$1000,Data!A$2:A$1000,A5,Data!C$2:C$1000,"<>")-AVERAGEIFS(Data!B$2:B$1000,Data!A$2:A$1000,A5,Data!C$2:C$1000,"<>")


Works great! Thanks for the help!