Forum Discussion
Need help figuring an Excel formula for order fulfillment
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!
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.
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!
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.
- 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).
- 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.
