I need a formula for the below

Options

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)

Tags:

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!