Forum Discussion
Excel Formula Help - searching the consecutive hours among weeks and find the exact target date
=IF(MAX(FREQUENCY(IF(C3:AM3>=18,COLUMN(C3:AM3)),
IF(C3:AM3<18,COLUMN(C3:AM3))))>=4,MAX(INDEX($C$1:$AM$1,
SMALL(IF((C3:AM3>=18)*(D3:AN3>=18)*(E3:AO3>=18)*(F3:AP3>=18),
COLUMN($A$1:$AK$1)),1)),B3), "N/A")Hi Brian. You are welcome. The SMALL part of the formula would extend like in above formula. The other parts have to be adapted accordingly.
=IF(MAX(FREQUENCY(IF(C3:K3>=18,COLUMN(C3:K3)),IF(C3:K3<18,COLUMN(C3:K3))))>=4,MAX(INDEX($C$1:$K$1,SMALL(IF((C3:K3>=18)*(D3:L3>=18)*(E3:M3>=18)*(F3:N3>=18),COLUMN($A$1:$I$1)),1)),B3), "N/A")An alternative could be this formula along with a helper range (C1:K1) for the Sundays. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The results are in column O in order to avoid a circular reference.
- Hi Oliver, Thanks for the reply. May I know if I need to keep track to more weeks (e.g. let say I need to view the period til week 52 of 2023. "SMALL(IF((C3:K3>=18)*(D3:L3>=18)*(E3:M3>=18)*(F3:N3>=18)" this part also need to extend? Thankss
=IF(MAX(FREQUENCY(IF(C3:AM3>=18,COLUMN(C3:AM3)),
IF(C3:AM3<18,COLUMN(C3:AM3))))>=4,MAX(INDEX($C$1:$AM$1,
SMALL(IF((C3:AM3>=18)*(D3:AN3>=18)*(E3:AO3>=18)*(F3:AP3>=18),
COLUMN($A$1:$AK$1)),1)),B3), "N/A")Hi Brian. You are welcome. The SMALL part of the formula would extend like in above formula. The other parts have to be adapted accordingly.
