+ Reply to Thread
Results 1 to 4 of 4

Calculate average price of repeated items in list

  1. #1
    Registered User
    Join Date
    03-28-2012
    Location
    Birmingham, UK
    MS-Off Ver
    Excel 2010
    Posts
    4

    Calculate average price of repeated items in list

    Hi,

    I have two columns: a list of items and a list associated prices. How do I calculate the average price for the items that are repeated. I have given an simplified list as an example. the number next to the item represents the price:


    Green Apple 0.56
    Green Apple 0.60
    Green Apple 0.51
    Red Apple 0.35
    Red Apple 0.31
    Grape 0.89
    Pear 0.47
    Pear 0.45
    Pink Grapefruit 0.61
    Pink Grapefruit 0.59
    Pink Grapefruit 0.63
    Pink Grapefruit 0.57
    Red Grapefruit 0.56
    White Grape Fruit 0.74
    White Grape Fruit 0.73
    White Grape Fruit 0.81

    How would I find the average price of each fruit type automatically. I have tried using IF functions but ended up with too many arguments. I feel I want to tell excel to check if an item is repeated and if so, calculate an average of the numbers in the same row...but I cannot figure it out. Please help!

  2. #2
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,283

    Re: Calculate average price of repeated items in list

    Just use

    =SUMIF(A:A,A2,B:B)/COUNTIF(A:A,A2)

  3. #3
    Registered User
    Join Date
    03-28-2012
    Location
    Birmingham, UK
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Calculate average price of repeated items in list

    Amazing - Works perfectly. Thank you.

  4. #4
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Calculate average price of repeated items in list

    Or
    =AVERAGEIF(A:A,A2,B:B)

+ 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