Search results

Great. :)

Thanks, but it would be better to post the formulae rather than a piucture so that members can copy and paste if they wish.

Was this of any help to you?

I think you have Excel 2021. If so, try this: =LET(downtrend,FILTER(C15:D374,D15:D374<=179),uptrend,FILTER(C15:D374,D15:D374>179), direction,IF(H6="Uptrend",uptrend,downtrend),type,IF(H6="Uptrend",1,-1), XLOOKUP(H7,INDEX(direction,,1),INDEX(direction,,2),"",1,type))

Which version of Excel are you using?

Or: =VLOOKUP(H6,$C$14:$D$374,2,0)

If you have 2024 or 365: =TAKE(FILTER(FILTER(Calculator!$D$2:$R$21,(Calculator!$C$2:$C$21=F2)*(Calculator!$B$2:$B$21>=E2)),Calculator!$D$1:$R$1>=G2),1,1)

Which version of Excel are you using?

See posts #2 and #3 for the difference - same results, though. Sorry - I forgot that 2021 does not have VSTACK. Go with this from @p45cal in post #4: =ABS(C6:C366/6-30)-15

Or this: =LET(s,I10,m,K10,x,IF(C6:C186=90,0,-SEQUENCE(181,,m,60/360)), y,IFNA(IF(C187:C366=270,0,SEQUENCE(181,,m,60/360)),s), VSTACK(x,DROP(y,1)))

If you have Excel 2021, 2024 or 365, paste this into E6 and hit ENTER: =LET(x,IF(C6:C186=90,0,-SEQUENCE(181,,-15,60/360)), y,IFNA(IF(C187:C366=270,0,SEQUENCE(181,,-15,60/360)),15), VSTACK(x,DROP(y,1)))

Which version of Excel are you using?

You can LIKE our contributions by clicking on the icons in our posts. :)

This will suffice: =TEXTBEFORE(TEXTAFTER(A1,",",-2),",")

By definition, there cannot be FIVE quarters! Attach a desensitised workbook, please.

Glad to help. :)

In E2 copied down: =IF(C2>D2,((1-C2)+D2)*24,(D2-C2)*24)

This: =IF(--B246-INT(B246)<--A246-INT(A246),(1-MAX(22/24,--A246-INT(--A246)))+MIN(6/24,--B246-INT(--B246)),0) If you have Excel 2021 or newer: =LET(a,--A246-INT(A246),b,--B246-INT(B246),IF(b<a,(1-MAX(22/24,a))+MIN(6/24,b),0))

Is that it? No 'thank you'? OK, well no more help from me - sorry. A lack of common courtesy is a HUGE turn-off.

Not one of those pairs spans midnight!!! But if they did, this would be a belt and braces approach: =IF(--B2<--A2,(1-MAX(22/24,--A2-INT(--A2)))+MIN(6/24,--B2-INT(--B2)),0)

The OP cross-posted (has a thread on ExcelForum) and thought (wrongly) that they needed to dlete their post here.

By the way, if the situation is the other way around, you can use this (see attached): =VLOOKUP(--D15,$O$5:$P$13,2,0) Hopefully between this and post #8 you'll find a way forward. Let me know if you need any further explanation.

I'll try and address the issue you have directly. Using formatting does NOT change the underlying value of a cell; it just changes how it looks. So you need a different approach. In the attached, you will see that I have set up a lookup table with numbers converted to text in L5:M13. In...

Excellent.

Thanks for the like - does this mean that the issue is now resolved?