+ Reply to Thread
Results 1 to 4 of 4

Averaging a certain criteria.

  1. #1
    Registered User
    Join Date
    06-17-2010
    Location
    Essex, England
    MS-Off Ver
    Excel 2003
    Posts
    12

    Averaging a certain criteria.

    Evening Folks,

    This is only my second post since joining this site and like last time i have a predicament i need help with.

    I'm fairly new to Excel and i'm picking up as i go along. So heres my predicament............

    I've been tasked with monitoring how long my distribution centres drivers are delayed at stores based on an average. The problem is that i have a list a mile long of all the delays we've had this year.

    What i want to do is calculate an average by identifying the unique store number then ONLY average the delays for that specific store.

    The form is laid out:

    Column1 = Store ID No.
    Column2 = Store Name
    Column3 = Volume delivered
    Column4 = Volume collected
    Column5 = Length of delay (Mins)

    So i need to indentify the unique ID (column1) and average all the Delays (column4) for that store.

    Any ideas guys??

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Averaging a certain criteria.

    You can use a pivot table to do that...

    Data|Pivot Tables...Next... make sure correct 4 column range.. click Next...

    Click Layout, drag Store ID No from right side to Row area... then drag Length of Delay to Data Area... double click that dragged button and select Average.

    Choose where you want Pivot table to go... click Finish.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    06-17-2010
    Location
    Essex, England
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Averaging a certain criteria.

    Thanks for the help mate, that is the obvious choice and the best solution.....

    But lets say for learning purposes, what would the formula be to achieve the same result?

    Thanks again

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Averaging a certain criteria.

    For learning purposes...

    Make a list of unique IDs.. say in column F.. starting at F2...

    then =Average(If($A$2:$A$1000=F2,$E$2:$E$1000))

    Confirmed with CTRL+SHIFT+ENTER not, just ENTER.

    In XL2007... AVERAGEIF(A:A,F2,E:E) copied down

    or, in any XL version...

    =SUMIF(A:A,F2,E:E)/COUNTIF(A:A,F2) if your list is very large...


    copied down

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1