IF(AND(NOT(ISBLANK Multiple Conditions?
Hello all,
I'm having trouble nesting the IF(AND(NOT(ISBLANK formula with multiple "ANDS"
If the Provisional Requested Amount column is NOT Blank AND the Provisional Requested Amount is less than or equal to the Final Requested amount column AND the Provisional Requested Amount is over 50000, I want the cell to say approved...
=IF(AND(NOT(ISBLANK([Provisional Requested Amount]@row)), [Provisional Requested Amount]@row <= [Final Requested Amount]@row), "Final Approval", IF([Provisional Requested Amount]@row <= 50000, "Final Approval"))
Thanks in advance.
Best Answer

@corilowenstein You're very close. From the logic you describe, I don't see why you have a second IF in there. You can have more than two criteria inside an AND statement for IF. Also your operator sign for greater than or equal to 50000 is reversed. Let's fix these into one AND statement in your IF.
=IF(AND(NOT(ISBLANK([Provisional Requested Amount]@row)), [Provisional Requested Amount]@row <= [Final Requested Amount]@row, [Provisional Requested Amount]@row >= 50000), "Final Approval")
Question: If your Provisional Requested Amount is not blank, will it always be a numeric/currency value? If so, you can use ISNUMBER instead of the NOT(ISBLANK):
=IF(AND(ISNUMBER([Provisional Requested Amount]@row), [Provisional Requested Amount]@row <= [Final Requested Amount]@row, [Provisional Requested Amount]@row >= 50000), "Final Approval")
English: If there's a numeric value in Provisional Requested Amount, and that's less than the Final Requested Amount, and it's greater than or equal to 50000, set this cell to "Final Approval"; otherwise, do nothing.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers

@corilowenstein You're very close. From the logic you describe, I don't see why you have a second IF in there. You can have more than two criteria inside an AND statement for IF. Also your operator sign for greater than or equal to 50000 is reversed. Let's fix these into one AND statement in your IF.
=IF(AND(NOT(ISBLANK([Provisional Requested Amount]@row)), [Provisional Requested Amount]@row <= [Final Requested Amount]@row, [Provisional Requested Amount]@row >= 50000), "Final Approval")
Question: If your Provisional Requested Amount is not blank, will it always be a numeric/currency value? If so, you can use ISNUMBER instead of the NOT(ISBLANK):
=IF(AND(ISNUMBER([Provisional Requested Amount]@row), [Provisional Requested Amount]@row <= [Final Requested Amount]@row, [Provisional Requested Amount]@row >= 50000), "Final Approval")
English: If there's a numeric value in Provisional Requested Amount, and that's less than the Final Requested Amount, and it's greater than or equal to 50000, set this cell to "Final Approval"; otherwise, do nothing.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
Check out the Formula Handbook template!