Averages

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.

Find more posts tagged with

Healthcare

Project Management

Account and User Management

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Automated email with number of pending records
Hello, I use Smartsheet in such way that people are asked to provide data on many records and then that data needs to be approved by one other person (always the same person). I would like to set up an automation that, each morning, checks to see if there are records waiting to be approved, and if there are, sends an…
How to Share Single Workspace Folder?
I have a workspace that I created with two subfolders. A user needs to have access to only one of them but when I have that folder selected, I can only share the entire workspace with them. Is there a secret way that I can share the parent folder and ensure that any new files created will be shared with the same people? I…
License Question
I already have a Smartsheet license with my company, but I was recently invited to collaborate on a sheet owned by another group within the same organization. When I accepted, I was told I need to upgrade in order to edit or contribute. Can you please explain why this is required? It seems unreasonable to need multiple…