SOLVED

Need help figuring an Excel formula for order fulfillment

Occasional Contributor

Please see attached images of the 2 spreadsheets containing data..

 

Essentially what I want to do is calculate in row "T" how many trays total needed to fulfill the orders placed by customers, per crop.

 

On the "Crop Data" sheet you can see the average yield per tray of Amaranth, Red Garnet is 500g, and 450g for Crop 2.

It is not on the spreadsheet but:

Small(S) contains 100g of crop

Medium(M) contains 150g of crop

Large(L) contains 200g of crop

 

Assuming more customers were added and ordered perhaps the same crops or different, my goal is to calculate how many total trays per crop ordered I will need to produce to fulfill the weights required. 

 

I thought perhaps I would need to give each size (S, M, L) their respective values(100, 150, 200) somehow and multiply them by the "Qty". I am just unsure how to formulate that. Especially to calculate each crop individually like I would prefer.  

 

Hopefully I have provided enough information for those trying to help. More info if needed.

Thanks in advance!

9 Replies
An image is helpful to a very limited extent. Can you post a copy of the actual spreadsheet? Otherwise you're, in effect, asking us to recreate one from the image...

@mathetes Of course my apologies. Attached is the workbook containing the sheets. Thanks for your response!

@coltenpratt 

 

I've made a start, but came up against my own ignorance. So let me tell you what I have done, and then pose the question about what isn't at all clear to me.

 

  1. I radically changed your "Crops" sheet--don't worry, I left the original untouched. This is essentially a database, however, and the conventional way to do databases is with the "entities" down the vertical axis, and the "attributes" of those entities across the horizontal. You'd done it the other way around. Either way is workable, but I'd just point out that doing it the conventional way allows for a large number of entities to be included and still have the table be intelligible. I don't know how many different crop categories you're going to be working with, but I seriously recommend keeping my Crops2 table going. (And now that it's an official table, you can add rows and they'll automatically be included in any LOOKUP or INDEX/MATCH formulas).
  2. I added the Small/Medium/Large tray sizes to that table. Assuming the numbers will vary for different crops, this will enable the various values to be recognized by any formulas that refer to them.

 

But now we come to what was for me a block: I started to fill in the "Orders" sheet, but very quickly realized there are headings there that don't refer (at least not that I could tell) to data that's in the Crops table. Such things as Type and Price, for example. They need to be somewhere so they can be looked up.

 

And then, finally, that Column for "Trays," your presenting request for help, I realized I had no idea how to connect it mathematically with the yield data in the table. So I'm asking you for clarification:

Do I see that Jerry is ordering M (medium) size, and that Amaranth tray yield is 500, so does that mean 3 1/3 medium trays? You see the problem.  You've got tray sizes as a measurement in two different ways: you know what you mean, but it's confusing and I don't want to create a formula based on crazy assumptions.

 

Going by your own use of INDEX and MATCH, it would appear to me that you'd be able to resolve this on your own, but if not, come back with clarification on my puzzlement.

@mathetes Ah, this seems to be the right track for sure! I very much appreciate your help in this matter. I would like to clarify though as I'm still not quite sure how to formulate the "Trays" problem..

 

But first note I have added a "ddl" page updating a few of the cells with drop down menus such as the "Type" as you've previously mentioned, as well as the "Size". These are planned to be used to calculate the "Price" in row J with the respective price for Retail, Wholesale, or Restaurant chef clients. That is my plan once I finish the Excel sheet for calculating the price for each. Apologies for a half finished workbook.

 

As for calculating the "Trays"...

If you view the "Crops2" page you created there is a header in the table that says "Tray Size", both crops are currently documenting the 500g average yield from being grown in a 10"x20"x1.25" tray, NOT a medium tray.

The S, M, L are the size containers in which I will be selling my harvested crop in.

 

Going back to Jerry, he is ordering 2 Medium containers of ARG containing 150g, and 3 Small containers of C2 containing 100g. You can see that I would only need 1 single tray of each crop to fulfil the orders as each 10"x20"x1.25" tray yields enough product for multiple containers.

 

Example: If ARG average yield per tray is 500g, I can get 5 Smalls at 100g each,  3 Mediums at 150g each and 2 Larges at 200g each. 

 

You can see however as more orders come in for a certain crop it may require more than a single tray. If someone, or multiple people ordered a combined total of 7 Small ARG totalling 700g of ARG needed, I would like the "Trays" row to round up to "2" because it would require 2 full trays rather than 1 full tray and 20% of another.

 

Hopefully this clears up any confusion

Thanks again

@coltenpratt 

 

OK we're moving in the right direction. BUT I don't think you want it to round on each row (although that's the way this revision is going now).

 

I think we need a more complete description of how this Order sheet is going to be used. With it be Tom AND Jerry ordering, along with Tom2, **bleep** and Harry, etc. or might you have on any given morning, enough orders so that there are several of the Order sheets filled out, and you want to be efficient with the Whole set of orders?

 

I'm assuming the latter, which really means a separate "Dashboard" that gives the instructions for how many full "growing trays" (I'm going to call them) you need to pull in order to fill the orders for the "selling trays"

 

So flesh out the work flow here, if you would. The formula per se is pretty easy; it's still a matter of clarifying what is at the front end.

@mathetes So the plan once the formulas were figured out, I was going to add more columns downward for more capacity of orders. Then copy the whole chart down for a total of 52. Hence the “1” in the top left cell. The copied graph below will be “2”, then “3” and so on. This giving me the dates of each Monday of the year in the long cell at the top. As Mondays will be my harvest day.

In short meaning the order sheet being worked on is for just 1 of 52 weeks of the year.

As I’m creating this for a small business I’m just starting, each graph will not have to accommodate a mass of space for orders.

Hope this helps!
best response confirmed by coltenpratt (Occasional Contributor)
Solution

@coltenpratt 

 

I hope you have the most recent version of Excel. I use the recently introduced function UNIQUE in this version. Use it twice, first to produce a summary of the dollars of each customer's order; second to produce a list of the products involved in all the orders, from that the total product quantity, and from that the total number of "growing trays" needed.

 

I added three columns (by the way, you have used the word "column" where the real word is "row" ...a vertical array of cells is a column, a horizontal array is a row)...I added three columns just to make clear the progression. I'm sure it'd be possible to consolidate all those into a single formula, but that kind of "behind the scenes magic" isn't clear to you, the user. This is therefore more maintainable by you.

 

Let me know if this works.

 

Perfect! Everything works great. I'm glad you were able to solve this as I would likely never have achieved it, as I'm sure you've noticed I'm quite new to using excel haha. As well as I noticed to calculate the total trays needed you use the LET function which never would have crossed my mind.

I thank you very much for taking the time to help me solve this.
cheers

Good, I'm glad it worked for you. It was fun to do.

I just went back and looked at that LET formula and realized in looking closer that it does nothing of value. This simpler formula does the exact same thing.

 

=IFERROR(VLOOKUP(R6,CropData,2,0),"")

 

So switch it out.

 

LET is fun to use, but I got carried away. It only would have made sense had I needed the VLOOKUP to happen several times.