locked
Averageifs statement. RRS feed

  • Question

  • Hi, I've got an excel query about using the averageifs statement. The data table is many thousands lines long with so many different companies which I need to arrange by. I have created a similar simplified and smaller data table below to try and help explain the problem I am having.


    What I'm trying to do is to get the average duration for the final column based on certain criteria.
    The problem I have is that I want to do this based on company name as criteria1 in the averageifs statement. So for example if PJ Smith appears three times, I want this average in 'Column J'. I know how to do this but I would like to do is to create a macro (I don't know if it's possible) that will save me from manually having to enter each time into the formula statement 'PJ Smith'. Ideally, I could filter out 'Reloading' and 'Shelf Stacking' in the purpose column by using <> in the statement.

    Once I've then got the company duration averages worked out, I would then like to do this by location averages (again, I don't want to manually type up 1000s of locations do this).

    I wondered whether there was a simpler way to do this where it could be like an autofeed into VBA. I'm quite new to Excel and VBA, so a few pointers would be apprecited.

    Jono

    PS - in time it would be useful to do some kind of analysis 

    eg.

    Location  Company   Date   Job ID         Purpose         Book on time     Book off time      Duration

    Wales      PJ Smith   01/01  220             Simple          00:30                 00:40                00:10

    Scotland    N Smith  01/01  229             Simple          00:30                 00:50                00:20

    England     T Derby  03/10  221             Complicated  00:40                  01:45               01:05

            
    Wednesday, February 13, 2013 5:14 AM

Answers

  • sorry this

    =SUMIF($D$11:$D$15,D11,$I$11:$I$15)/COUNTIF($D$11:$D$15,D11)

    • Marked as answer by 许阳(无锡) Tuesday, February 19, 2013 6:56 AM
    Wednesday, February 13, 2013 8:12 AM

All replies

  • Assuming that duration is calculated by you and is time format use

    =SUMIF($D$11:$D$15,D11,$I$11:$I$15)/COUNTIF(D11:D15,D11)

    D = Name

    I= Duration

    David

    Wednesday, February 13, 2013 8:11 AM
  • sorry this

    =SUMIF($D$11:$D$15,D11,$I$11:$I$15)/COUNTIF($D$11:$D$15,D11)

    • Marked as answer by 许阳(无锡) Tuesday, February 19, 2013 6:56 AM
    Wednesday, February 13, 2013 8:12 AM