1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Averageifs

Discussion in 'Business Applications' started by jonathan220, Feb 12, 2013.

Thread Status:
Not open for further replies.
  1. jonathan220

    jonathan220 Thread Starter

    Joined:
    Feb 12, 2013
    Messages:
    1
    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 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 Sainsburys 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 'Sainsburys'. Ideally, I could filter out 'Reloading' and 'Shelf Stacking' 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. 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




    Location Company Date Job ID Purpose Book on time Date Book off time Duration
    LocWales Sainsburys 03/02/2013 2256978 Shelf Stacking 11 01:25 04/02/2013 03:30 02:05
    Scotland Tesco 03/02/2013 2252890 S1 - No parties booked on today 1 04:06
    Newcastle Lidl 03/02/2013 2261740 Re-loading 3 00:26 04/02/2013 02:55 02:29
    Taunton Co-op 03/02/2013 2252890 S1 - No parties booked on today 1 18:18 03/02/2013 18:18 00:00
     
  2. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    you can just fill down a formula
    =AVERAGEIF(B:B,B2,I:I)
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1089278

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice