Using MOD and IF to find cell value vs reference and show if divisible but I have an issue

Originally Posted by AliGW

You need to ADD a column to the workbook with your EXPECTED results filled in manually. Please do this NOW.

It is not that simple as the expected answers changed based on the date in cell value B2 as I've mentioned numerous times now. I can look at the static data in Glenn's example (and I have now updated this as required).

Originally Posted by Glenn Kennedy

STILL no expected answers... My last guess without expected answers:

=LET(A,S15:AG15,B,FILTER(A,A<>0),C,--(DROP(B,,1)-DROP(B,,-1)<0),IFERROR(IF(AND(MOD(SUMPRODUCT(--(C=1)),3)=0,SUM(C)>0,SUM(TAKE(B,,-2)*{-1,1})<0),"HT",""),""))

Glenn, I've updated your sample with a column detailing where the expected answers differ from the ones your formula derives. I hope this is what you are expected, but as above, the dynamic aspect here is the value in B2, which will change each week and as such will change the starting point (or ending point) for the calculation I would imagine?

FVF_Sample (1)_CB.xlsx