Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jan 2011
    Location
    Illinois
    Posts
    62
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Returning a value in the last filled row of a particular column

    Hi, everyone. I have put together a little spreadsheet to help students answer that end-of-semester question "how many points do I need to earn in order to get a grade of (whatever grade they are striving to earn)." I also have it set to calculate their letter grade at a particular moment in time. The letter grade changes as assignments become available and students earn points.

    I want to point to the cell containing the current letter grade in a separate cell, for easy reference, at the top of the sheet. As the current grade row will change during the semester, I'm not sure how to reference it.

    In the attached sample, I want the current grade to display in cell F2. As this example stands, the current grade is D (value in L17). If the student scores 180 on the final exam, the grade should update to a C (value in L18 after the score is entered in B20). The sheet is protected so you can see how it works; there is no password to unprotect it and play with functions. All data entry will be in cells B5:B21.

    Thanks for any feedback!
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    If I understand you correctly, the F2 grade is L17 unless there is a final exam posted, then it's L18.

    In F2, does this work? =IF(ISBLANK(B20),L17,L18)

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    13ILGal,

    KW's formula will work if you want to find the last grade at the final exam point of time. If you want a formula to find the last grade at any point during the course, enter the following array formula in cell F2 then press Ctrl-shft-enter:

    =INDEX(L:L,MAX((L:L<>"")*(ROW(L:L))))

    Curly brackets will be automatically be placed around the formula.

    HTH,
    Maud

    lilGal.png

  4. The Following User Says Thank You to Maudibe For This Useful Post:

    13ILGal (2015-04-25)

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    An alternate non-array formula would be:

    =INDIRECT(ADDRESS(18-COUNTIF(L6:L18,""),12))

    HTH,
    Maud

  6. The Following User Says Thank You to Maudibe For This Useful Post:

    13ILGal (2015-04-25)

  7. #5
    Star Lounger
    Join Date
    Jan 2011
    Location
    Illinois
    Posts
    62
    Thanks
    17
    Thanked 2 Times in 2 Posts
    Maudibe, this works great, but I'm not sure I understand why. I pulled out my trusty "Excel Functions in Practice" (Blattner) to study up on these two functions.

    Here's what I understand: INDIRECT points to a cell containing a reference to another cell. The ADDRESS function is acting as the argument for the INDIRECT function.

    The ADDRESS function creates a cell address, given a row and column number. SO....18-all the blanks: 18 is the last row of my data area; you subtracted the blanks to find the current row that contains data, and 12 is the column position (L is the 12th column). This returns the current cell address that contains a letter grade.

    Wow - I think I just figured it out in rewriting it. Very cool set of functions. Thanks so much for the mini-lesson! :-)

  8. #6
    Star Lounger
    Join Date
    Jan 2011
    Location
    Illinois
    Posts
    62
    Thanks
    17
    Thanked 2 Times in 2 Posts
    Kweaver, this works if I'm only looking for the grade after the final. I was more looking for a current grade at any point in time. The INDIRECT/ADDRESS function pair should work great. Thanks for the suggestion, though!

Posting Permissions

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