Nesting IF Formula

Morgan Mattson

I have been reading though the discussions and have attempted to nest the IF functions but I am having difficulties with it maybe someone will be able to help me. We are using check boxes to track numbers when checked. I created the function and got it to work.

=IF([PRHT Wrap  Claimed]10, 10, 0)

The issue I am having is, we N/A the cells when they are not being used so they are not accidently checked. This of course creates a #INVALID DATA TYPE ERROR. I have made multiple different attempts to make it so a N/A is equal to zero but I am not having any luck. I am thinking I need a nested IF formula but I am not for sure. Formulas are not my strong suite sadly. Any help on this would be great

Mike Wilday

Try wrapping the IF statement in an IfError... 

=iferror(IF([PHRT Wrap Claimed]10, 1, 0))

Morgan Mattson

Thanks Mike, I attempted to put in the formula you suggested and it came up as #UNPSARSEABLE. Any other ideas?

Mike Wilday

Well, it looks like I misspelled your column name. This one should do it. 

=iferror(IF([PRHT Wrap Claimed]10, 1, 0))

I keep this link on hand when working with formulas in Smartsheet. 

https://help.smartsheet.com/articles/2476176-formula-error-messages

Morgan Mattson

When I was reading through your earlier post I had noticed the typo and changed it when I was originally trying your formula. I tried the new one you posted above with the fix and it still isn't working.

Mike Wilday

hmmm. can you post a screenshot of the data again with the new formula?  

Morgan Mattson

Here you go. I had to move the formula down to row 13 due to data input.

Mike Wilday

I see the problem. You have a 10 in there instead of 1. Delete that zero and you should see it working properly. 

What you have: =iferror(IF([PRHT Wrap Claimed]13, 10, 0))

What it should be: =iferror(IF([PRHT Wrap Claimed]13, 1, 0))

What do you get when you update that? 

Morgan Mattson

I'm still getting the unparseable.

 

Mike Wilday

I see some other potential errors here. What should be the result if the Wrap is checked? If the wrap = 1 then what should the result be? 

Morgan Mattson

When it's checked it equals 10. The original formula I have for that works, you can see it above line 13 in the pict I posted today.

Mike Wilday

=iferror(IF([PRHT Wrap Claimed]13 = 1, 10, 0)," ")

Try this one. 

Morgan Mattson

That one does not work either.

Mike Wilday

Hmmm. I am not sure what is going on. I have attached a couple screenshots to show you that it is working on my end. This should be completely unattached to anything else. The formula doesn't even throw an error when N/A is in the checkbox field, cause your just checking to see if its a 1.

I have it working with - and without - the IFERROR. There must be something else going on... 

See animated gif below. 

Morgan Mattson

Apparently it was due to the fact I was copying and pasting the formula in. I typed it in and now it is working. Sorry about the extra steps.

Thank you for all of your help on this this will make tracking our information a lot easier!

Mike Wilday

You're welcome.