Auto-populating Avg. of reported data and dispersing among different "Teams"
I created a form to input data that is collected on a sheet. My sheet correctly displays the correct information. I have Columns listed as Room 100-Room 110 as well as a Total column. Each room can have a score of 1-6. My total Column correctly displays the correct number when you add all the scores together for Room 100-Room 110.
Is there a way to now to disperse the data from Room 100-Room 110 and evenly spread it over 2-3 teams. Example: Room 100-3, Room 107-1, Room 108-2, Room 109-1, Room 110-1 The total Column will display 8.
Now I want to take the data from each individual room and disperse it two two teams automatically?
Team 1: Room 100-3 & Room 107- 1
Team 2: Room 108-2 & Room 109-1 & Room 110- 1
Answers
-
Are you able to provide some screenshots with mocked up data?
-
So now I would want to take the data from one specific date and evenly (as much as you can) disperse the "room data" to two teams (Team 1 and Team 2). For 10/16/24 It's even as it's 18. So is there a way It could automatically assign room numbers (up to 9) to team 1 and the other room numbers to team 2.
-
What would you want the final output to look like? Manually entered data to provide an example would be fine.
-
@Paul Newcome
10/16/2024Team 1: Room 100, 101,102, 103, 104, 105
Team 2: Room 106, 107, 108, 109, 110
10/17/2024
Team 1: Room 100, 101,103,105
Team 2: Room 106, 110
10/17/2024
Team 1: Room 101,103,110
Team 2:Room 105
10/17/2024
Team 1:Room 104, 105, 109
Team 2: Room 103, 108
I am not sure if something like that can be automatically done but I thought I would ask.
-
Ok. I have a few ideas. What if it is an odd number such as 15? What if the total across the row is only 1 (not sure if that is even possible or not but figured I would ask just in case)?
-
Good questions.
So the "score of 15" would be distributed as evenly as you can get. The tricky part, and Smartsheets might not be able to do this, is that the higher the score the higher the risk of room.
So with 15, You wouldn't want to give Team 1 (Room 103, Room 104) and give Team 2 the other remaining rooms which equal 5. You would want it dispersed so Team 1 has Room 103,109,110 (6 +1 +1) and Team 2 has Row 104 and 105 (4 +3). This is based on screenshot above.
The Score of 1: Not likely. But it could happen. Then either team would get the one. Wouldn't make much difference in this case. -
Unfortunately that logic isn't going to be possible. IF we were to just assign rooms from left to right and cap it at the halfway point, we could possibly do that, but the mixing and matching won't be possible in Smartsheet.
-
@Paul Newcome Appreciate your assistance. What I might end up doing is having smartsheet calculate the total and what the split would be for those two teams. Then I will get the teams to manually assign rooms based on Smartsheet calculation.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!