1

I have a sheet in Excel that shows success of students in certain parts of the exam (tests, presentations, final exam etc.). I need to calculate some statistical data and the problem is that for some tests they have three tries, only the highest points count and I do not have the "max" column. I need to count how many got more than 15 points in best attempt, how many between 10 and 14,9, etc. Also I need to see how many students had at least one try (based on a non-empty field or >=0).

Table looks like this (one part):
Student....Att1....Att2....Att3
Anna.........10......14.....13,5
Belle.........7.........15......15,5
Clara........17......18.....16,5

So, from this I would need to extract in one cell that number of max per-row values >=15 is 2 (Belle at 15,5 and Clara at 18), and that total students ever attempted at least once is 3.

I repeat that I cannot add the separate column where I would calculate max values because it is an official document, and adding one to another sheet is not a preferred solutions.

I tried some subtotal and offset combinations, but without much sucess. I use MS Excel 2013, other colleagues use 2007 and 2010.

2
  • 1
    If you can't add to this sheet, where are you planning to put the formula? – CharlieRB Mar 12 '14 at 11:58
  • could you please show (based on this sample) the final expected array with all this extra info without any added column ? – NeronLeVelu Mar 12 '14 at 16:30
3

This formula can be easily expanded for any number of rows or columns

=SUMPRODUCT((SUBTOTAL(4,OFFSET(B2:D2,ROW(B2:D10)-ROW(B2),0))>15)+0)

OFFSET returns each row as a separate range, SUBTOTAL finds the max value [4] in each row and SUMPRODUCT counts how many of those values are >15.

It's also possible to do the same with COUNTIF instead of SUBTOTAL, i.e.

=SUMPRODUCT((COUNTIF(OFFSET(B2:D2,ROW(B2:D10)-ROW(B2),0),">15")>0)+0)

For a range, e.g. >10 and <=15 you can adjust the SUBTOTAL version like this

=SUMPRODUCT((SUBTOTAL(4,OFFSET(B2:D2,ROW(B2:D10)-ROW(B2),0))>{10,15})*{1,-1})

4
  • How would you do a condition of between two numbers? – Madball73 Mar 12 '14 at 20:14
  • Good question, Madball - I edited my answer – barry houdini Mar 12 '14 at 22:58
  • Testing the above, looks like the between works. Also made me realize my method doesn't and should have actually used +--. Removing my answer. Yours also has advantage of being a single range instead of 3. Sweet! – Madball73 Mar 12 '14 at 23:21
  • Madball73, thanks a lot for this. The formula works well and gives me desired data. There is only one strange case: I have students that tried the test, got 0 points, and students that did not try the test and have no points (empty cell), I also have to differentiate them. The formula you gave, with <{5,0})*{1,-1}) at the end (I needed >=0 and <5), counts also case when student has 3 empty cells, or 3 cells with some text like N/A, -. Ending it <5)+0) does the same. How can I avoid that and differentiate empty cell (or cell containing non-numeric value) and 0, or count solely 0? – adriatic Mar 13 '14 at 14:01
1

for the count of more or equel to 15 for a line (let say in columns E, so E2 here)

=COUNTIF(B2:D2;">=15")

for you max it (in E5 here)

=countif(E2:E4;">0")

sample

student at1 at2 at3     up15
anna    10  14  13,5    0
Belle   7   15  15,5    2
clara   17  18  16,5    3
                 Max    2

could add a test for at least 3 try per row but not clear tome where you want it (per line or on max)

2
  • There is a requirement where helper/additional columns cannot be added. – Madball73 Mar 12 '14 at 16:11
  • but adding something will, by itself add a column (or row). Now is a hidden column available ? There is also 2 other solution 1) doing it from an external worksheet (not a clean solution) 2) make it from a macro than eventually use internal excel function (but is certainly more efficient with pure vbs code). – NeronLeVelu Mar 12 '14 at 16:17

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.