Auto-populating Avg. of reported data and dispersing among different "Teams"

Dreim

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

Paul Newcome

Are you able to provide some screenshots with mocked up data?

Dreim

@Paul Newcome

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.

Paul Newcome

What would you want the final output to look like? Manually entered data to provide an example would be fine.

Dreim

@Paul Newcome
10/16/2024

Team 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.

Paul Newcome

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)?

Dreim

@Paul Newcome

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.

Paul Newcome

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.

Dreim

@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.