2

My question simply: Is it possible to generate the data in A3:A5 using a formula?

          A         B        C       D       E       F

1   PASSED/TOTAL |           EXAMS ATTEMPTED
2   -------------|-------|-------|-------|-------|-------
3   1/3          | ✔exG  | ✖exZ  | ✖exH  |       |
4   3/4          | ✖exC  | ✔exJ  | ✔exR  | ✔exN  |
5   1/1          | ✔exW  |       |       |       |

(If it's not clear, columns B through F from row 3 onward show whether someone has taken an exam AND whether they passed or failed it.)

So, back to the question... I can easily determine the total number of exams in the 3rd row with something like this in A3:

=MINUS( 5, COUNTBLANK( B3:F3 ) )

But how can I determine the number of passed exams? Is there even a way with formulas to search text content of other cells with some sort of "contains" thing -- or maybe even regex searching? Something like this:

=CountCellsThatContainText( "✔", B3:F3 )

Assuming there is such a thing, then I would need to combine the two together -- which I also am unsure of ... maybe CONCATENATE() would work for that, e.g.:

=CONCATENATE( CountCellsThatContainText( "✔", B3:F3 ), "/", MINUS( 5, COUNTBLANK( B3:F3 ) )

So..... before I posted I did a little more digging and found QUERY() but uhhh.... I'm hoping a greater mind than mine can tell me if that's even the right direction to go in.

PS: If it would make it more do-able, we could split column A into 2 or 3 columns.

1

The following little piece of code gives you the result, without prior knowledge about the character code number.

Code

function mySplit(range, delimiter) {
  var output = [], maxCol = range[0].length;
  for(var i=0, iLen=range.length; i<iLen; i++) {
    var colCounter=0, counter=0;
    for(var j=0, jLen=range[0].length; j<jLen; j++) {
      if(range[i][j] == "") {
        colCounter++;
      } else {
        if(range[i][j].split(delimiter).length > 1) {
          counter++;
        }
      }
    }
    output.push(counter + "/" + (maxCol - colCounter));
  }  
  return output;  
}

Screenshot

enter image description here
enter image description here

Example

I've created an example file for you: Certain Text Characters
If you copy the file, then you can access the code. Add the code into your existing sheet by going to Tools\Script editor in the main menu. Paste the code and save.

2
  • WOW. Had no idea you could do something like that. I'm playing with it now. – rsaw May 2 '14 at 13:36
  • 1
    Brilliant! Thank you so much! I tweaked it a little bit and couldn't be more happy. – rsaw May 2 '14 at 14:25
1
=ArrayFormula(SUM(--(if(B3:F3="",0,CODE(LEFT(B3:F3,1))=10004))))&"/"&minus(5,countblank(B3:F3))  

works for your example but highly not recommended. Simple booleans (Pass/Fail, Yes/No, 1/0) would be much easier to deal with in a spreadsheet and the ex? do seem redundant, given the column headings.

2
  • RE "the ex? do seem redundant, given the column headings": Indeed. In the actual spreadsheet there are dozens of possible exams but people only ever take 5-10 -- and not in any specific order. Also, in the actual spreadsheet there is only one EXAMS heading (a merged cell) above them all because I don't need a column for each specific exam. – rsaw May 2 '14 at 13:21
  • 2
    I'm going with the custom function solution but I want to thank you for taking the time to come up with something @pnuts. – rsaw May 2 '14 at 14:27
1

Here is another solution also:

=COUNTA(SPLIT(REGEXREPLACE(JOIN(",",B2:E2),"✖\w+",""),","))&"/"&COUNTA(B2:E2)

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.