Forum Discussion

James818's avatar
James818
Brass Contributor
Aug 15, 2023

If one of multiple cells contain specific text then return value

I am stumped on work project.

 

I have a cell F13 intended to be dependent on a range of 4 other cells.

 

When all cells are No, I just need F13 to stay blank. So far this works.

 

Range F5, F7, F9 & F11 will either be "No" or Not Effective". If any of those cells just has 1 Not effective, I want to make F13 to populate as "Not Effective".

 

If I can only select those cells specifically that would be perfect as in between is just rows as a spacer.

 

 

 

Here are my formulas.

 

F13, this cell currently works only if all cells are Not effective.

=IF(AND(F5="Not Effective",F11="Not Effective"),"Not Effective","No")

 

I attached a link to my file on google drive.

 

Thank you in advance.

 

https://docs.google.com/spreadsheets/d/1tgQbOQjgpP33nw91Ahl-lz04cezLVMwZ/edit?usp=sharing&ouid=114356740515231394022&rtpof=true&sd=true

excel
Formulas and Functions

7 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Aug 15, 2023

    James818 

    Just for entertainment, I returned each of the 4x6 array of assessment criteria with a single dynamic array formula.  Then I aggregated the table to give the summary row with a second formula.

     

    Worksheet formula to give 2D array
= IF(keyDates, IF((keyDates>=startDates) * (keyDates<=endDates),text,"No"), "")

Worksheet formula to give summary row
= IF(BYCOL(status=text, ORλ), text, "No")

Lambda function
ORλ
= LAMBDA(x, OR(x))

     

    The key thing I would note is that current Excel formulas bear little to no resemblance to traditional spreadsheet methods.

     

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Aug 15, 2023

    James818 

    I think an OR condition would capture the cases you need while ignoring the FALSE arising from the intervening blank cells.

    = IF( OR(statusColF = "Not effective"), "Not effective", "")

    If you needed to remove the blanks, that is straightforward with 365 and the array shaping functions WRAPROWS and TAKE.

    • James818's avatar
      James818
      Brass Contributor
      Aug 15, 2023

      PeterBartholomew1 

       

      Thank you 

       

       

      With everything my boss keeps adding and subtracting form our chart I keep having to change formulas.   Thank you. 

       

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Aug 15, 2023

        James818 

        Some things are easier with array formula and Lambdas; others you just have to bite the bullet and get in there with a knife and fork.  The times Lambda wins out are when a single change in the Lambda propagates throughout the workbook.  If every formula is a one off then it gains you little and can even increase your problems.

  • mtarler's avatar
    mtarler
    Silver Contributor
    Aug 15, 2023
    By "If any of those cells just has 1 Not effective" means at least 1 of those cells is 'not effective'
    then it would be easy to use this but it does include the rows in between
    =IF(COUNTIF(F5:F11,"Not Effective")>=1, "Not Effective", "No")
    alternatively you can change that to be =1 instead
    if you don't want to include those inbetween rows you could try
    =IF(SUM(--(INDEX(F5:F11,{1,3,5,7})="Not Effective"))>=1,"Not Effective", "No")
    • James818's avatar
      James818
      Brass Contributor
      Aug 15, 2023
      mtarler Thank you.
      The first one works perfect, =IF(COUNTIF(F5:F11,"Not Effective")>=1, "Not Effective", "No")

      I tested the 2nd formula, and it did not change. Fortunate the first one worked I hoped for.

      Luckily when this does turn into a final chart, I will lock cells only for what can be filled in only, so the in between rows should not be an issue once the sheet is protected.

      Thank you so much!