Good Day all,
I am creating a shooting score sheet for my officers.
Please refer the image attached.
In our shooting contact,
Full Score is 120 Points
Marksman is 108 Points - 120 Points
1st Class Shooter is 97 Points - 107 Points
2nd Class Shooter is 84 Points - 96 Points
Fail is <83 Points.
We have 3 stages for each attempt.
Each stage we have In and Out score.
For In Score will be x2 and Out Score will be x1
E.g.
1) Stage 1 full score will be 40 Points. (20 Rounds In, will be 20x2=40 Points)
2) Stage 2 full score will be 60 Points. (30 Rounds In, will be 30x2=60 Point)
3) Stage 3 Full score will be 20 Points. (10 Rounds In, will be 10x2=20 Point)
Each officer will be given 3 attempts to get 2nd class shooter or above.
All formula already created however I having 2 issue where the AA(Final Score) and AB (Final Class)
E.g.
1) Officer A, 1st Attempt got Full Score & his Class will be Marksman. He will not go for 2nd & 3rd Attempt. As such his Final Scare will be 120 and Final Class will be Marksman.
2) Officer B, 1st Attempt got 60 Points & he failed. As such Officer B need to go for 2nd Attempt & he got 105 Points & his Class is 1st Class Shooter. Final score will be 105 and Final Class will be 1st Shooter.
3) Officer C, 1st Attempt got 82 Points & he failed. Officer C go for 2nd Attempt, got 83 Points & failed again. However, he went for 3rd attempt, he got 88 Points $ his Class will be 2nd Class Shooter. So Final Score will be 88 Point and Final Class will be 2nd Class Shooter.
4) Officer D failed all 3 attempts and his final score will be base on the 3rd attempt & that will be 83 Point & his class will be Failed.
I had tried to use IF & IFS Function in Final Score and Final Class but it doesn't work at all.
Or is there any function that I can use?
Able to guide me along the way please?
I am creating a shooting score sheet for my officers.
Please refer the image attached.
In our shooting contact,
Full Score is 120 Points
Marksman is 108 Points - 120 Points
1st Class Shooter is 97 Points - 107 Points
2nd Class Shooter is 84 Points - 96 Points
Fail is <83 Points.
We have 3 stages for each attempt.
Each stage we have In and Out score.
For In Score will be x2 and Out Score will be x1
E.g.
1) Stage 1 full score will be 40 Points. (20 Rounds In, will be 20x2=40 Points)
2) Stage 2 full score will be 60 Points. (30 Rounds In, will be 30x2=60 Point)
3) Stage 3 Full score will be 20 Points. (10 Rounds In, will be 10x2=20 Point)
Each officer will be given 3 attempts to get 2nd class shooter or above.
All formula already created however I having 2 issue where the AA(Final Score) and AB (Final Class)
E.g.
1) Officer A, 1st Attempt got Full Score & his Class will be Marksman. He will not go for 2nd & 3rd Attempt. As such his Final Scare will be 120 and Final Class will be Marksman.
2) Officer B, 1st Attempt got 60 Points & he failed. As such Officer B need to go for 2nd Attempt & he got 105 Points & his Class is 1st Class Shooter. Final score will be 105 and Final Class will be 1st Shooter.
3) Officer C, 1st Attempt got 82 Points & he failed. Officer C go for 2nd Attempt, got 83 Points & failed again. However, he went for 3rd attempt, he got 88 Points $ his Class will be 2nd Class Shooter. So Final Score will be 88 Point and Final Class will be 2nd Class Shooter.
4) Officer D failed all 3 attempts and his final score will be base on the 3rd attempt & that will be 83 Point & his class will be Failed.
I had tried to use IF & IFS Function in Final Score and Final Class but it doesn't work at all.
Or is there any function that I can use?
Able to guide me along the way please?
