• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculating Manpower on the basis of filled up delivery boxes

Status
Not open for further replies.
S

Santanu Chatterjee

Member
I am trying to build a calculator for calculating the manpower required for my company who does last mile deliveries at different states. As and when the customer orders come in, the orders are segregated basis out delivery centers at different cities where the last mile need to happen. What I am trying to implement is that if at the point of segregation of the orders, if the "Packaging Box Type", "Packaging Type" & the number of boxes required each order is put in, the total surface volume that should go per delivery box can be calculated, and the required manpower can also be calculated. This much, I am able to do but I would also like a flag to show up against each record where it will check for the "Packaging Type" (Cold Chain or Normal mentioned in the data attached) and see if the total surface volume till the current cell id within the capacity of the concerned delivery box and populate number 1 till the time the volume is fitting into one delivery box and number 2 if a second box will be required...etc.

I have attached the file with details of what is required. Could you please help me out?

Regards,
Santanu Chatterjee
 

Attachments

  • problem-statement.xlsx
    13.2 KB · Views: 6
Santanu Chatterjee
Based Your I have attached the file with details of what is required.
Were You all values valid (G-column)?
... I got some of those something else. I've solved those - Your were fixed values for some reason.
Please, next time You should show expected results too.
You've written something about riders ... where are those in Your file?
H-column shows my sample results based Your given above blue text.
 

Attachments

  • problem-statement.xlsx
    14.2 KB · Views: 2
Hi Vletm,

Thanks for your guidance. The values in the G column are actually the surface volumes calculated according to the Box Types mentioned in the column D. If you look at Cell F5, the quantity of Type B boxes as mentioned is 5 and if you look up the table J3:N5, you will find that in the column N the surface volumes of all the packaging boxes of the "cold chain" are listed. Thus, in column G, the values will vary according to the quantity of the boxes mentioned, as the Quantity of boxes are multiplied by the respective volumes mentioned in column N.

Secondly, Number of riders are equivalent to the number of full delivery boxes that is decided by summing up the surface volumes of the packaging boxes in the "Normal" or "Cold Chain" categories and checking it with the total permitted volume of the two categories mentioned in the column S & T.

I guess the values in the cells H4 should be 1 as it is in H12 where the "cold chain" box quantities are Zero. Attaching the file with expected results for your reference.

Regards,
Santanu Chatterjee
 

Attachments

  • problem-statement-solved.xlsx
    14.2 KB · Views: 5
Santanu Chatterjee
About Your The values in the G column are actually the surface volumes
... There are difference between Your original fixed values and mine values.
... That's why I tried to ask You about that ( Sentence which starts with word Were ... )

How You write that You're guessing one expect value (H4)?
You should have clear rules instead of guessing.
There seems to be only one value (H4) which is different than my H-column calculated values.
For me, it will be 0 (zero) because there are none Cold Chain Packaging Types which has Box QTY over 0 (zero).
The 1st Cold Chain is in next row and row 12 value is 1 (one),
because there would use totally 1 (one) that kind of number of Packing based S/T-column.
 
Dear Vletm,

Thanks for your guidance. Let me understand the formula and logic you have used. If I will be having any thing more to understand, I will seek your help.

Regards,
Santanu
 
Dear Vletm,

I have meticulously reframed the requirement for your proper understanding. Mentioning below for your better understanding.

Background:
1. In cell range K2:N6 are mentioned the dimensions of some packing boxes.
2. In Column o, the volume for each box is mentioned by multiplying LxBxH.
3. The packing boxes either need to fit into a bigger box of category "cold chain" or "normal".
4. The volume of the bigger boxes (in which the smaller boxes would go in) are mentioned in the column P &Q according to categories. Same depicted in the data in Col: F
5. In the column A - I are mentioned some invoice numbers, corresponding with customer names, Item Names
6. Againt items are mentioned in which type of box they should be packed (Col: E)
7. Against each box, the category of the bigger box is also mentioned
8. In column "G" is mentioned the quantity of boxes where the items will be packed
9. Column H shows the total volume by multiplying Quantity of boxes with the volume of each type of box

What is required is:

1. The formula should check for the "Packaging Type" (Col F) accordingly sum up the volumes projected in Col: H
2. Second criteria is that once the volume of the particular packaging Type (Cold Chain/Normal) is achieved by summing the volumes in Col:H, all the corresponding cells in Col:I should be populated by a serial number ( here it is shown as 1,2,3....). You may take it as a "Units" which comprises of a collection of boxes.
3. Both "Packaging Types" (Cold Chain/Normal) mentioned in Col: E cannot be in the same unit.
4. If the Packaging Type is "Cold Chain" and falls into the 1st unit of its type, then the Col : I should populate 1 and the same should with "Normal" ...and the series should go on
 

Attachments

  • problem-statement-solved.xlsx
    17.1 KB · Views: 5
Santanu Chatterjee
... for Your something ...
Have You read my replies?
Have You checked my sample solution?
You seems to write same texts again and again
... that won't help even Your understanding.

As I wrote 28th Dec (#6 reply)
- You seems to need only those two values.
 
Dear Vletm,

I have been working with the formula you had set. Tried to improvise on that within my limitation but could not succeed which is why wrote to you again. May be I failed to explain you the requirement vividly last time which is why I again wrote to you. In the last message that you have written, you mentioned to check for the cells marked in yellow. I checked them and found that you had created two categories considering "Cold Chain" & "Normal" and tagged them as 1 & 2. But the flag that I am talking about to be populated in column H are unit numbers, which is not limited to 1 & 2 only. One unit of Cold Chain Packaging Type might have several boxes but limited to the total volume of 1 unit i.e 2592. Cold chain might have unit numbers 1,2,3,4,5.... so on and Normal can also have unit numbers as 1,2,3,4,5..... so on. Cold Chain Unit-1 is not equal to Normal Unit-1. Both the packaging types cannot go into a single unit.

I would request you to go through the writeup and the excel sheet once. I think I have properly defined it this time.

Regards,
Santanu
 
Status
Not open for further replies.
Back
Top