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

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" wrote in message
...
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,

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)+11,"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" wrote in message
...
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

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)+11,"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" wrote in message
...
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

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)+11,"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" wrote in message
...
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