SOLVED

AR Report

%3CLINGO-SUB%20id%3D%22lingo-sub-1834969%22%20slang%3D%22en-US%22%3EAR%20Report%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1834969%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETrying%20to%20add%20another%20rule%20to%20this%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(TODAY()-B2%26lt%3B%3D30%2C%220-30%20days%22%2CIF(TODAY()-B2%26lt%3B%3D60%2C%2231-60%20days%22%2CIF(TODAY()-B2%26lt%3B%3D90%2C%2261-90%20days%22%2C%22over%2090%20days%22)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20additional%20string%20would%20be%20to%20basically%20cancel%2Fdont%20calculate%20if%20in%20cell%20K2%20the%20word%20%3A%20%22paid%22%20or%20%22applied%22%20or%20%22void%22%20is%20there.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JessEpitaph_2020_0-1604002599294.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F230238i274F161A0963A708%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JessEpitaph_2020_0-1604002599294.png%22%20alt%3D%22JessEpitaph_2020_0-1604002599294.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1834969%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-1835362%22%20slang%3D%22en-US%22%3ERe%3A%20AR%20Report%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1835362%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F850992%22%20target%3D%22_blank%22%3E%40850992%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELike%20this%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(OR(K2%3D%7B%22applied%22%2C%22paid%22%2C%22void%22%7D)%2C%22%22%2CLOOKUP(TODAY()-B2%2C%7B-1000%3B31%3B61%3B91%7D%2C%7B%220-30%20days%22%2C%2231-60%20days%22%2C%2261-90%20days%22%2C%22over%2090%20days%22%7D))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1836820%22%20slang%3D%22en-US%22%3ERe%3A%20AR%20Report%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1836820%22%20slang%3D%22en-US%22%3EThank%20you!%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello!

 

Trying to add another rule to this formula:

 

=IF(TODAY()-B2<=30,"0-30 days",IF(TODAY()-B2<=60,"31-60 days",IF(TODAY()-B2<=90,"61-90 days","over 90 days")))

 

The additional string would be to basically cancel/dont calculate if in cell K2 the word : "paid" or "applied" or "void" is there.

 

 

JessEpitaph_2020_0-1604002599294.png

 

 

 

2 Replies
Highlighted
Best Response confirmed by 850992 (New Contributor)
Solution

@850992 

Like this:

=IF(OR(K2={"applied","paid","void"}),"",LOOKUP(TODAY()-B2,{-1000;31;61;91},{"0-30 days","31-60 days","61-90 days","over 90 days"}))
Highlighted
Thank you!