+ Reply to Thread
Results 1 to 6 of 6

Taking credits logic into account to find weighted grade

  1. #1
    Registered User
    Join Date
    01-22-2018
    Location
    West Yorkshire, England
    MS-Off Ver
    2016
    Posts
    8

    Cool Taking credits logic into account to find weighted grade

    Hello,

    How can i work out a final mark from these grades taking into account credits.

    Each year has 120 credits, but only the top 100 count.
    So if there are 6 modules in a year and 5 are marked at 75% and the other at 72% you would drop the full module grade.
    However, if this module was still worth 30 you would have to keep 10 credits worth of the marks.

    An example of the data can be seen below.

    Capture.PNG

    What would be the best way to approach this, with a formulae?
    In the end i would want a grade for all 3 years.


    Thank you,

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Taking credits logic into account to find weighted grade

    I think the best way is

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    01-22-2018
    Location
    West Yorkshire, England
    MS-Off Ver
    2016
    Posts
    8

    Re: Taking credits logic into account to find weighted grade

    Hello,

    thank you for your time.

    I think i have uploaded the file

    Harvey
    Attached Files Attached Files

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Taking credits logic into account to find weighted grade

    with your example:
    you can add helper column: =PRODUCT(F4,G4)/100 then use PivotTable
    or use PowerQuery without helper column
    or PowerPivot without helper column
    all is included
    Last edited by sandy666; 06-24-2018 at 07:16 PM.

  5. #5
    Registered User
    Join Date
    01-22-2018
    Location
    West Yorkshire, England
    MS-Off Ver
    2016
    Posts
    8

    Cool Re: Taking credits logic into account to find weighted grade

    Hi Sandy666,

    Thank you for your time. but im afraid this is not what i was after.
    The end data already accounted for the minus 20 credits for each year.

    However I gave it another try myself and came up with a very sloppy solution. I also learnt from your power query steps to create the final product.

    I've attached what I was able to come up with. Does this help you understand better what i was trying to do?

    Ideally i want to learn how to undertake all these steps in one PowerQuery step

    Maybe one day :D

    If you have any further suggestions let me know.
    Thanks again for your time.
    Attached Files Attached Files
    Last edited by Harveyhirst1; 06-24-2018 at 07:41 PM. Reason: add attachment

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Taking credits logic into account to find weighted grade

    I did what I saw in your example as result

    One thing you need to know: PQ works with columns and content not with single cells. PivotTable and PowerPivot also. (say: with Tables)

    maybe someone else will give a formula solution
    Last edited by sandy666; 06-24-2018 at 08:13 PM.

+ 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. [SOLVED] Vba code to match multiple debits and credits by account number
    By TheRock_82 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-12-2015, 05:17 PM
  2. [SOLVED] Lookup Account Name. Sum Debits and Credits to get Balance
    By NoExcelSkills17 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-13-2015, 03:09 PM
  3. Average of weighted letter grade.
    By nico8492 in forum Excel General
    Replies: 2
    Last Post: 01-27-2015, 05:12 AM
  4. [SOLVED] Find the discount taking into account the qty ordered
    By carmelindazzz in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-18-2014, 03:41 AM
  5. Find Min Time taking 24hrs into account
    By kiktuo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-24-2014, 09:06 PM
  6. Replies: 0
    Last Post: 07-15-2013, 12:00 PM
  7. Excel 2007 : Weighted Grade Sheet Help
    By SiGhKoTiK in forum Excel General
    Replies: 0
    Last Post: 03-21-2012, 01:24 PM

Tags for this Thread

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