Level 2 Sprint 3 - Confirmed PO Delivery Formula; POST Function
Hello dear Community members, I have used the following formula for 'Confirmed PO Delivery':
IF PO Submitted > 0 THEN POST(PO Submitted, Shipping Time Weeks) ELSE 0
However, I am not getting the desired result. For example, I checked the box for 'Submit Purchase Order Request' for Week 1, so the 'PO Submitted' cell value changed to 1. But the value for 'Confirmed PO Delivery' shows 0 in Week 3 [where instead according to the POST formula as mentioned above, it should have posted 1 (from Week 1 'PO Submitted')], as the corresponding 'Shipping Time Weeks' for Week 1 is 2 (weeks, that is, the target is Week 3):
I tried to understand the issue, then, I noticed this (Case 2):
In my IF THEN formula (from above), I had written that 'IF PO Submitted > 0' then (do the calculation) else return 0. So, when the system, I believe, read the cell value for 'PO Submitted' for Week 3 (which was not marked with a check, so it would have been 0) in Case 1, it would have gone to the ELSE part of the function to overwrite the initial value in 'Confirmed PO Delivery' (which would have been 1, as posted from Week 1, using the POST formula). To test this, I also checked the 'Submit Purchase Order Request' box for Week 3 (Case 2) and the 1 appeared in 'Confirmed PO Delivery' for Week 3. I think this is because the system read that 'PO Submitted' is 1, so it POSTs a value to another cell (as per THEN part of the formula) and leaves the current cell as it is, without overwriting it.
I would like to know how this issue can be overcome. I would like to know if there is any mistake in my approach. I think the ELSE part should have something that can keep the value as it is instead of 0, that is, to keep it as 1 if it was initially 1, but I am not sure how. Am I missing something obvious?
I think one obvious way would be to just write the formula for 'Confirmed PO Delivery' as:
POST(PO Submitted, Shipping Time Weeks)
(This formula works well and seems logical, but might have performance issues, so I am not really sure).