1

I have a dataset in which the rows are a student's performance on a test, and the columns are the individual test questions. I would like to create a formula that sums the columns for a student (the test performance; 0 = incorrect, 1 = correct) until there are four 0s in a row. For instance, if a student scored:

Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16 Q17 Q18 Q19 Q20
1  1  1  1  1  1  0  1  1  0   0   0   1   0   0   0   0   1   1   0

The formula should return 9, because it sums until Q17 where the condition is met (four straight incorrect responses).

Presume performance on the first question for the first student is cell B2 (first row is headers, first column is student name).

Any help would be much appreciated, because this exceed my excel knowledge.

2

Use this formula that finds the first string of 4 0 and sets that as the end of the range:

=SUM(B2:INDEX(B2:U2,MATCH(1,INDEX((B2:R2=0)*(C2:S2=0)*(D2:T2=0)*(E2:U2=0),),0)))

enter image description here

| improve this answer | |
  • This is fantastic. I am blown away by the elegance here. :-) But how the heck does the INDEX() return a cell reference? The Match returns 14, which is the start of the string of 4 zeros. The outer Index() evaluates to 0, which is the value of the 14th element in B2:U2. That's what it's supposed to do. So the Sum should boil down to SUM(B2:0) which would make no sense. And instead Sum thinks it's adding up the first 14 1's and 0's. It shows that array as it's "Value 1". Enlighten me, PLEASE. – Bandersnatch Sep 10 '17 at 17:40
  • ...and it doesn't work without the inner INDEX() which doesn't seen necessary on the face of it. What is it doing? C'mon, Scott, be a mensch. :-) – Bandersnatch Sep 10 '17 at 17:50
  • @Bandersnatch the inner index creates an array that is 17 columns wide, consisting of 0/1 in this case {0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0} then Match returns 14 to the index. When you use INDEX() as part of an address. it will set it as an address and not a value: SUM(INDEX(A:A,1):INDEX(A:A,10)) is the same as =SUM(A1:A10) – Scott Craner Sep 10 '17 at 20:14
  • This is great - thanks so much! Within the INDEX function, why is the first one B2:R2 though, and the next C2:S2, etc? For instance, what if the test had 79 items (as one of mine does) - how would this formula need to be changed? Just trying to understand the principles so I can scale it up and down as needed. Thanks again! – Steven Sep 12 '17 at 2:50
  • @Steven the ranges all need to be the same size, and we need to compare four consecutive cells. So the first starts with the first column and ends on the forth from the end. Then each range moves one cell over. So the first iteration compares B2,C2,D2,E2. Each iteration after that moves one to the right till the last compares R2,S2,T2,U2. – Scott Craner Sep 12 '17 at 12:39
1

Consider the following User Defined Function:

Public Function SpecialSum(rng As Range) As Long
    Dim r As Range, crit As Long, v As Long
    SpecialSum = 0
    crit = 0
    For Each r In rng
        v = r.Value
        SpecialSum = SpecialSum + v
        If v = 0 Then
            crit = crit + 1
        Else
            crit = 0
        End If
        If crit = 4 Then Exit Function
    Next r
End Function

It adds the values in the range until it encounters four consecutive zeros:

enter image description here

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=SpecialSum(B2:Z2)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

| improve this answer | |
  • This one's great too, GS. – Bandersnatch Sep 10 '17 at 17:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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