Forum Discussion
Calculated planting week
Hi all,
I grow plants for a living and have a spreadsheet with different plants in the rows, columns represent week 1 to 52 and each plant has quantities entered when we want to have them ready.
A second tab has the again plant names in the rows and data in the columns for how long it takes for the plants to grow.
I am looking for the best solution to combine both sets of data and create a list of when plants should be planted.
e.g. we want to have 500 Snapdragon ready in week 12, to get them ready in this week it takes 8 weeks to grow them so in week 4 we need to plant 500.
Up to 50 different plants and growing time changes throughout the year where products are much faster in summer than they are in winter.
It can happen that with the above Snapdragon example we want another 500 in week 13 and have a growing time of 9 weeks, in this case we need to plant 1,000 in week 4.
I have been able to create a file but the crude formulas I used don't hold up when a new product line is added unfortunately.
Appreciate anyone willing to put suggestions forward.
Regards,
6 Replies
Updates made. New workbook attached.
Lambda - 'Summarize' =LAMBDA(a,v,LET( sched, XLOOKUP(v, ProductFC & " - " & VarietyFC, SalesFC), Required, TOCOL(FILTER(sched, sched <> 0)), c, FILTER(COLUMN(sched), sched <> 0) - 3, ctimes, CHOOSECOLS(XLOOKUP(v, ProductCT & " - " & VarietyCT, CropTime), c), PlantWk, TOCOL(c - ctimes), r, ROWS(Required), VSTACK(a, HSTACK(EXPAND(v, r, , v), PlantWk, Required)) ))=REDUCE(Header,ProductFC&" - "&VarietyFC,Summarize)Thank you for your reply.
This goes beyond what I fully understand but with the very helpful excel example you gave I can learn a lot.
I need a solution that allows for changing crop growing times throughout the year though which makes it a lot more complicated again.
To clarify I have attached the data with quantity for each plant and when they should be ready and then the time it takes to get them ready. The combination of these two sets of data should allow for us to create a planting schedule.
- Thank you for the file. I've updated my previous post with a new workbook. Let me know what you think about the results sheet. The 'Summarize' function I built is at a point where it can be adjusted as needed to change the display.
My knowledge about planting is very limited, even if I like to eat vegetables :).
Here is some information I was able to put together that may help you.
- You can use Excel formulas to calculate the planting dates based on your last frost date and the growing time of each plant. For example, one website suggests using this formula: = CELL ID of last frost date + CELL ID planting (weeks) * 7
- You can also use a template that has already been created for seed starting and planting schedules. For example, one website offers a downloadable template that you can adjust according to your location and crops.
- You can also use online tools and calculators that are designed for garden planning. For example, one website provides a fall-harvest planting calculator workbook that works backward from your first fall frost date to determine the best seeding date for each crop.
- You can also use a garden planning spreadsheet that allows you to track all your gardening activities. For example, one website shares a garden planning spreadsheet that includes a seed/plant tracker, a garden map, and notes for plant care and companion planting.
Or lastly, you create your own plan as you wish and you can always come back to this forum for whatever steps you get stuck on.
Otherwise, if you are not sure yourself with Excel, I recommend giving this to commission work. There are enough users who could possibly take over this (not me please, I don't generally take on commissioned work :).
I hope this information are helpful for you.
Thank you so much for your reply.
I have a lot to learn still about Excel and every little bit of information brings me closer to the answers I am looking for.
Would love to pass this to someone willing to take it on as commisioned work but have tried this in the past and have had very dissapointing results as I didn't understand Excel well enough to explain what I wanted and the excel experts didn't understand the desired outcome well enough.
Therefor trying to further educate myself.
Your suggestions are great but I need a solution that allows for changing crop growing times throughout the year with multiple planting dates for the same crop so we have continues flowering over a much larger period of time.
You can combine data from two sheets in Excel by using the Consolidate feature.
You can also create a dynamic list in Excel based on criteria by using the FILTER and OFFSET functions.
To create a list of when plants should be planted, you can use Excel formulas to calculate the planting dates based on your last frost date and the growing time of each plant. You can also use a template that has already been created from Mr. Patrick2788 for seed starting and planting schedules.
To create a dynamic list of when plants should be planted, you can use INDEX - MATCH with other functions. This will allow you to create a list that changes as you add new data to your spreadsheet.
Furthermore, if I may recommend, insert a file (without sensitive data) and explain your project step by step on the basis of this file. At the same time, it would be an advantage if the digital environment was known, such as Excel version, operating system, storage medium, etc..
Thank you for your patience and understanding
