Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Count, sum, average
I use Smartsheet to track employee certifications. I'd like to determine the average number of certifications per person at each site/area. I'm able to use an equation to determine the number of certifications each person has, but that occurs in each row which throws off the average. In other words, =IF(Status1 = "Inactive", 0, COUNTIF(EmpNumber:EmpNumber, EmpNumber1))
The above formula gives me the numbers, but the problem is that if someone has 10 certifications, they would have 10 - 10's in that column which would throw off the average.
I am not sure what I need to do with this information to get an accurate ratio of certifications per person. I appreciate anyone's assistance with this.
Comments
-
I'm missing some information to give a full answer.
With the formula you used, you have the total number of certificates for each employee. Bob will have 10, Mary 5, etc. But if you are copying this formula down the column (which is what you imply), then you are likely putting them in the wrong place.
If, instead, row 1 contained a 'summary row' - that is a row that will be used to consolidate your totals, then you would put Bob in the EmpNumber1 cell and your formula would still return 10 (because Status1 would be something besides "Inactive"). Each employee will have their own row in the 'summary section'
(I would move this to row 2 and have a parent row for the summary section - you'll be able to collapse / semi-hide it and it will be easier for the sum/counts to come)
I'm assuming that Bob is only at one site/area.
There must be somewhere on the rest of the sheet where we know Bob is from site/area "Area 51".
In the Summary section you'll be able to get a total of number of certificates by site/area.
You'll need to count how many people are in each area.
Each area will then be able to determine average certificates / per person.
I hope that is clear and helps
Craig
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives