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

Topic

New Reply

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!

Reply | Quote

Replies

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)

Reply | Quote

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!

Reply | Quote

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

40361-lilGal

Reply | Quote

An alternate non-array formula would be:

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

HTH,
Maud

Reply | Quote

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!

Reply | Quote