Divide the total in one cell into up to 6 others

[Dos Equis]

Hi,

Does anyone know how to divide the number in one cell and have up to 6
other cells equal the original number? i.e. 65 = 11, 11, 11, 11, 11,
10.

Thanks for any help

Byron

 

[Guest]

hi

assuming the 65 is on the a2

on b2 use:

=$a2/6
copy it util f2
and on g2 use a2-sum(b2:f2)

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Dos Equis" escreveu:

 

[Pete_UK]

Just a slight tweak - use:

=ROUND($a2/6,0)

Hope this helps.

Pete

 

[Dos Equis]

Thanks guys, that function helps, but it's returning incorrect data.
If $a2 is 47, $a3-9 return 45 and the sum in $a10 is 270. Not what I
was looking for. Should have ended with 12,12,12,11 in the first 4
cells and totaled 47 in $a10

It does give me something to work with though so thank you. If you
have any other ideas, I'll still be checking this post..

Thanks again,

Byron

 

[Pete_UK]

Just check your formula in A3 to A9 again - it looks like you have
=$A2-2 for it to give you 45 in those cells.

In your original post, you said you wanted to split the number into 6
cells, whereas you now quote 47 split into 4 - is the number of cells
variable, and if so, where (which cell) would this be located?

Pete

 

[Dos Equis]

Pete,

I checked my cells and then copy and pasted the formula's paosted here
to the cells, same result.

The numbers go in cells C12 - C20, I put in the number of items to be
grouped with a minimum of 10 and maximum of 15. I currently have from
12 to 69 in a group. I was thinking a formula that started by dividing
the number of items by 10 would tell me how many groups I will have but
it also has to account for any left overs. For instance, if I start
with 18, only 15 can go in a group and the other 3 have to be accounted
for.

What I'd like is something like this formula in an outside block:
(Block C12) 47
(Block A137) =Round C12/10(4)
(Block A138) =If[4 cells](D12:G12)<C12,D12+1 Loop, E12+1, loop,
F12+1...

I know the code above is invalid, but that's the kind of thing I'm
looking for. I should evaluate the number of cells needed, total the
sum of those cells, compare that sum to the number of items listed and
add one to consecutive cells until the number of items is reached.

Yes, I'm asking alot, but it should be possible and probably easy, just
not obvious.
Anyway, Thnaks for your help sofar,

Hey,

I just came up with this:

=IF(N22>3,ROUND(C22/N22,0),0)

That's the 4th cell in line and N22 divides C22/10 giving me 4 in that
cell.

With that formula, I end up with 12 in the frist 4 cells and 0 in the
last 2 for a total of 48 instead of the 47 I'm looking for.

Any thoughts?

 

[Dos Equis]

Oh, and it was gining me 45 because I was refering to the wrong cell.
C21 instead of C22, large difference.

Pete,

I checked my cells and then copy and pasted the formula's paosted here
to the cells, same result.

The numbers go in cells C12 - C20, I put in the number of items to be
grouped with a minimum of 10 and maximum of 15. I currently have from
12 to 69 in a group. I was thinking a formula that started by dividing
the number of items by 10 would tell me how many groups I will have but
it also has to account for any left overs. For instance, if I start
with 18, only 15 can go in a group and the other 3 have to be accounted
for.

What I'd like is something like this formula in an outside block:
(Block C12) 47
(Block A137) =Round C12/10(4)
(Block A138) =If[4 cells](D12:G12)<C12,D12+1 Loop, E12+1, loop,
F12+1...

I know the code above is invalid, but that's the kind of thing I'm
looking for. I should evaluate the number of cells needed, total the
sum of those cells, compare that sum to the number of items listed and
add one to consecutive cells until the number of items is reached.

Yes, I'm asking alot, but it should be possible and probably easy, just
not obvious.
Anyway, Thnaks for your help sofar,

Hey,

I just came up with this:

=IF(N22>3,ROUND(C22/N22,0),0)

That's the 4th cell in line and N22 divides C22/10 giving me 4 in that
cell.

With that formula, I end up with 12 in the frist 4 cells and 0 in the
last 2 for a total of 48 instead of the 47 I'm looking for.

Any thoughts?

Just check your formula in A3 to A9 again - it looks like you have
=$A2-2 for it to give you 45 in those cells.

In your original post, you said you wanted to split the number into 6
cells, whereas you now quote 47 split into 4 - is the number of cells
variable, and if so, where (which cell) would this be located?

Pete