SOLVED

Dynamic array formulas and Logic Functions

%3CLINGO-SUB%20id%3D%22lingo-sub-2424050%22%20slang%3D%22en-US%22%3EDynamic%20array%20formulas%20and%20Logic%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2424050%22%20slang%3D%22en-US%22%3E%3CP%3EI%20just%20turned%20my%20legacy%20Array%20Formulae%20into%20Dynamic%20Array%20Formulae.%3C%2FP%3E%3CP%3EI%20want%20to%20use%3A%26nbsp%3B%3DIF(ISBLANK(B2%3AB2000)%2C%22%22%2CAND(AL2%3AAL2000%3D%22Not%20Received%22%2CAK2%3AAK2000%26gt%3B60))%3C%2FP%3E%3CP%3Ebut%20realized%20that%20the%20AND%20function%20wants%20to%20test%20for%20every%20cell%20in%20the%20array%20having%20a%20value%20greater%20than%2060%2C%20not%20just%20the%20individual%20cells.%20How%20do%20I%20refer%20to%20the%20individual%20cells%20when%20using%20the%20Dynamic%20Array%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2424050%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2425743%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20array%20formulas%20and%20Logic%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2425743%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1073227%22%20target%3D%22_blank%22%3E%40IanBradley%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(ISBLANK(B2%3AB2000)%2C%22%22%2C(AL2%3AAL2000%3D%22Not%20Received%22)*(AK2%3AAK2000%26gt%3B60))%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I just turned my legacy Array Formulae into Dynamic Array Formulae.

I want to use: =IF(ISBLANK(B2:B2000),"",AND(AL2:AL2000="Not Received",AK2:AK2000>60))

but realized that the AND function wants to test for every cell in the array having a value greater than 60, not just the individual cells. How do I refer to the individual cells when using the Dynamic Array?

2 Replies
best response confirmed by IanBradley (New Contributor)
Solution

@IanBradley 

Try

 

=IF(ISBLANK(B2:B2000),"",(AL2:AL2000="Not Received")*(AK2:AK2000>60))

Thanks, good idea! I wanted the result to express as true/false so modified slightly but this worked:
=IF(ISBLANK(B2:B2000),"",IF((AL2:AL2000="Not Received")*(AK2:AK2000>60),TRUE,FALSE))