excel help - formulas

[mooks]

I need to show that the manuscript processing time, averaged per issue, has increased during a particular time frame.

My data is currently in the following format:

[table="width: 500, class: grid"]
[tr]
[td]VolIss[/td]
[td]AcceptDate[/td]
[td]DespatchDate[/td]
[td]Months passed[/td]
[/tr]
[tr]
[td]120/10[/td]
[td]21-Aug-09[/td]
[td]05-Nov-09[/td]
[td]3[/td]
[/tr]
[/table]

Under the VolIss column, I have 43 unique values but a total list of 2000+ manuscript entries.

So I need excel to look at column VolIss (column A) and calculate the average number of Months Passed (column D) for all manuscripts in that issue.

Because there are a variable number of manuscripts per VolIss, to do this manually would take too long.

(I know this could be done with filtering per VolIss value and then just averaging the figures in column D, but this spreadsheet is a template that will be applied to much larger sample ranges in future).

Help appreciated

 

[RexxGrim]

Don't know if you're familiar with Pivot Tables.

If you put the data in a pivot table. Put the "VolIss" as row lables and the "Months passed in the body/data part of the table.

Then change the "months passed" "Value Field settings" to average. The default should be sum. You can do this by right clicking on the data part of the pivot table and selecting Value Field Settings.

 

[mooks]

Thanks, forgot about Pivot Tables (I'm not that great with them). Have used a combination of COUNTIF & SUMIF and then used those values to calculate the average, but I'm sure there must be a neater way.

Will give Pivot Tables a bash!

 

[Ou grote]

I've added 3 columns to your sheet, and 30 rows to test.

In column E, I use =COUNTIF($A$2:$A$30,A2)
Counts the number of VolIss

Column F, I Use =SUMIF($A$2:$A$30,A2,$D$2:$D$30)
Adds the months

Column G, I use =F2/E2
Does the average.

A few other ways to do it, I'd probably write a VBA function to do it.