Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting problem - newspapers
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 -- Mike Jacksonville, Florida |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting problem - newspapers
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 "Mike Saffer" wrote: 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 -- Mike Jacksonville, Florida |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting problem - newspapers
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 "Mike Saffer" wrote: 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 -- Mike Jacksonville, Florida |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting problem - newspapers
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. "Mike Saffer" wrote in message ... 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 -- Mike Jacksonville, Florida |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting problem - newspapers
Thank you Ron, Tim and Andy for your quick replies!
I have my solution now and I appreciate your help. Best, Mike -- Mike Jacksonville, Florida "Mike Saffer" wrote: 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 -- Mike Jacksonville, Florida |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting problem - newspapers - follow up Q
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 -- Mike Jacksonville, Florida "Mike Saffer" wrote: 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 -- Mike Jacksonville, Florida |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting problem - newspapers - follow up Q
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 "Mike Saffer" wrote: 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 -- Mike Jacksonville, Florida "Mike Saffer" wrote: 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 -- Mike Jacksonville, Florida |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting problem - newspapers - follow up Q
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 -- Mike Jacksonville, Florida "Ron Coderre" wrote: 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 "Mike Saffer" wrote: 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 -- Mike Jacksonville, Florida "Mike Saffer" wrote: 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 -- Mike Jacksonville, Florida |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Startup Problem | Excel Discussion (Misc queries) | |||
Problem with MS Community Newsgroups? | Excel Discussion (Misc queries) | |||
Counting Problem | Excel Discussion (Misc queries) | |||
Problem With Reference Update | Excel Worksheet Functions | |||
Problem with counting characters in a cell | Excel Worksheet Functions |