Nesting IF Formula
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
Comments
-
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?
-
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.
-
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.
-
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.
-
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.
-
=iferror(IF([PRHT Wrap Claimed]13 = 1, 10, 0)," ")
Try this one.
-
That one does not work either.
-
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.
-
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!
-
You're welcome.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!