I need a formula for the below

Ridley_HK

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)

Jeff Reisman

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.)

Jeff Reisman

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.)