Mar 21 2021 11:58 AM - edited Mar 21 2021 12:01 PM
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!
Mar 21 2021 01:43 PM
Mar 21 2021 06:33 PM
@mathetes Of course my apologies. Attached is the workbook containing the sheets. Thanks for your response!
Mar 22 2021 09:14 AM
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.
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.
Mar 22 2021 11:12 AM - edited Mar 22 2021 11:14 AM
@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
Mar 22 2021 12:28 PM
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.
Mar 22 2021 01:26 PM
Mar 23 2021 07:57 AM
Solution
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.
Mar 23 2021 09:49 AM
Mar 23 2021 10:06 AM - edited Mar 23 2021 10:13 AM
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.