Averages

Paulacosentino

Hello all.  Thank you for any help you can provide.

I have 5 fields containing averaged numbers.  I want to roll them all into 1 field to show the average for that specialty.  Anyone know of the right formula to use?  

In English, I have the average for 5 counties for primary care clinics and want just 1 average for the district.

Thanks again.

 

 

Paul Newcome

=AVG([Column Name]#, [Column Name]#, [Column Name]#, [Column Name]#, [Column Name]#)

This should do the trick for you. Just replace Column Name with the name of your column and the # with your row number. 

If they are all in adjacent cells within the same row:

=AVG([First Column Name]#:[Last Column Name]#

If they are in adjacent cells within the same column replacing 1 and 5 with the appropriate row numbers:

=AVG([Column Name]1:[Column Name]5)

If they are the ONLY cells within a column you can use:

=AVG([Column Name]:[Column Name])

Paulacosentino

Thank you!  Does this still work if the numbers are already averages?

 

bdchapin

Yes and no. It will average them, but it will give them all equal weight for that average.

So if they are averages with equivalent sample sizes, then this would work fine. But if there is a lot of variance in your sample sizes this may give a bit too much weight to areas with smaller sample sizes.

If you want to take a true average of all of the data, then you’ll need to either use the original data (before it was averaged), or adjust your average buy multiplying each average by the count and then dividing by the total count.

Paul Newcome

It also depends on how those averages are displayed. If the averages for your counties are coming form a formula, you will need to change formula for those smaller averages to:

=VALUE(Paste original formula here that gives your county average to include the ending parenthesis)

This will pull the actual number values as opposed to the text string that represents the number in the background of Smartsheet.

If you are concerned with making it a weighted average, you would add a column that lists how many clinics are in each county. Then you could use the following:

=AVGW([Column Name]1:[Column Name]5, [Number of Clinics]1:[Number of Clinics]5)

[Column Name]1:[Column Name]5 represents the range of the numbers you want averaged.

[Number of Clinics]1:[Number of Clinics]5 represents the corresponding range within the column containing the number of clinics in each county.

The AVGW function will give you a weighted average giving a higher importance to the counties with more clinics. 

Paulacosentino

THANK YOU for taking the time to show me.  I appreciate you!

Paulacosentino

THANK You.  That helps!  I appreciate your time!