+ Reply to Thread
Results 1 to 30 of 30

How to calculate sum of best of five out of six external marks

  1. #1
    Registered User
    Join Date
    07-03-2011
    Location
    nmh, india
    MS-Off Ver
    Excel 2013
    Posts
    23

    How to calculate sum of best of five out of six external marks

    we have six subject marks internal and external
    need formula to sum best of five subject external marks and sum of internal marks which correlate with best of five external marks

    External Marks
    ENGLISH HINDI MATHEMATICS SCIENCE SOCIAL SCIENCE COMPUTER SCIENCE (Internal marks )ENGLISH HINDI MATHEMATICS SCIENCE SOCIAL SCIENCE COMPUTER SCIENCE
    55.0 25.0 66.0 78.0 49.0 17.0 15.0 12.0 16.8 18.7 17.5 15.5

    Result we need
    78.0 66.0 55.0 49.0 25.0 273.0
    18.7 16.8 15.0 17.5 12.0 80.0


    Bestfive.jpg
    Last edited by AliGW; 04-10-2018 at 09:53 AM. Reason: Changed title added to opening post.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Need formula

    Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!

    • Use concise, accurate thread titles.
    • Your post title should describe your problem, not your anticipated solution.
    • Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
    • Responding to a request to change your thread title by doing so is mandatory.

    To change a title go to your first post, click EDIT then Go Advanced and change your title.

    No help to be offered, please, until the OP complies with this request.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: Need formula

    can I add to the request that an excel file instead of picture of your example would really help in building the formulas

  4. #4
    Registered User
    Join Date
    07-03-2011
    Location
    nmh, india
    MS-Off Ver
    Excel 2013
    Posts
    23

    Re: How to calculate sum of best of five out of six external marks

    Pl Find attachment
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: How to calculate sum of best of five out of six external marks

    For row 9 (and others) would the "best" internal marks not be ALL 20 and not 16,20,20,20,20?

  6. #6
    Registered User
    Join Date
    07-03-2011
    Location
    nmh, india
    MS-Off Ver
    Excel 2013
    Posts
    23

    Re: How to calculate sum of best of five out of six external marks

    yes best marks for internal depends on external
    Attached Files Attached Files

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: How to calculate sum of best of five out of six external marks

    In N8 drag copied to R8:

    =LARGE($B8:$G8,N$7)

    In T8 drag copied to X8:

    =INDEX($H8:$M8,,MATCH(N8,$B8:$G8,0))

    Then drag copy down.

  8. #8
    Registered User
    Join Date
    07-03-2011
    Location
    nmh, india
    MS-Off Ver
    Excel 2013
    Posts
    23

    Re: How to calculate sum of best of five out of six external marks

    Problem is for internal subjects marks all is coming 16 for all five in instate

    correct marks
    16.0 2.0 15.0 20.0 5.0

    how to solve this, problem is when all or any two subjects marks same in external subjects than internal subjects marks display first subject marks like 16 for all
    Attached Files Attached Files
    Last edited by newmake; 04-10-2018 at 10:29 AM.

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: How to calculate sum of best of five out of six external marks

    Unfortunately Ali's solution does work when there are duplicate scores e.g. row 9 (I have puzzling over this myself!!)

    And hence my question on row 9 where we have 6 values of 80 in "External" and should select (in my view) the 5 values of 20 in "Internal" not 16 and 4 x 20.

  10. #10
    Registered User
    Join Date
    07-03-2011
    Location
    nmh, india
    MS-Off Ver
    Excel 2013
    Posts
    23

    Re: How to calculate sum of best of five out of six external marks

    Yes, one 16 and 4x 20
    suppose in row 9 six scores in external are five 80 and one 50 and internal scores of first five are 12, 24, 1, 35, 44, 55

    correct scores for internal scores required 12, 24, 1, 35, 44
    Last edited by newmake; 04-10-2018 at 10:51 AM.

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: How to calculate sum of best of five out of six external marks

    Array formula in N8, copied across and down:

    =IF(SUM($B8:$G8)=0,"",LARGE($B8:$G8+COLUMN($B8:$G8)/10^10,COLUMNS($N:N)))

    Array formula in T8, copied across and down:

    =IFERROR(INDEX($H8:$M8,,MATCH(N8,($B8:$G8+COLUMN($B8:$G8)/10^10),0)),"")

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  12. #12
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: How to calculate sum of best of five out of six external marks

    Cross-posted : https://www.mrexcel.com/forum/excel-...best-five.html

    Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

  13. #13
    Registered User
    Join Date
    07-03-2011
    Location
    nmh, india
    MS-Off Ver
    Excel 2013
    Posts
    23

    Re: How to calculate sum of best of five out of six external marks

    for internal marks values is not correct
    Attachment 569503
    Attached Files Attached Files
    Last edited by newmake; 04-10-2018 at 12:34 PM.

  14. #14
    Registered User
    Join Date
    07-03-2011
    Location
    nmh, india
    MS-Off Ver
    Excel 2013
    Posts
    23

    Re: How to calculate sum of best of five out of six external marks

    Ok I was not aware and not repeat again
    https://www.mrexcel.com/forum/excel-...best-five.html

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: How to calculate sum of best of five out of six external marks

    OK. Try this out.... I added in another very small number to help make the scores unique.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    07-03-2011
    Location
    nmh, india
    MS-Off Ver
    Excel 2013
    Posts
    23

    Re: How to calculate sum of best of five out of six external marks

    In row 9 first to five score of internal marks should be 1, 2,3,4,5
    still display 20, 5, 4, 3, 2

  17. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: How to calculate sum of best of five out of six external marks

    Hahaha. i should have subtracted, not added...
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    07-03-2011
    Location
    nmh, india
    MS-Off Ver
    Excel 2013
    Posts
    23

    Re: How to calculate sum of best of five out of six external marks

    Pl see Row 9 the value of internal marks correct order should be 1,2,3,4,5

  19. #19
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: How to calculate sum of best of five out of six external marks

    It is in that order!!!

  20. #20
    Registered User
    Join Date
    07-03-2011
    Location
    nmh, india
    MS-Off Ver
    Excel 2013
    Posts
    23

    Re: How to calculate sum of best of five out of six external marks

    yes it is working and perfect thanks a lot Glenn

  21. #21
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: How to calculate sum of best of five out of six external marks

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  22. #22
    Registered User
    Join Date
    07-03-2011
    Location
    nmh, india
    MS-Off Ver
    Excel 2013
    Posts
    23

    Re: How to calculate sum of best of five out of six external marks

    when I am creating another file with similar structure formula for internal marks is not displaying blank only files which you corrected is ok other is not ?? external subj marks formula is ok

  23. #23
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: How to calculate sum of best of five out of six external marks

    Attach a non-working file here.

  24. #24
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: How to calculate sum of best of five out of six external marks

    Yes... agreed. Impossible to tell what's going on, without seeing something.

  25. #25
    Registered User
    Join Date
    07-03-2011
    Location
    nmh, india
    MS-Off Ver
    Excel 2013
    Posts
    23

    Re: How to calculate sum of best of five out of six external marks

    is it possible second formula i.e. for internal scores in VLOOKUP
    my file size is big not possible to upload/attache

  26. #26
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: How to calculate sum of best of five out of six external marks

    Don't post the whole sheet. Indeed, I don't want to see 000s of rows. Just post a little relevant data showing the problem. Without seeing it, I would be guessing and that would be pointless.

  27. #27
    Registered User
    Join Date
    07-03-2011
    Location
    nmh, india
    MS-Off Ver
    Excel 2013
    Posts
    23

    Re: How to calculate sum of best of five out of six external marks

    pl find sample file as zip
    Attached Files Attached Files

  28. #28
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: How to calculate sum of best of five out of six external marks

    Glenn asked for a small sample file displaying the problem. He explicitly said not a large file.

  29. #29
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: How to calculate sum of best of five out of six external marks

    In your file, you had selected an option to use the precision of the numbers as displayed.

    File/Options/Advanced/When calculating this workbook

    and UNCHECK "set precision as displayed".

  30. #30
    Registered User
    Join Date
    07-03-2011
    Location
    nmh, india
    MS-Off Ver
    Excel 2013
    Posts
    23

    Re: How to calculate sum of best of five out of six external marks

    Excellent
    Thanks again Glenn

+ 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. Replies: 5
    Last Post: 02-16-2018, 06:50 AM
  2. Replies: 8
    Last Post: 09-22-2017, 05:41 AM
  3. Excel formula bar to display the result of the formula , not the formula?
    By max_max in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-18-2016, 07:15 PM
  4. Replies: 11
    Last Post: 06-06-2014, 03:34 PM
  5. how to hide formula in formula box, view lookup result in formula box?
    By vengatvj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2013, 04:06 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