I recently got a request through LinkedIn from someone I met at a conference who wanted to know how she could do the following..
She wanted to calculate exam results given the following parameters.
” If they do not fail any module but can now have up to a maximum of 3 modules that falls between 35 and <40 they can pass by compensation, my question is how do I get excel to understand it is up to 3 and no more. For example, if they have not failed any modules and have 3 or less modules that come in between the above they would ‘Pass by Compensation’, if they have not failed any modules but have 4 or more between 35 and <40 they fail. But if they fail any module, they fail regardless. ”
This required a combination of IF/And/CountIfs.
You can download the file here…Compensation pass fail question
And in this video I explain how I came up with the formula.
[fvplayer src=”https://s3-eu-west-1.amazonaws.com/the-excel-expert/compensation_pass_fail_pass.mp4″]