+ Reply to Thread
Results 1 to 21 of 21

Calculate POINTS and DIVISIONS

  1. #1
    Registered User
    Join Date
    09-06-2013
    Location
    Dsm,Tanzania
    MS-Off Ver
    Excel 2007
    Posts
    53

    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.

  2. #2
    Registered User
    Join Date
    09-04-2013
    Location
    Seattle, WA, USA
    MS-Off Ver
    Excel 2013, Excel 2016
    Posts
    10

    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?
    Get Excel tips and tutorials at ExcelTactics.com

  3. #3
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    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
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  4. #4
    Registered User
    Join Date
    09-06-2013
    Location
    Dsm,Tanzania
    MS-Off Ver
    Excel 2007
    Posts
    53

    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.

  5. #5
    Registered User
    Join Date
    09-06-2013
    Location
    Dsm,Tanzania
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Help me how to calculate POINTS and DIVISIONS

    Hi,
    Thanks so much for this formula. However, this could not work well.
    Last edited by nkhondeya@gmail.com; 09-12-2013 at 03:59 PM.

  6. #6
    Registered User
    Join Date
    09-22-2009
    Location
    Cornwall, England
    MS-Off Ver
    Excel 2010 (Work) O365Home (Home)
    Posts
    94

    Re: Help me how to calculate POINTS and DIVISIONS

    try the attachment
    Attached Files Attached Files

  7. #7
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    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

  8. #8
    Registered User
    Join Date
    09-22-2009
    Location
    Cornwall, England
    MS-Off Ver
    Excel 2010 (Work) O365Home (Home)
    Posts
    94

    Re: Help me how to calculate POINTS and DIVISIONS

    Quote Originally Posted by XOR LX View Post
    "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

  9. #9
    Registered User
    Join Date
    09-06-2013
    Location
    Dsm,Tanzania
    MS-Off Ver
    Excel 2007
    Posts
    53

    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
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    09-06-2013
    Location
    Dsm,Tanzania
    MS-Off Ver
    Excel 2007
    Posts
    53

    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.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    09-22-2009
    Location
    Cornwall, England
    MS-Off Ver
    Excel 2010 (Work) O365Home (Home)
    Posts
    94

    Re: Help me how to calculate POINTS and DIVISIONS

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

  12. #12
    Registered User
    Join Date
    09-06-2013
    Location
    Dsm,Tanzania
    MS-Off Ver
    Excel 2007
    Posts
    53

    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.

  13. #13
    Registered User
    Join Date
    09-06-2013
    Location
    Dsm,Tanzania
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Help me how to calculate POINTS and DIVISIONS

    Its so good. Big up!
    Regards

  14. #14
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    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.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  15. #15
    Registered User
    Join Date
    09-06-2013
    Location
    Dsm,Tanzania
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Calculate POINTS and DIVISIONS

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

  16. #16
    Registered User
    Join Date
    09-06-2013
    Location
    Dsm,Tanzania
    MS-Off Ver
    Excel 2007
    Posts
    53

    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
    Attached Files Attached Files
    Last edited by nkhondeya@gmail.com; 09-24-2013 at 04:10 PM. Reason: To attach file

  17. #17
    Registered User
    Join Date
    09-20-2013
    Location
    dar es salaam
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Calculate POINTS and DIVISIONS

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

  18. #18
    Registered User
    Join Date
    09-06-2013
    Location
    Dsm,Tanzania
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Calculate POINTS and DIVISIONS

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

  19. #19
    Registered User
    Join Date
    09-06-2013
    Location
    Dsm,Tanzania
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Calculate POINTS and DIVISIONS

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

  20. #20
    Registered User
    Join Date
    09-06-2013
    Location
    Dsm,Tanzania
    MS-Off Ver
    Excel 2007
    Posts
    53

    Posting problem

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

  21. #21
    Registered User
    Join Date
    09-06-2013
    Location
    Dsm,Tanzania
    MS-Off Ver
    Excel 2007
    Posts
    53

    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.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calculate two break even points
    By Reykjavik in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-17-2013, 02:39 PM
  2. Calculating points and divisions of students scores in excel
    By Edward Masoya in forum Excel General
    Replies: 4
    Last Post: 03-28-2013, 12:40 PM
  3. Calculating points and divisions of students scores in excel
    By Edward Masoya in forum Excel General
    Replies: 4
    Last Post: 04-08-2012, 01:45 PM
  4. Calculate hi-points
    By Kristengeyer in forum Excel General
    Replies: 7
    Last Post: 10-11-2011, 02:21 PM
  5. Use a % to calculate points above or below that %
    By james.kilgore in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-05-2011, 06:23 PM

Bookmarks

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