Help using NOT function

kpierson109

I want this column to show "N/A" or be blank if the Resource/Object type contains "guidance." If the type does not contain "guidance" I want it to populate the formula that starts at lower. This shows an incorrect argument set error when entered. Any advice would be great!

=IF(NOT(CONTAINS("guidance", [Resource/Object Type]@row, "N/A"), LOWER(Curriculum@row + ".g" + [Grade/Level]@row + ".m" + Module@row + IF([Concept/Topic/Arc]@row <> "", "." + [Concept/Topic/Arc]@row + IF(Lesson@row <> "", ".l" + Lesson@row)) + "." + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Public Facing Title]@row, " ", ""), CHAR(34), ""), "-", ""))))

Paul Newcome

Try this:

=IF(NOT(CONTAINS("guidance", [Resource/Object Type]@row)), "N/A", LOWER(Curriculum@row + ".g" + [Grade/Level]@row + ".m" + Module@row + IF([Concept/Topic/Arc]@row <> "", "." + [Concept/Topic/Arc]@row, "") + IF(Lesson@row <> "", ".l" + Lesson@row, "") + "." + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Public Facing Title]@row, " ", ""), CHAR(34), ""), "-", ""))))

kpierson109

I tried that and it says it is unparseable.

Carson Penticuff

If you want the result to be "N/A" if [Resource/Object Type] DOES contain "guidance", I don't believe you want to use the NOT() function.

=IF(CONTAINS("guidance", [Resource/Object Type]@row), "N/A", LOWER(Curriculum@row + ".g" + [Grade/Level]@row + ".m" + Module@row + IF([Concept/Topic/Arc]@row <> "", "." + [Concept/Topic/Arc]@row + IF(Lesson@row <> "", ".l" + Lesson@row)) + "." + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Public Facing Title]@row, " ", ""), CHAR(34), ""), "-", "")))

kpierson109

That worked! Is it possible to have the cell be blank instead? I don't know if that is a possibility. I also want this to happen if the resource/object type contains page. So either guidance or page. What would that look like if I did that?

Carson Penticuff

I think this one will work.

=IF(OR(CONTAINS("guidance", [Resource/Object Type]@row), CONTAINS("page", [Resource/Object Type]@row)), "", LOWER(Curriculum@row + ".g" + [Grade/Level]@row + ".m" + Module@row + IF([Concept/Topic/Arc]@row <> "", "." + [Concept/Topic/Arc]@row + IF(Lesson@row <> "", ".l" + Lesson@row)) + "." + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Public Facing Title]@row, " ", ""), CHAR(34), ""), "-", "")))

Paul Newcome

Sorry about that. I forgot to adjust the parenthesis at the very end. There should have been a total of two.

=IF(AND(NOT(CONTAINS("guidance", [Resource/Object Type]@row)), NOT(CONTAINS("page", [Resource/Object Type]@row))), LOWER(Curriculum@row + ".g" + [Grade/Level]@row + ".m" + Module@row + IF([Concept/Topic/Arc]@row <> "", "." + [Concept/Topic/Arc]@row, "") + IF(Lesson@row <> "", ".l" + Lesson@row, "") + "." + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Public Facing Title]@row, " ", ""), CHAR(34), ""), "-", ""))

kpierson109

Thank you! This is all so helpful.

I am trying to build this formula in another column. I want the formula to populate the name using the formula starting at LOWER if the resource/object type contains "page." How would I fix this?

=IF(CONTAINS("page", [Resource/Object Type]@row, LOWER(Curriculum@row + ".g" + [Grade/Level]@row + ".m" + Module@row + IF([Concept/Topic/Arc]@row <> "", "." + [Concept/Topic/Arc]@row + IF(Lesson@row <> "", ".l" + Lesson@row)) + IF([Product Line]@row = "Prologue", ".pro") + IF(CONTAINS("guidance", [Resource/Object Type]@row), ".tg"))))

Paul Newcome

It looks like you have a misplaced parenthesis or two.

=IF(CONTAINS("page", [Resource/Object Type]@row), LOWER(Curriculum@row + ".g" + [Grade/Level]@row + ".m" + Module@row + IF([Concept/Topic/Arc]@row <> "", "." + [Concept/Topic/Arc]@row, "") + IF(Lesson@row <> "", ".l" + Lesson@row, "") + IF([Product Line]@row = "Prologue", ".pro", "") + IF(CONTAINS("guidance", [Resource/Object Type]@row), ".tg", ""))

kpierson109

Thank you!

Paul Newcome

Happy to help. 👍️