Counting problem - newspapers

[Guest]

Hello again,

I would make a simple sheet showing a count of newspapers to our delivery
drivers. They are making mistakes calculating complete and partial bundles
in their head.

They are used to seeing their total newspaper count for the day expressed as
X bundles and X singles. For instance, if the diver has 2678 newspapers and
they come bundled 60 papers to each bundle, that's 44 bundles and 38 singles.

My sheet looks like this so far:

A
1 2678 (total papers)
2 60 (bundle count)
3 44.6333 (bundles)

I would like it to look like below but can't seem to break out the partial
bundle count (the singles) into another cell.

A
1 2678 (total papers)
2 60 (bundle count)
3 44 (bundles)
4 38 (singles) <~ 60 X .6333 rounded up

Does anyone has a soluotion for this one?

Much obliged,
Michael
Jacksonville, Florida

 

[Guest]

Try this:

A1: 2678
A2: 60
A3: =INT(+A1/A2)
A4: =MOD(+A1/A2,1)*A2

In the above example:
A3 returns 44
A4 returns 38

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

There might be a more elegant way of doing this but this seemed to work:

for the number of bundles: =ROUNDDOWN(A1/A2,0)
for the singles: =A1-A3*60

A1 is the cell with totla numbers
A2 is the cell with papers per bundle
A3 is your rounded down formula from above
A4 is your singles formula

 

[Guest]

Hi

For your bundles, use
=INT(2678/60)
and for your singles use
=2678-(60*bundles)

With your total in A2 and your bundle size in A3 use:
INT(A2/A3) in A4 for bundles and
=A2-(A3*A4) for singles.

Hope this helps.
Andy.

 

[Guest]

Thank you Ron, Tim and Andy for your quick replies!
I have my solution now and I appreciate your help.

Best, Mike

 

[Guest]

Hello,
I've run into a problem I didn't forsee. That is, when the total count of
singles exceeds the bundle size, I don't know what to do. Here's my example
I made from using your formulas:

There are 60 newspapers to a bundle.

A B C D
COUNT ZIP Bundles Singles
2423 32204 40 23
3355 32221 55 55
1250 32234 20 50
115 128
TOTAL

I've ended up with 115 bundles and 128 singles.
The desired result is 117 bundles and 8 singles and I don't know how to make
that happen in cells C6 and D6.

I really appreciate the help, Thanks,
Mike
Jacksonville, Florida

 

[Guest]

It depends on what you're trying to achieve.

If each zip code is a stand-alone route delivered by separate drivers, then
the previously posted formulas are accurate.

BUT...
IF those 3 routes represent 1 driver's activity
AND...you want to calculate how many bundles and singles that driver needs
in total.
THEN...apply the formulas to the total of 7028

You'll get 117 bundles and 8 singles.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Thank you very much Ron,
Yes, muliple zip codes are for 1 driver. Applying the formulas to the
combined total, makes sense now. I'll give it a whirl first thing Monday
moning!
All the best,
Mike Saffer