Forum Discussion
Solved
Offset & If Funtions
Hi all, I'm looking to offset a cell so I know when to order a certain amount of stock (order policy). This depends on a specific lead time and when MPS Quantity = Order policy. I need any cell in the MPS start row to offset left by the value of the lead time. I.e. If order policy =150, lead time = 1, and MPS Quantity =150 in week 2, then MPS Start =150 in week 1 or if Order Policy = 150, lead time = 2 and MPS Quantity = 150 in week 3, then MPS Start = 150 in week 1.
I know it involves an IF and OFFSET funtion but ive tried several combinations and have found myself scratching my brain at the logic. Any help would be greatly appreciated!
=BYCOL(E11:L11,LAMBDA(col,IF(OFFSET(col,0,L4,1,1)=L3,L3,0)))You can try this formula which seems to return the intended result in my sheet.
For example lead time 1week:
Or lead time 2 weeks:
=BYCOL(E11:L11,LAMBDA(col,IF(OFFSET(col,0,L4,1,1)=L3,L3,0)))You can try this formula which seems to return the intended result in my sheet.
For example lead time 1week:
Or lead time 2 weeks:
The SPILL error means that the spill range isn't blank. You can read here how to handle this error.
How to correct a #SPILL! error - Microsoft Support
In the practical example you can delete all entries in range E12:L12 and enter the suggested formula in cell E12.
=BYCOL(E11:L11,LAMBDA(col,IF(OFFSET(col,0,L4,1,1)=L3,L3,0)))In your sheet the formula is entered with "-L4" instead of "L4".
