# Calculate POINTS and DIVISIONS

1. ## Calculate POINTS and DIVISIONS

Dear Experts,
Am having problem with calculating POINTS and DIVISIONS using excel. For example, if a student scores D,A,F,D,C,B,A,A,F,C,C,B, in say twelve subjects. Manually one can calculate this by taking any seven subjects that a student performed highly out of the twelve ie A,A,A,B,C,C and C. Where A=1,B=2,C=3,D=4,F=5. Therefore the total points for these seven subjects is 14. Now this is DIVISION ONE(1) of POINTS 14. Division 1 starts with point 7-17, Division Two(11) point 18-21, Division Three point 22-25, Division 1V point 26-33 and Division Zero 34-35. So please help me calculating this automatic in excel.  Register To Reply

2. ## Re: Help me how to calculate POINTS and DIVISIONS

I am going to try to rephrase your question to see if I understand it. Tell me if I am correct or incorrect:

You want to take the 7 best scores out of 12 for each student.

Then, you want to add the 7 best scores up.

Then you want to label the student as Division 1, Division 2, Division 3, or Division 0, depending on how badly the student has done.

Is this correct?

Also, please describe the layout of your spreadsheet... Where are students names and scores kept? Where would you like the Division number to be calculated?  Register To Reply

3. ## Re: Help me how to calculate POINTS and DIVISIONS

Hi,

Assuming the twelve grades are in cells A1:A12, try this array formula (must be confirmed with CTRL+SHIFT+ENTER, not just ENTER):

=LOOKUP(SUM(SMALL(LOOKUP(A1:A12,{"A","B","C","D","F"},{1,2,3,4,5}),ROW(\$1:\$7))),{7,18,22,26,34},"Division "&{"One","Two","Three","Four","Zero"})

Regards  Register To Reply

4. ## Re: Help me how to calculate POINTS and DIVISIONS

Yes it is correct .Thanks.
A layout should have names(A),Nine subjects ie B,C,D,E,F,G,H,I,J and their respective scores,POINTS and DIVISIONS to be calculated. Where A,B,C etc are columns.  Register To Reply

5. ## Re: Help me how to calculate POINTS and DIVISIONS

Hi,
Thanks so much for this formula. However, this could not work well.  Register To Reply

6. ## Re: Help me how to calculate POINTS and DIVISIONS

try the attachment  Register To Reply

7. ## Re: Help me how to calculate POINTS and DIVISIONS

"Hi,
Thanks so much for this formula. However, this could not work well.
"

Can you give an example or two of cases in which it doesn't work?

Regards  Register To Reply

8. ## Re: Help me how to calculate POINTS and DIVISIONS Originally Posted by XOR LX "Hi,
Thanks so much for this formula. However, this could not work well.
"

Can you give an example or two of cases in which it doesn't work?

Regards
yours works fine for me  Register To Reply

9. ## Re: Help me how to calculate POINTS and DIVISIONS

Hi guy,
Now i have attached the spreadsheet to see the layout and complete POINTS and DIVISIONS.Sorry, Previously i had no idea how and where to attach a worksheet.
Regards  Register To Reply

10. ## Re: Help me how to calculate POINTS and DIVISIONS

Hi Cooktein2,
I found this formula and your layout example very good.But i apply it without success. After typing in the formula, i enter(CTRL+SHIFT+ENTER) but it does not return anything. I attach my worksheet similar to yours and help to complete please.I also attach another spreadsheet with more columns but aims the same ie calculating POINTS and DIVISION per student.  Register To Reply

11. ## Re: Help me how to calculate POINTS and DIVISIONS

note that i have a second worksheet which stores the lookup tables  Register To Reply

12. ## Re: Help me how to calculate POINTS and DIVISIONS

Hi,
This formula has done what i needed.I was previously making my own errors.Thanks so much.  Register To Reply

13. ## Re: Help me how to calculate POINTS and DIVISIONS

Its so good. Big up!
Regards  Register To Reply

14. ## Re: Calculate POINTS and DIVISIONS

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.  Register To Reply

15. ## Re: Calculate POINTS and DIVISIONS

Thank you so much. Am now familiar with marking threads as solved or unsolved in future  Register To Reply

16. ## How to deal with two or more sheets

Hi experts,
I have (attached) spreadsheet with solutions.If you open it there are two sheets ie Student's Score Sheet and Lookup Sheet.
I don't know the steps that wil lead me to get the same results. Please help me.

Regards  Register To Reply

17. ## Re: Calculate POINTS and DIVISIONS

am having the same problem and i have resolved it even though i have followed the steps  Register To Reply

18. ## Re: Calculate POINTS and DIVISIONS

Hi,
Can you please give me some steps that led to resolving it?  Register To Reply

19. ## Re: Calculate POINTS and DIVISIONS

Hi,
Can you please give me some steps that led to resolving it?  Register To Reply

20. ## Posting problem

Hi,
How can i post a new thread? I always see Reply,Go Advance,Submit Reply etc  Register To Reply

21. ## How to deal with two or more sheets

Hi experts,
I have (attached) spreadsheet with solutions.If you open it there are two sheets ie Student's Score Sheet and Lookup Sheet.
I don't know the steps that wil lead me to get the same results. Please help me.  Register To Reply

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1