Calculate POINTS and DIVISIONS

**[email protected]** · 09-08-2013, 02:00 AM

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.

**ExcelTactics** · 09-08-2013, 03:14 AM

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?

**XOR LX** · 09-08-2013, 06:31 AM

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

**[email protected]** · 09-11-2013, 04:29 PM

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.

**[email protected]** · 09-11-2013, 04:32 PM

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

**Cookstein2** · 09-13-2013, 10:27 AM

try the attachment

**XOR LX** · 09-13-2013, 10:33 AM

"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

**Cookstein2** · 09-13-2013, 10:37 AM

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

**[email protected]** · 09-15-2013, 08:24 AM

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

**[email protected]** · 09-15-2013, 09:10 AM

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.

**Cookstein2** · 09-15-2013, 01:31 PM

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

**[email protected]** · 09-15-2013, 04:09 PM

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

**[email protected]** · 09-16-2013, 12:42 PM

Its so good. Big up!
Regards

**arlu1201** · 09-18-2013, 02:24 AM

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.

**[email protected]** · 09-18-2013, 04:11 AM

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

**[email protected]** · 09-19-2013, 04:20 PM

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

**binemungu** · 09-20-2013, 03:30 PM

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

**[email protected]** · 09-20-2013, 04:01 PM

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

**[email protected]** · 09-20-2013, 04:03 PM

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

**[email protected]** · 09-24-2013, 03:03 PM

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

**[email protected]** · 09-24-2013, 03:39 PM

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.

Calculate POINTS and DIVISIONS

LinkBack

Thread Tools

Rate This Thread

Display

Calculate POINTS and DIVISIONS

Re: Help me how to calculate POINTS and DIVISIONS

Re: Help me how to calculate POINTS and DIVISIONS

Re: Help me how to calculate POINTS and DIVISIONS

Re: Help me how to calculate POINTS and DIVISIONS

Re: Help me how to calculate POINTS and DIVISIONS

Re: Help me how to calculate POINTS and DIVISIONS

Re: Help me how to calculate POINTS and DIVISIONS

Re: Help me how to calculate POINTS and DIVISIONS

Re: Help me how to calculate POINTS and DIVISIONS

Re: Help me how to calculate POINTS and DIVISIONS

Re: Help me how to calculate POINTS and DIVISIONS

Re: Help me how to calculate POINTS and DIVISIONS

Re: Calculate POINTS and DIVISIONS

Re: Calculate POINTS and DIVISIONS

How to deal with two or more sheets

Re: Calculate POINTS and DIVISIONS

Re: Calculate POINTS and DIVISIONS

Re: Calculate POINTS and DIVISIONS

Posting problem

How to deal with two or more sheets

Thread Information

Users Browsing this Thread

Similar Threads

Calculate two break even points

Calculating points and divisions of students scores in excel

Calculating points and divisions of students scores in excel

Calculate hi-points

Use a % to calculate points above or below that %

Bookmarks

Bookmarks

Posting Permissions