How to distribute items from different groups throughout a set number of columns??

[sauci11]

Howdy

I've been struggling with building a worksheet to help with some work functions, can seem to connect the math with the excel functions. Any help is greatly appreciated.

We have several sets of tools identified by color. There are a varying numbers of each colored set of tools. We also have determined how many times per year that each group of tool sets needs to be re-colored depending on usage. Part of our list could look like this,


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]# of sets
[/TD]
[TD]Times per year
[/TD]
[TD]Week 1
[/TD]
[TD]Wk 2
[/TD]
[TD]Wk 3
[/TD]
[TD]Wk 4
[/TD]
[TD]Wk 5
[/TD]
[TD]Wk 6
[/TD]
[TD]Wk 7....
[/TD]
[/TR]
[TR]
[TD]Red tools
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Green tools
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blue tools
[/TD]
[TD]2
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yellow tools
[/TD]
[TD]4
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

We are trying to have this data distributed evenly over 52 columns to correspond with the weeks in the year. The number of sets of each color group would change and the times per year would change, depending on usage and other factors.

Thanks in advance for any help!

 

[JLGWhiz]

Hi sauci11, welcome to the Forum
I am no Euclid at math, but I am good enough to know that the figures shown in the example cannot be distributed over 52 weeks in whole numbers. It is also unclear whether you are referring to distributing the sets of tools or the times they are re-colored. Maybe a more detailed explanation would help, like defining what you mean by distributing.

 

[sauci11]

Hi sauci11, welcome to the Forum
I am no Euclid at math, but I am good enough to know that the figures shown in the example cannot be distributed over 52 weeks in whole numbers. It is also unclear whether you are referring to distributing the sets of tools or the times they are re-colored. Maybe a more detailed explanation would help, like defining what you mean by distributing.

Ok. Lets just look at the Red tools.

There are 5 sets and each needs to be recolored 3 times per year, so there is a sequence of the sets that looks like this....1,2,3,4,5,1,2,3,4,5,1,2,3,4,5...that needs to be spread out over the 52 week calendar year. I understand that rounding will need to happen and distribution throughout the calendar won't be evenly spaced.

Does that help?