Nesting IF Formula

03/23/18 Edited 12/09/19

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

Capture.JPG

Popular Tags:

Comments

  • Mike WildayMike Wilday ✭✭✭✭✭

    Try wrapping the IF statement in an IfError... 

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

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

  • Mike WildayMike 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

  • 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 WildayMike Wilday ✭✭✭✭✭

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

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

    2018-04-23 15_36_33-BPP Tracking Log w_ Claiming - Smartsheet.com - Internet Explorer.png

  • Mike WildayMike 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? 

  • I'm still getting the unparseable.

     

  • Mike WildayMike 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? 

  • 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 WildayMike Wilday ✭✭✭✭✭

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

    Try this one. 

  • That one does not work either.

  • Mike WildayMike 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. 

    2018-04-24_08-34-15.gif

  • 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 WildayMike Wilday ✭✭✭✭✭

    You're welcome. 

Sign In or Register to comment.