Adusting range as needed I have the following formula: =IF(COUNTA(E3:P3)=0,\"\",

Question

Adusting range as needed

I have the following formula:     =IF(COUNTA(E3:P3)=0,"",COUNTIF(E3:P3,"<>"&B3))

It is used to determine how many students got the questions correct. I created form on the spreadsheet for each major test I give. It is set up for max of 12 students, but I could have less than 12 students, which I have in this case.

BREAKDOWN OF FORMULA

COUNTA(E3:P3) ---> Looks to be sure there are values greater than zero, ie test grades before it calculates.

COUNTIF(E3:P3), "<>"&B3 ---> Looks at the answer for that question for each student and see if there are any that do not match the correct answer in B3

The COUNTIF(E3:P3) works correctly only when there are 12 students. I need something different to replace P3 so that it will automatically adust the range it looks at if there are less than 12 students. E3:P3 fill in automatically, without skipping a column, with the student's name. Currently, I have 9 students which is E3:M3. The range that holds student names is E1:P1

Thanks for your help

Explanation / Answer

@$^$&%*&%$

=IFERROR(COUNTIF(OFFSET(E3,0,0,1,COUNTA(E3:P3)),"<>"&B3),"")

The syntax of OFFSET is OFFSET(range, rows, columns, [height], [width])

From range, we move rows rows down and columns columns to the right.

Optionally, we can use height and width to resize the range.

In OFFSET(E3,0,0,1,COUNTA(E3:P3)) we move 0 rows down and 0 columns to the right from E3, so we still start at E3.

We leave the height set to 1, but change the width to COUNTA(E3:P3), i.e. to the number of filled columns in E3:P3. For example, if E3 ... H3 are filled, and I3 ... P3 are empty, COUNTA(E3:P3) = 4, so OFFSET(E3,0,0,1,COUNTA(E3:P3)) is equivalent to E3:H3.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.