+ Reply to Thread
Results 1 to 6 of 6

Average only the 1st item when there are duplicates in a list

  1. #1
    Forum Contributor
    Join Date
    04-13-2010
    Location
    England
    MS-Off Ver
    Office 2016 365
    Posts
    211

    Average only the 1st item when there are duplicates in a list

    Hi everyone,

    I have a set of data which lists the details of work orders. A work order can have several segments (say £1000 for Parts, £2000 for labour etc...) however, as a whole the work order will only be so many days old. So i need to average out how old the work orders but avoid double counting duplicate entries in the calculation.

    I have attached an example which illustrates my problem and desired result. I've been scratching my head on how to achieve this for some time now, any help would be greatly appreciated!

    Thanks in advance,

    J
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    08-14-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    573

    Re: Average only the 1st item when there are duplicates in a list

    Hi, You can use this formula in C11.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    04-13-2010
    Location
    England
    MS-Off Ver
    Office 2016 365
    Posts
    211

    Re: Average only the 1st item when there are duplicates in a list

    Thanks Sindhus,

    I probably should have extrapolated the example a little further. The reason i was looking for a row by row formula was because the data forms part of a table which then feeds a pivot table allowing a user to slice and dice information by (for example), region. Take a look at the attached for a bit more detail.

    The report is currently structured this way so was trying to manipulate the data to fit the current structure...

    EDIT: On the attached you can filter on two regions but my data has a dozen regions and a sub-filter for another hundred or so stores...so quite a lot to filter on!
    Attached Files Attached Files

  4. #4
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Average only the 1st item when there are duplicates in a list

    In that case in E3 use..

    =IF(COUNTIF($A$3:A3,A3)>1,"",C3)

    Copy down and do the conventional average in Column E
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  5. #5
    Forum Contributor
    Join Date
    04-13-2010
    Location
    England
    MS-Off Ver
    Office 2016 365
    Posts
    211

    Re: Average only the 1st item when there are duplicates in a list

    Thanks Ace,

    I have been messing around with CountIf for about 2 hours and just couldn't figure it out!

    Thanks a million.

    J

  6. #6
    Valued Forum Contributor
    Join Date
    08-14-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    573

    Re: Average only the 1st item when there are duplicates in a list

    Oh! got your requirement after seeing Ace_Xl post.
    Last edited by Sindhus; 10-08-2012 at 06:21 AM.

+ 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