Request for a formula to return a value based on several conditions

Question

I am new to Smartsheet and wanted assistance in developing a formula based on the below conditions. My attempts thus far is unfortunately returning a error message. If the age of the document is <=730 and the type of the document is Procedure, then return the value of 6 If the age of the document is between 731 and 1440 and the type of the document is Procedure, then return the value of 3 If the age of the document is >1441 and the type of the document is Procedure, then return the value of -6 If the age of the document is <=730 and the type of the document is Work Instruction, then return the value of 3 If the age of the document is between 731 and 1440 and the type of the document is Work Instruction, then return the value of 0 If the age of the document is >1441 and the type of the document is Work Instruction, then return the value of -6 If the age of the document is <=730 and the type of the document is Form, then return the value of 1 If the age of the document is between 731 and 1440 and the type of the document is Form, then return the value of 0 If the age of the document is >1441 and the type of the document is Form, then return the value of -1

Kelly Moore · Accepted Answer

Hey @Newbie Happy to help. Try this formula. I noticed you have a gap between at 1441 - I took a guess at which way you wanted it to work. Test the formula thoroughly and we can tweak wherever is needed. It looks intimidating just because it's long. You'll find I grouped the ANDs and the ORs by what result it produced. =IF(AND([Document Type]@row = "Procedure", [Age of Document (Days)]@row <= 730), 6, IF(OR(AND([Document Type]@row = "Work Instruction", [Age of Document (Days)]@row <= 730), AND([Document Type]@row = "Procedure", [Age of Document (Days)]@row > 730, [Age of Document (Days)]@row < 1441)), 3, IF(OR(AND([Document Type]@row = "Work Instruction", [Age of Document (Days)]@row > 730, [Age of Document (Days)]@row < 1441), AND([Document Type]@row = "Form", [Age of Document (Days)]@row > 730, [Age of Document (Days)]@row < 1441)), 0, IF(AND([Document Type]@row = "Form", [Age of Document (Days)]@row <= 730), 1, IF(AND([Document Type]@row = "Form", [Age of Document (Days)]@row > 1440), -1, IF(AND(OR([Document Type]@row = "Procedure", [Document Type]@row = "Work Instruction"), [Age of Document (Days)]@row > 1440), -6)))))) If you're interested, you can find information on IF/AND or IF/OR below cheers, Kelly https://help.smartsheet.com/function/and https://help.smartsheet.com/function/or