I need a formula for the below
Hi Team,
I'm looking for a formula to tick the RUC Required box if the RUC end KMS are either within 1000km of the Odometer reading or higher. But I also need it to disregard the odometer box and take the HUBO Reading if that cell has a value in it.
I use this so once the form is filled out, the formula is applied and an automation flicks out an email to the required person to remedy.
This is my current formula, but it only works on rows that have a HUBO value not rows with no value.
=IF(AND(AND(NOT(ISBLANK([Hubo Reading]@row)), [Odometer Reading]@row > [RUC End Kms]@row), ([Hubo Reading]@row > [RUC End Kms]@row)), true)
Best Answer

I think you're trying to do too much in a single IF. You might have to nest a second IF with its own AND as the false condition of the first one.
Try this:
=IF(AND(ISBLANK([Hubo Reading]@row), [Odometer Reading]@row > [RUC End Kms]@row), 1, IF(AND([Hubo Reading]@row <> "", [Hubo Reading]@row > [RUC End Kms]@row), 1, 0))
In English: IF Hubo Reading is blank and Odometer Reading is greater than RUD End kms, check the box, otherwise, if Hubo Reading is not blank, and Hubo Reading is greater than RUC End Kms, check the box, otherwise don't check the box.
(fyi  a value of 1 in a checkbox or flag column is the same as true, 0 is the same as false.)
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

I think you're trying to do too much in a single IF. You might have to nest a second IF with its own AND as the false condition of the first one.
Try this:
=IF(AND(ISBLANK([Hubo Reading]@row), [Odometer Reading]@row > [RUC End Kms]@row), 1, IF(AND([Hubo Reading]@row <> "", [Hubo Reading]@row > [RUC End Kms]@row), 1, 0))
In English: IF Hubo Reading is blank and Odometer Reading is greater than RUD End kms, check the box, otherwise, if Hubo Reading is not blank, and Hubo Reading is greater than RUC End Kms, check the box, otherwise don't check the box.
(fyi  a value of 1 in a checkbox or flag column is the same as true, 0 is the same as false.)
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!