Calculate the number of instances of a certain time then evenly distribute the number

Hi all,

I would like to be able to count the number of times a specific value occurs then spread this number counted out evenly over several other cells.

I have counted the number of instances of the value occurs using a simple sumif formula. I would now like to split this number evenly over several other cells, I have done this but because the range of the cells provided is more than the number the formula I am using comes back with minus figures in some of the cells.

For example

In B2= 5

I want to evenly spread the figure above out over 8 potential cells so:-


B C D E F G H I J
2 5 1 1 0 1 1 0 0 1


If I am able to do this I also wondered if I could expand on this to include more criteria but for now I wanted to see if the above was possible.

thanks in advance for any help.

I don't understand your example - do you want the value 5/8 in each cell?

Pete

Sorry Pete,

It seems to have formatted this differently to what I initially wrote, so in cell B2 I have a value of 5.

In cells C2-J2 I want to try and evenly distribute these numbers, I have used a round/rand formula but as there is more available cells than the total number it is showing some cells as -1. I need it to be whole numbers and no minus figures.

so maybe:-
1,1,0,1,0,0,1,0

hope that helps?

Put this formula in M1:

=RAND()

and copy across into N1:T1 so that you have 8 random numbers.

Use A1 for the number (5), then you can use this formula in B1:

=IF(RANK(M1,$M$1:$T$1)<=$A1,1,0)

and copy this across into C1:I1. Then you can just press F9 to get another distribution pattern. You can also change the value in A1.

Hope this helps.

Pete

Hi,

thanks for getting back to me, the formula worked well until the number (that I gave as 5 in the above example) went above 8 all the cells stayed as one and did not increase to equate to the specified number.

This is what you said in your first post:

... I want to evenly spread the figure above out over 8 potential cells ...

but now you seem to be implying that you want this to be a variable number of cells. Please explain what your new requirements are with reference to some examples.

Pete

Hi,

The number of cells that I split the number over will not change, it will always be split out over 8 cells.

The only thing that will change is the initial figure that I require to be distributed amongst these cells, in the first example it was 5 (in cell B2) but will consistently change. When I have used the above formula it will evenly spread out the initial figure until this goes over 8, each cell will then remain at one and will not rise above this in order to total correctly.

hope that makes sense?

So, if you had a value of 23, for example, you would want the total of the 8 numbers to equal 23, but they might be distributed like:

4,1,7,0,3,6,0,2

or do you want them to be fairly even, like:

3,3,3,3,3,3,3,2

If the first case, are there any constraints on how large the numbers could be relative to the initial number?

Pete

Hi Pete,

Yes so if the initial figure came back as 23 I would need that number to be spread over the 8 specified cells, ideally evenly but if that is not possible I would each cell to be no more than two higher or lower.

Jacc,

I can certainly post a workbook as I have one that I am working on, I thought I would be able to explain it well enough but I seem to be doing a poor job of it today.

thanks,

Okay, same arrangement as before, with the initial number in A1, you can put this formula in B1:

=IF(RANK(M1,$M$1:$T$1)<=$A1,IF($A1<=8,1,IF(COLUMNS($B:B)<=MOD($A1,8),ROUNDUP($A1/8,0),ROUNDDOWN($A1/8,0))),0)

then copy across. The random numbers are only needed to give you a random distribution across the 8 cells if your initial number is less than 8. If the number is larger than this, the new formula will give you an even distribution, with the numbers on the left rounded up and the numbers to the right rounded down to maintain the total.

I've attached the Excel file that I worked on, as we seem to have different layouts.

Hope this helps.

Pete

Works fantastically, thank you so much for you patience and help!.

Elle

You're welcome - glad we finally got there.

Thanks for the rep.

Pete