CODEWITHSUNDEEP
excel-formulatextjoin
Skittles
Skittles

Reputation: 1

IF(OR(NOT) nestled functions in Excel - how to return a sentence when certain checkboxes are not ticked?

I am making an assessment spreadsheet in Excel, and I want to return two sentences based on whether on not certain checkboxes are ticked.

For example, for spelling, we might be learning the suffixes '-ly' '-ed' and '-ing'. I want to return a result in one cell "Johnny has mastered the following suffixes: -ly, -ed," if those two checkboxes were ticked in Johnny's column.

In another cell, I want to return "Johnny is still working on the following suffixes: -ing" as that was not ticked in Johnny's column. I have worked out how to do the former, but need help with the latter. Here was my solution for the first, where C1 = Johnny's name, B column = the checkboxes, and A column = the names of the suffixes:

=TEXTJOIN(" ", TRUE, C1, "mastered the following suffixes:", IF(OR(NOT(B2), B2=""), "", A2), IF(OR(NOT(B3), B3=""), "", A3), IF(OR(NOT(B4), B4=""), "", A4),)

However, I can't work out how to give the negative sentence, based on the checkboxes that aren't ticked. Any help is appreciated!

I'm still learning excel functions and so I played around with it for a bit, but with no luck.

Upvotes: 0

Views: 43

Answers (1)

Vlado Bošnjaković
Vlado Bošnjaković

Reputation: 21

You can replace your formula with:

=TEXTJOIN(" ",TRUE,C1,"mastered the following suffixes:",FILTER(A2:A4,B2:B4=1))

And use the additional formula for checkboxes that aren't ticked:

=TEXTJOIN(" ",TRUE,C1,"is still working on the following suffixes:",FILTER(A2:A4,B2:B4=0))
  1. TEXTJOIN() have parameters
  • " " -> separator,
  • TRUE -> ignore blanks,
  • rest are strings to be joined
  1. FILTER() have parameters:
  • A2:A4 -> array from which values should be returned,
  • B2:B4=0 -> range of values to be tested against value after = sign.

The number of elements in the first and second arrays in the filter function should be the same.

Upvotes: 0

Related Questions