Excel formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2616935%22%20slang%3D%22en-US%22%3EExcel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2616935%22%20slang%3D%22en-US%22%3EHi%3CBR%20%2F%3EI%20want%20to%20thank%20the%20excel%20community%20for%20helping%20me%20try%20to%20understand%20complex%20formulas.%20I%20have%20another%20formula%20and%20may%20have%20a%20few%20more%20in%20the%20upcoming%20weeks.%3CBR%20%2F%3E%3DIF(AND(N3%3D1%2CP3%3D0)%2CIF(AND(R3%26gt%3B%3DE3-90%2CR3%26lt%3B%3DF3)%2CR3)%2CIF(AND(R3%26gt%3B%3DE3%2CR3%26lt%3B%3DF3)%2CR3%2CIF(AJ3%3D1%2CR3%2C%22NA)))%3CBR%20%2F%3E%3CBR%20%2F%3EAm%20I%20correct%20in%20saying%20that%20this%20is%20how%20the%20formula%20is%20read%20.%20IF%20N3%3D1%20AND%20P3%3D0%20AND%20R3%26gt%3B%3DE3-90%2CR3%26lt%3B%3DF3%20then%20value%20is%20R3.%20If%20that%20criteria%20is%20not%20met%20it%20looks%20to%20see%20IF%20R3%26gt%3B%3DE3%2CR3%26lt%3B%3DF3.%20If%20that%20criteria%20is%20met%20its%20R3.%20If%20not%20met%20it%20will%20look%20for%20IF%20AJ3%3D1%20if%20criteria%20is%20met%20R3%2C%20if%20not%20NA%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2616935%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-2617009%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2617009%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1041458%22%20target%3D%22_blank%22%3E%40Mel4342%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20don't%20think%20the%20formula%20is%20correct.%20There%20is%20no%20%22%20after%20NA%2C%20and%20the%20formula%20returns%20FALSE%20in%20several%20situations.%20Perhaps%20it%20should%20be%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(AND(N3%3D1%2CP3%3D0)%2CIF(AND(R3%26gt%3B%3DE3-90%2CR3%26lt%3B%3DF3)%2CR3%2CIF(AND(R3%26gt%3B%3DE3%2CR3%26lt%3B%3DF3)%2CR3%2CIF(AJ3%3D1%2CR3%2C%22NA%22)))%2C%22NA%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ebut%20of%20course%20I%20don't%20know%20what%20the%20person%20who%20wrote%20it%20intended.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2617017%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2617017%22%20slang%3D%22en-US%22%3EBut%20was%20my%20translation%20of%20the%20original%20formula%20correct%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2617001%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2617001%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1041458%22%20target%3D%22_blank%22%3E%40Mel4342%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%E2%80%99re%20looking%20to%20ask%20a%20question%20or%20start%20a%20conversation%20about%20Excel%2C%20you%E2%80%99re%20in%20the%20right%20place!%3C%2FP%3E%3CP%3EPlease%20include%20the%20following%20info%20to%20help%20others%20answer%20your%20question%3A%20%3CSTRONG%3EClick%20on%20the%20Link%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fwelcome-to-your-excel-discussion-space%2Fm-p%2F2204395%22%20target%3D%22_blank%22%3EWelcome%20to%20your%20Excel%20discussion%20space!%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor
Hi
I want to thank the excel community for helping me try to understand complex formulas. I have another formula and may have a few more in the upcoming weeks.
=IF(AND(N3=1,P3=0),IF(AND(R3>=E3-90,R3<=F3),R3),IF(AND(R3>=E3,R3<=F3),R3,IF(AJ3=1,R3,"NA)))

Am I correct in saying that this is how the formula is read . IF N3=1 AND P3=0 AND R3>=E3-90,R3<=F3 then value is R3. If that criteria is not met it looks to see IF R3>=E3,R3<=F3. If that criteria is met its R3. If not met it will look for IF AJ3=1 if criteria is met R3, if not NA
4 Replies

@Mel4342 

If you’re looking to ask a question or start a conversation about Excel, you’re in the right place!

Please include the following info to help others answer your question: Click on the Link
Welcome to your Excel discussion space!

 

Thank you for your understanding and patience

 

Nikolino

I know I don't know anything (Socrates)

@Mel4342 

I don't think the formula is correct. There is no " after NA, and the formula returns FALSE in several situations. Perhaps it should be

 

=IF(AND(N3=1,P3=0),IF(AND(R3>=E3-90,R3<=F3),R3,IF(AND(R3>=E3,R3<=F3),R3,IF(AJ3=1,R3,"NA"))),"NA")

 

but of course I don't know what the person who wrote it intended.

But was my translation of the original formula correct?
With the exception of the missing " on the NA which would cause errors I believe your interpretation of the formula to be correct. So the following might be an alternative:
=IF(OR( AND(N3=1,P3=0,R3>=E3-90,R3<=F3),AND(R3>=E3,R3<=F3),AJ3=1),R3,"NA")