0

I have a dataset of subjects (one per row), each with scores taken over several days. Each day has multiple scores, but the number of scores varies such that there are many blank cells.

See a small sample of the spreadsheet here

I need formulas for two things:

  • The number of times the cell range for each day only contains scores of 1 or 2 (ignoring blanks)
  • The number of times the cell ranges for consecutive days contain any instances of scores of 1 or 2 (ignoring blanks)

I've entered the values in the last two columns manually but I have 21 days and >500 subjects so really need to automate it.

Edit: I'm not sure how I can make the question more specific :/

Improve this question
2
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.
    – Community Bot
    Commented Apr 9 at 10:28
  • Hi and welcome to Superuser. Please note that this is not a free code or formula writing service. To make you question more specific, you should let us know what you have tried and the exact problem (not task) you are facing. Look into COUNTIF.
    – cybernetic.nomad
    Commented Apr 9 at 12:46

1 Answer 1

Reset to default
0

With helper columns (copy as required):

Z18 : =MAXIFS($H18:$V18,$H$16:$V$16,Z$17)
AC18 : =--(MINIFS($H18:$V18,$H$16:$V$16,AC$17)<3)

Results (copy as required):

W18 : =COUNTIF($Z18:$AB18,"<3")
X18 : =LET(data,$AC18:$AE18, MAX(FREQUENCY(IF(data,COLUMN(data)), IF(data,,COLUMN(data)))))

Scores

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .