Formula even distribution to equal 100%

[AAA_1327]

I need to distribute 100% across a set of columns. I have tried using =RANDBETWEEN(1,100) but it does not total to 100.

What I am looking for should look like this in the perfect world. Is there a formula that would do this:

A B C D E
Mike 5% 25% 70% 0% 0%
Jorge 100% 0% 0% 0% 0%
Dan 10% 0% 30% 20% 40%

 

[mancemonster]

What are you trying to achieve?

Is it a random allocation of the 100% across each of the names?

Regards

Roger

 

[AAA_1327]

Yes that is exactly what I need.

 

[Jonmo1]

You could try something like

A2: =RANDBETWEEN(0,100)
B2: =RANDBETWEEN(0,100-SUM($A$2:A$2))
Fill right to D2
E2: =100-SUM($A$2:$D$2)

 

[mancemonster]

Right,
how about...

in H2,3,4,5,6 =Rand()
In I6 =sum(H2:H6)

A= Mike B=H3/I6, C=H4/I6, D=H5/I6, E=H5/I6 F=H5/I6

You would then need to create another table for the next person...

Etc...

Probably not the cleanest way, but it'll work.

Regards

Roger

 

[Dave Patton]

new information; solution not appropriate

 

[AAA_1327]

Thanks everyone I tried the first solution it seems to work the best for what I need.

 

[AAA_1327]

None of these solutions truly work. Anyone else have an idea?