Changing Value Prefilled Cell Only if Matching Value

Jimrny · 05/20/22

The following rows are moved by automation to this sheet with without the formulas just to display the values. What is the best way to have any cell under "Duration" column that if it matches "#INVALID VALUE" to Display "No Downtime/Delayed" without clearing the rest of the column?

Paul Newcome · 05/23/22

My apologies. Missed a closing parenthesis.

=IFERROR(IF(Difference@row < 0, ROUNDDOWN(Difference@row / 60, 0) + " hrs " + (60 - MOD(Difference@row, 60)), ROUNDDOWN(Difference@row / 60, 0) + " hrs " + MOD(Difference@row, 60) + " mins"), "No Downtime/Delayed")

Mike TV · 05/20/22

@Jimrny

I would add an IFERROR function to the sheet it's coming over from that will display that text if it's getting an error. If that's not possible, then you'll need to add a helper column which has =IF(CONTAINS( and have it look for the value of "#INVALID VALUE" and replace it with what you'd like it to display. Then hide the duration column that's copied over from the original sheet.

Jimrny · 05/23/22

https://community.smartsheet.com/discussion/comment/330568#Comment_330568

where would be the best place to insert IFERROR ?

Paul Newcome · 05/23/22

@Jimrny You would wrap the whole thing like so...

=IFERROR(original_formula, "No Downtime/Delayed")

Jimrny · 05/23/22

https://community.smartsheet.com/discussion/comment/330657#Comment_330657

I wrapped it like this:

=IFERROR(Difference@row < 0, ROUNDDOWN(Difference@row / 60, 0) + " hrs " + (60 - MOD(Difference@row, 60)), ROUNDDOWN(Difference@row / 60, 0) + " hrs " + MOD(Difference@row, 60), "No Downtime/Delayed") + " mins"

And now I'm getting #INCORRECT ARGUMENT SET

Do I need to drop the additonal " mins" at the end, I added that to give me hrs/mins for duration. I'm guessing it would of given me "No Downtime/Delayed mins" if there was an error?

I'm trying to get duration to display XX hrs XX mins and if there's an error, to display "No Downtime/Delayed"

Paul Newcome · 05/23/22

That last bit with the "Mins" should actually come before the output of the IFERROR like so:

=IFERROR(Difference@row < 0, ROUNDDOWN(Difference@row / 60, 0) + " hrs " + (60 - MOD(Difference@row, 60)), ROUNDDOWN(Difference@row / 60, 0) + " hrs " + MOD(Difference@row, 60) + " mins", "No Downtime/Delayed")

Jimrny · 05/23/22

https://community.smartsheet.com/discussion/comment/330669#Comment_330669

I made the adjustment but it keeps giving me "Incorrect Argument Set", hmm.

Paul Newcome · 05/23/22

You forgot to include that first IF statement.

=IFERROR(IF(Difference@row..................

Jimrny · 05/23/22

https://community.smartsheet.com/discussion/comment/330707#Comment_330707

It's still giving me an "INCORRECT ARGUMENT SET" with the following:

=IFERROR(IF(Difference@row < 0, ROUNDDOWN(Difference@row / 60, 0) + " hrs " + (60 - MOD(Difference@row, 60)), ROUNDDOWN(Difference@row / 60, 0) + " hrs " + MOD(Difference@row, 60) + " mins", "Delayed, No Downtime"))

Am I missing an additional argument?

Paul Newcome · 05/23/22

My apologies. Missed a closing parenthesis.

=IFERROR(IF(Difference@row < 0, ROUNDDOWN(Difference@row / 60, 0) + " hrs " + (60 - MOD(Difference@row, 60)), ROUNDDOWN(Difference@row / 60, 0) + " hrs " + MOD(Difference@row, 60) + " mins"), "No Downtime/Delayed")

Jimrny · 05/24/22

https://community.smartsheet.com/discussion/comment/330740#Comment_330740

Thank you!! It's working properly now 😄

Changing Value Prefilled Cell Only if Matching Value

Best Answer

Answers

Help Article Resources

Categories