Hello Excel gosu's,
I have a pickle with an excel formula, and I can't seem to find an answer anywhere.
My data looks vaguely like the data below
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>
Let's say I want the maximum 'marks' of student B but NOT for class A & B. (let's assume there are thousands of records)
I know I can fix this by hardcoding A, B in the MAXIFS function, but I want to read these values from a list or table.
excludeClass is a named range containing {"A";"B"}
In my head the function looks like this: {=MAXIFS(marks,student,"A",class,"<>"&excludeClass)}
That doesn't work (it says 7 instead of 4).
I then tried converting 'class' to a ones and zeroes like this
=MAXIFS(marks,student,"A",IF(ISERROR(MATCH(class,excludeClass,0)),1,0),1)
That function returns {0;0;0;0;0;0;1;1} which I wanted to filter on 1, but apparantly that doesn't work either
It gives me an embarassing #VALUE
Does anyone have a clue
?
Cheers!
I have a pickle with an excel formula, and I can't seem to find an answer anywhere.
My data looks vaguely like the data below
| student | class | marks |
| A | A | 10 |
| B | A | 10 |
| A | B | 7 |
| B | B | 9 |
| A | C | 3 |
| B | C | 9 |
| A | D | 1 |
| B | D | 4 |
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>
Let's say I want the maximum 'marks' of student B but NOT for class A & B. (let's assume there are thousands of records)
I know I can fix this by hardcoding A, B in the MAXIFS function, but I want to read these values from a list or table.
excludeClass is a named range containing {"A";"B"}
In my head the function looks like this: {=MAXIFS(marks,student,"A",class,"<>"&excludeClass)}
That doesn't work (it says 7 instead of 4).
I then tried converting 'class' to a ones and zeroes like this
=MAXIFS(marks,student,"A",IF(ISERROR(MATCH(class,excludeClass,0)),1,0),1)
That function returns {0;0;0;0;0;0;1;1} which I wanted to filter on 1, but apparantly that doesn't work either
It gives me an embarassing #VALUE
Does anyone have a clue
?Cheers!
