Hi there
I am working in the South African Police Services as a subject matter trainer,
as a part thereof I need to evaluate and calculate scores of trainees on the shooting range.
as a part of a specialized unit the pass rates are very strict, in my fictitious database that I added you will notice three examples, one of Captain Dollar of which I am happy with the formulas, the score sheet consists of 8 tables, (8 shooting targets) of which the member must also have at least 80% to pass each table, with our organization if you failed to achieve at least 80% on a table it means you fail the whole exercise.
Now example 2 shows Captain Euro failing on Table 8 by achieving only 67%, now I need a Formula that will automatically revert the percentage in purple (Block V13) to 0%.
Reason for this is because the Captain failed Table 8 he fails the exercise but if you consolidate all 8 tables the percentage combine is still a pass of 91.7% of which we don't want. You will also see there is a Rifle exercise with a total out of 200, now these two totals must be combine to give final competent or not competent.
so Example 3 shows how I would want this score sheet to look, I would really appreciate if you could assist with a formula as I need to capture bulk information and need formulas to do the hard work for me.
Thank you so much for this opportunity
Regards
A simple 'If' statement will work perfectly here for your purposes. Insert the following formula in your 'Total % pistol exercises' column:
=IF(OR(G13<0.8,I13<0.8,K13<0.8,M13<0.8,O13<0.8,Q13<0.8,S13<0.8,U13<0.8),0,AVERAGE(G13,I13,K13,M13,O13,Q13,S13,U13))
I only put the formula in the cell in purple so you can see at a glance how it compares to example 3.
Hi Jessica
Thank you very much, the formula works perfectly.
Regards
