Distribution loop

[Martin]

Dear all,

I have 4 bags and 4 apples and want to put 1 apple in each bag. Easy.

However when I have 3 apples and want to put them in the first 3 bags out of
the 4 bags.

When I have 5 apples and want to put 4 into each bag and add the additional
apple to the first bag.

The number of apples and the number of bags can change from case to case.
For example I could have 10 apples and 6 bags, 3 apples and 10 bags etc

How do I do this? Can I do it in the spreadsheet or do I need to produce a
macro?

Any help highly appreciated.

--
Regards,

Martin

[Tom Ogilvy]

You could use division and the mod function. The mod function would tell
you the remainder which you would distribute 1 per bag until exhausted.

--
Regards,
Tom Ogilvy





"Martin" <martin@mail.com> wrote in message
news:59B513A8-7A6A-4FE3-A21F-43CD7E9B3E4C@microsoft.com...
> Dear all,
>
> I have 4 bags and 4 apples and want to put 1 apple in each bag. Easy.
>
> However when I have 3 apples and want to put them in the first 3 bags out
of
> the 4 bags.
>
> When I have 5 apples and want to put 4 into each bag and add the
additional
> apple to the first bag.
>
> The number of apples and the number of bags can change from case to case.
> For example I could have 10 apples and 6 bags, 3 apples and 10 bags etc
>
> How do I do this? Can I do it in the spreadsheet or do I need to produce a
> macro?
>
> Any help highly appreciated.
>
> --
> Regards,
>
> Martin

[Bernie Deitrick]

MArtin,

If you put the number of bags in A2 and the number of apples in B2:

These give bags/apples and bags/apples
=MOD(B2,A2)
=IF(MOD(B2,A2)<>0,INT(B2/A2)+1,0)
=A2-MOD(B2,A2)
=INT(B2/A2)

You can even make a sentence from it. All in one cell:

=IF(MOD(B2,A2)<>0,IF(MOD(B2,A2)=1,"There is 1 bag","There are " & MOD(B2,A2) & " bags") & " with "
&INT(B2/A2) +1 & " apple" & IF(INT(B2/A2)+1>1,"s","") & " and t", "T") & "here are " & A2-MOD(B2,A2)
& " bag" & IF( A2-MOD(B2,A2)>1,"s","") & " with " &INT(B2/A2) & " apple" & IF(INT(B2/A2)>1,"s.",".")


HTH,
Bernie
MS Excel MVP


"Martin" <martin@mail.com> wrote in message
news:59B513A8-7A6A-4FE3-A21F-43CD7E9B3E4C@microsoft.com...
> Dear all,
>
> I have 4 bags and 4 apples and want to put 1 apple in each bag. Easy.
>
> However when I have 3 apples and want to put them in the first 3 bags out of
> the 4 bags.
>
> When I have 5 apples and want to put 4 into each bag and add the additional
> apple to the first bag.
>
> The number of apples and the number of bags can change from case to case.
> For example I could have 10 apples and 6 bags, 3 apples and 10 bags etc
>
> How do I do this? Can I do it in the spreadsheet or do I need to produce a
> macro?
>
> Any help highly appreciated.
>
> --
> Regards,
>
> Martin

[Martin]

Thanks a lot Bernie,

I think this is going to be helpful. I need to work my way around it - then
I might need to post another question.

--
Regards,

Martin


"Bernie Deitrick" wrote:

> MArtin,
>
> If you put the number of bags in A2 and the number of apples in B2:
>
> These give bags/apples and bags/apples
> =MOD(B2,A2)
> =IF(MOD(B2,A2)<>0,INT(B2/A2)+1,0)
> =A2-MOD(B2,A2)
> =INT(B2/A2)
>
> You can even make a sentence from it. All in one cell:
>
> =IF(MOD(B2,A2)<>0,IF(MOD(B2,A2)=1,"There is 1 bag","There are " & MOD(B2,A2) & " bags") & " with "
> &INT(B2/A2) +1 & " apple" & IF(INT(B2/A2)+1>1,"s","") & " and t", "T") & "here are " & A2-MOD(B2,A2)
> & " bag" & IF( A2-MOD(B2,A2)>1,"s","") & " with " &INT(B2/A2) & " apple" & IF(INT(B2/A2)>1,"s.",".")
>
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Martin" <martin@mail.com> wrote in message
> news:59B513A8-7A6A-4FE3-A21F-43CD7E9B3E4C@microsoft.com...
> > Dear all,
> >
> > I have 4 bags and 4 apples and want to put 1 apple in each bag. Easy.
> >
> > However when I have 3 apples and want to put them in the first 3 bags out of
> > the 4 bags.
> >
> > When I have 5 apples and want to put 4 into each bag and add the additional
> > apple to the first bag.
> >
> > The number of apples and the number of bags can change from case to case.
> > For example I could have 10 apples and 6 bags, 3 apples and 10 bags etc
> >
> > How do I do this? Can I do it in the spreadsheet or do I need to produce a
> > macro?
> >
> > Any help highly appreciated.
> >
> > --
> > Regards,
> >
> > Martin
>
>
>

[Martin]

Dear All,

Thanks to Bernie I am half way there. I also need to put the apples in each
bag. In the spreadsheet example below I have 4 bags and 7 apples and then
want 2 apples in the first 3 bags and 1 apple in the 4th bag.

Column A Column B
# of Apples
Bag1 2
Bag2 2
Bag3 2
Bag4 1

Can I use Bernie's formula to distribute the apples automatically into the 4
bags?

--
Regards,

Martin


"Bernie Deitrick" wrote:

> MArtin,
>
> If you put the number of bags in A2 and the number of apples in B2:
>
> These give bags/apples and bags/apples
> =MOD(B2,A2)
> =IF(MOD(B2,A2)<>0,INT(B2/A2)+1,0)
> =A2-MOD(B2,A2)
> =INT(B2/A2)
>
> You can even make a sentence from it. All in one cell:
>
> =IF(MOD(B2,A2)<>0,IF(MOD(B2,A2)=1,"There is 1 bag","There are " & MOD(B2,A2) & " bags") & " with "
> &INT(B2/A2) +1 & " apple" & IF(INT(B2/A2)+1>1,"s","") & " and t", "T") & "here are " & A2-MOD(B2,A2)
> & " bag" & IF( A2-MOD(B2,A2)>1,"s","") & " with " &INT(B2/A2) & " apple" & IF(INT(B2/A2)>1,"s.",".")
>
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Martin" <martin@mail.com> wrote in message
> news:59B513A8-7A6A-4FE3-A21F-43CD7E9B3E4C@microsoft.com...
> > Dear all,
> >
> > I have 4 bags and 4 apples and want to put 1 apple in each bag. Easy.
> >
> > However when I have 3 apples and want to put them in the first 3 bags out of
> > the 4 bags.
> >
> > When I have 5 apples and want to put 4 into each bag and add the additional
> > apple to the first bag.
> >
> > The number of apples and the number of bags can change from case to case.
> > For example I could have 10 apples and 6 bags, 3 apples and 10 bags etc
> >
> > How do I do this? Can I do it in the spreadsheet or do I need to produce a
> > macro?
> >
> > Any help highly appreciated.
> >
> > --
> > Regards,
> >
> > Martin
>
>
>