Search results

In AR3 copied across and down: =LET(s,SUMPRODUCT(($A$2:$A$49=$AQ3)*($AN$2:$AN$49=AR$2)),IF(s=0,"",s)) =LET(s;MATR.SOMMA.PRODOTTO(($A$2:$A$49=$AQ3)*($AN$2:$AN$49=AR$2));SE(s=0;"";s))

You also have IFNA, so you an provide a fallback: =IFNA($K$10:$K$16,"")

Because you are testing for text, not an error. Try this: =ISNA($K$10:$K$16) The hashtag followed by N/A will be seen by Excel as an error message, not regular text. Same with #VALUE!, for which you could use =ISERROR($K$10:$K$16)

Here's a SPILL version: =MAP(E9:E16,F9:F16,LAMBDA(e,f,XLOOKUP(f&G$8,'Raw Data'!A2:A14,'Raw Data'!D2:D14,LET(u,UNIQUE(FILTER('Raw Data'!D2:D14,(NOT(ISNUMBER(--'Raw Data'!D2:D14))*(LEFT('Raw Data'!A2:A14,LEN(e))=e)))),IF(ROWS(u)=1,u,"Multiple"))))) The attached contains a breakdown of how the...

The double unury — just forces an array to return numbers and not text. Shout if you want a fuller explanation and I’ll provide it tomorrow.

Try this: =XLOOKUP(F9&G$8,'Raw Data'!$A$2:$A$14,'Raw Data'!$D$2:$D$14,LET(u,UNIQUE(FILTER('Raw Data'!$D$2:$D$14,(NOT(ISNUMBER(--'Raw Data'!$D$2:$D$14))*(LEFT('Raw Data'!$A$2:$A$14,LEN(E9))=E9)))),IF(ROWS(u)=1,u,"Multiple")))

There's no match for this in the Raw Data: 32026DELIVER TO STATE 1 In the Raw Data, you have things like this: 32026001DELIVER TO STATE 1 No match will result in an error.

OK - try this: =IF(OR(LEN(A2)<LEN(A1),A1="Project ID"),"Top Level","")

Try this: =IF(AND(LEN(A2)<>LEN(A1),B2<=B1),"Top Level","")

The problem is that errors can easily happen when copy typing a formula - by copying and pasting it means that no errors are made. And it's actually easier for you just to copy and paste formulae into your post instead of making screenshots, so it's a win-win. Similarly, it's actually easier for...

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.