Forum Discussion
Production Timeline
In a table representing 52 weeks in columns, I want to enter a quantity of plants to be planted in a given week, 1-52, and in a separate cell, the time it will take for the plants to come to maturity. If i plant the plant in week three of the year, and enter it into the column representing the third week and it takes 10 weeks to come to maturity, I want the same quantity value as the planted quantity to appear in the table in week 13, hopefully in a different color than the original start quantity. I recognize that I may have to use two rows to avoid the conflict between a planted quantity and the mature quantity in the same cell but it would be better if there is a way to show both quantities in the same cell in different colors in the case where plants are being planted and harvested in the same week.
Mike_Byrum I put together the following sheet that I think does what you want. I used this formula:
=IFERROR(1/(1/SUMPRODUCT($B$3:$BA$3*(MOD($B$1:$BA$1+$B$4:$BA$4,52)=B1))),"")to calculate the number of mature plants that week. I added the 1/ 1/ to create an error so cells that would should 0 actually show blank. I could have also just changed to a custom number format but that sometimes confuses people.
I put the mature count above so they can be 'locked' and you could add more below, just felt right to me. I assumed the 52 weeks would roll over because you didn't say otherwise but that can cause issues when you still have plant info from early wks and then plant info in later weeks pointing at the "same week" but actually 2 different years.
So in the image you can see wk 1 has plants maturing in wk 3.
wks 7 & 12 have plants maturing in wk 15
wk 8 has a plant maturing in over a year and wk 19 in 1 wk but it looks like all 17 mature in wk 20
see attached file.
6 Replies
Mike_Byrum I put together the following sheet that I think does what you want. I used this formula:
=IFERROR(1/(1/SUMPRODUCT($B$3:$BA$3*(MOD($B$1:$BA$1+$B$4:$BA$4,52)=B1))),"")to calculate the number of mature plants that week. I added the 1/ 1/ to create an error so cells that would should 0 actually show blank. I could have also just changed to a custom number format but that sometimes confuses people.
I put the mature count above so they can be 'locked' and you could add more below, just felt right to me. I assumed the 52 weeks would roll over because you didn't say otherwise but that can cause issues when you still have plant info from early wks and then plant info in later weeks pointing at the "same week" but actually 2 different years.
So in the image you can see wk 1 has plants maturing in wk 3.
wks 7 & 12 have plants maturing in wk 15
wk 8 has a plant maturing in over a year and wk 19 in 1 wk but it looks like all 17 mature in wk 20
see attached file.
- Thank you mtarler!, This appears to be an excellent solution. I will test it out using some real data and let you know how it goes.
With your permission, if I can recommend. It can help us all if you upload an Excel file (without sensitive data), no picture. Even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases. You could get a precise solution much faster with a file (w/out sensitive data). This would also be a blessing for all of us, as we can understand the problem much better, a win-win situation for everyone.
*Knowledge of Excel version and the operating system is a must have if you want to proposing a reasonable solution (Example: office version e.g. 2016 or 2019 or 365 web or 365 pro, etc) and your operating system (e.g. Win10 (2004), Win 10 (1903), Mac, etc.).
In order not to be misunderstood, in the community it is the aim of excel users to give help on their project (from Excel users), or rather, to provide food for thought and not to deliver finished work or finished ideas.
Thank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)
- Thank you Nikolino,
I appreciate your help and the information you offered. A man who hopes to discover gold needs more than someone to show him the characteristics of a good potential sight for a mine. He also needs someone to show him how to get to the area where the potential mines are located not to mention the tools he will need. Next time I will be sure to take all of the right tools with me, thanks to you:).I am glad that you were able to find a solution with the help of Mr. mtarler
We cannot do great things - only small ones, but those with great love.
Mother Teresa©
Thank you for your patience and time.
Wish you a nice day / night with lots of health, joy and love.
Nikolino
I know I don't know anything (Socrates)
- Thank you mtarler, This appears to be an excellent solution. I will test it out using some real data and let you know how it goes.
