Final Score and Final Class

Jongnj87

New Member
Joined
Oct 16, 2020
Messages
32
Office Version
  1. 365
Platform
  1. Windows
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?
 

Attachments

  • Mr Excel.PNG
    Mr Excel.PNG
    95.9 KB · Views: 4

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,069
Office Version
  1. 365
Platform
  1. Windows
How about in AA4
Excel Formula:
=IF(Y4<>"",Y4,IF(Q4<>"",Q4,I4))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,044
Messages
5,767,800
Members
425,437
Latest member
blaix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top