Log In
Register
Home
Active Member
September 14, 2021
Offline
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
See Answer
Trusted Members
February 13, 2021
Offline
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.
Answers Post
1 Guest(s)
