+ Reply to Thread
Results 1 to 3 of 3

Average based on names

  1. #1
    Registered User
    Join Date
    06-04-2009
    Location
    Home
    MS-Off Ver
    Excel 2003
    Posts
    11

    Average based on names

    Hi all,

    I have a table that looks something like this:
    Code Days
    A 7
    B 9
    A 2
    C 1

    Etc.. Now I would like to create an average of all the days for a particular code, i.e. for code A, I want the average number of days for all "day" entries that belong to code A... for code B, the average of all "day" entries that belong to code B, etc...

    Any ideas?

    Thank you.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Average based on names

    The simplest solution would be to use a Pivot Table with Code as ROW Field and Days as DATA Field set to Avg.

    Failing that AVERAGE Array (unless using XL2007 in which case AVERAGEIF), ie:

    =AVERAGE(IF($A$2:$A$5="A",$B$2:$B$5))
    committed with CTRL + SHIFT + ENTER

    To reiterate though, a Pivot Table would be preferable IMO.

  3. #3
    Forum Contributor
    Join Date
    04-16-2009
    Location
    Stellenbosch, South Africa
    MS-Off Ver
    Excel 2003; Excel 2007; Excel 2010; Excel 2013
    Posts
    136

    Re: Average based on names

    You could use a pivot table and drag the code to the row segments and the days to the data segment. Then change the function of days to 'Average'.

    Or for formula:

    =SUMIF(A1:A10,"A",B1)/COUNTIF(A1:A10,"A")

    or

    =AVERAGE(IF(A1:A10="A",B1:B10))

    ...but this 2nd formula must be committed with CONTROL+SHIFT+ENTER because it is an array formula.
    Regards

    Jon (Excel 2003, 2007, 2010, 2013)

+ 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