I am trying to create an IF formula with a +/- range!

Alicia D

Hello!

I am trying to create an IF formula where if the value is 40% with a range of +/- 2%, it is a pass. If it is any percentage outside of the range (38%-42%), then I was it be a fail. Here is the chart in reference! Any help would be amazing.

KPH

Try this

=IF(AND([Rasterized Patch Measurement]@row >= 0.38, [Rasterized Patch Measurement]@row <= 0.42), "Pass", "Fail")

This looks at the value in Rasterized Patch Measurement. If it is more than or equal to 0.38 (which is 38% if you have used the percent format option) AND less than or equal to 0.42 (42%) then it returns "Pass". For all other values it returns "Fail".

KPH

You can set up conditional formatting using this option in the tool bar

Set your condition

Click OK

Then set your format

Click OK

Then choose whether to change the whole row or just the cell

Repeat for green.

There is a tutorial here: https://help.smartsheet.com/learning-track/level-1-get-started/conditional-formatting

KPH

Try this

=IF(AND([Rasterized Patch Measurement]@row >= 0.38, [Rasterized Patch Measurement]@row <= 0.42), "Pass", "Fail")

This looks at the value in Rasterized Patch Measurement. If it is more than or equal to 0.38 (which is 38% if you have used the percent format option) AND less than or equal to 0.42 (42%) then it returns "Pass". For all other values it returns "Fail".

Alicia D

@KPH this worked perfect, thank you so much!

Alicia D

@KPH , do you know if I would be able to have the column color change along with the pass/fail? Like the photo below

KPH

You can set up conditional formatting using this option in the tool bar

Set your condition

Click OK

Then set your format

Click OK

Then choose whether to change the whole row or just the cell

Repeat for green.

There is a tutorial here: https://help.smartsheet.com/learning-track/level-1-get-started/conditional-formatting

Alicia D

@KPH , amazing thank you very much!

KPH

Happy to help. If you can, please mark the formula as an answer as well, so anyone who is looking for the answer to your first question can find it. Enjoy your colorful sheet!