0

I have trouble with creating a complex conditional formula in Google Sheets. Honestly, I feel there is a need for some script with a "for" cycle, but I'm not sure.

The task is the following. I have a table with countable and text data. Based on the values of the "Result" cells there could be the filled-in decision cell with a text either "OK" or "NOT OK". See the screenshot and google sheet file below. Could you please help?

link to the file

enter image description here

Improve this question
3
  • Welcome to Web Applications Stack Exchange. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results.
    – doubleunary
    Sep 20 at 15:20
  • Please address the previous comment. Further, your rules are confusing to follow and the content is not accessible to everyone when in a picture. Please edit your question to include clearer instructions as text. Note that Rule 1 means that "NOT OK" is returned for the sample data because of the AND. You should approach your rules descriptions using math's order of operations. Use brackets to make clear what is inclusive, exclusive, etc. Rule 6 is unnecessary as those cells appear nowhere else in the rules ergo are already ignored etc. Your examples should show results
    – Blindspots ♦
    Sep 20 at 17:23
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Sep 20 at 17:25

1 Answer 1

Reset to default
1

Use rows() and filter(), like this:

=if( 
  and( 
    rows(filter(B4:B6, len(A4:A6))) = rows(filter(B4:B6, B4:B6 >= 100%)), 
    rows(filter(B8:B10, len(A8:A10))) = rows(filter(B8:B10, regexmatch(B8:B10, "Acceptable|Excellent"))) 
  ), 
  "OK", 
  "NOT OK" 
)

See rows() and filter().

Improve this answer
1
  • Thanks, it works!
    – Alex Savin
    Sep 22 at 18:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.