September 14, 2021
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
February 13, 2021
A simple 'If' statement will work perfectly here for your purposes. Insert the following formula in your 'Total % pistol exercises' column:
I only put the formula in the cell in purple so you can see at a glance how it compares to example 3.