# Average based on names

1. ## 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. ## 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. ## 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.

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

#### 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