3 Replies Latest reply on Nov 8, 2019 10:08 AM by Subodh Gupta

# Average Number of Records by Group

Hi,

I am working with a data set from the National Science Foundation that has information on grants from the past few years.

I am trying to create a bar chart that shows the average number of awards for each type of institution.

This is what happens when I try to put [Type] and AVG[Number of Records] into the sheet, but it is obviously wrong because it shows 1 for each group.

• ###### 1. Re: Average Number of Records by Group

Hi Laurel,

Number of Records is set to 1 for each row. So the Average of that is 1.

You want to Average the number of awards for each type of institution over what would be my question?

Subodh.

• ###### 2. Re: Average Number of Records by Group

Hi Subodh,

I would liek to calculate the average number of awards for each institution type over number of organizations in each institution type.

Thanks

• ###### 3. Re: Average Number of Records by Group

Hi,

Assuming 'Organization' gives me individual organizations, I can calculate the number of organizations per type as:

{ FIXED  [Type]: COUNTD([Organization])}

Assuming each record represents an award, the number of awards per type as:

{ FIXED [Type] : SUM([Number of Records])}

The average would then be:

SUM([Awards/Orgs Type])/SUM([Number of Orgs/Org Type])

So I can get something like:

Then it's converting it to a bar-chart:

Hope that helps:

Subodh.