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...

Formulas and Functions

mtarler
Nov 19, 2020
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.
plants.xlsx11 KB

mtarler

Silver Contributor

Nov 19, 2020

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.

plants.xlsx11 KB