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

Options

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

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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


Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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


  • Newbie
    Newbie ✭✭✭
    Options

    Thank you Kelly for taking the time to answer my question. The above formula worked exactly how I wanted it to.

    I also now know were I went wrong in my earlier attempts. I did not use the AND and OR function wisely. Lesson learnt.

    Thank you

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!