Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    named formula vs cell formula

    Hi All,

    I have a spreadsheet that I use for my gradebook. One of the sheets is used to record lab scores (cols B:Z) and compute the avg lab score (col AC).

    A few months ago, I decided to add a feature that would show 1 of 4 strings in col AE for a student (each students gets his/her own row going from row 13 to row 44) based on how many labs had been done and whether the latest lab score was the highest achieved by the student over the semester. I decided to implement this test by using a named formula highest_lab_msg in col AE, since I always wanted to try out this feature:

    The first part just tests if the semester is pretty close to the end (by comparing the number of labs left to do vs the number of labs needed before the "highest" msg is meaningful - I didn't need the msg if the 2nd lab was higher than the first). This is not where my problem is.

    The problem occurs in the "IF(INDEX(...COUNT(...)" part

    The problem occurred when I inserted a new col within the B:Z range (25 cols) for a new lab that I borrowed from another class to use in my class. This made the range of lab scores C:AA (25 cols) in the named formula when it should have been B:AA (26 cols). However, using the same formula in the relevant cell (AE moved to AF after inserting the col for the extra lab), rather than referring to the name highest_lab_msg, worked fine - the formula in AF adjusted to B:AA.

    Is this the way things are supposed to work? Seems like a bug to me. I have no problem getting rid of the named formula and putting the formula in the cell and filling down for all the students. Would have creating a name for B:Z helped in the named formula?

    I thought a named formula, in addition to just wanting to try out the feature, might be more efficient. When I clicked on a cell in AE over several rows and then examined the named formula, it was adjusted for the proper row (seemed neat). It also seemed to me that having the formula in the cell copied down for all the rows might be less efficient.

    I also discovered a logic problem with my formula that has nothing to do with whether it is a named formula or just in the cell. Help on this would also be appreciated.

    If a student registers at the beginning and we do all the planned labs, no problem. The INDEX(...COUNT(...)) will return the last score. But if a student registers late after a lab has been done, I fill the cell with "nl" (for "no lab") so that he/she is not penalized in his/her avg for a 0 on a lab for which they weren't present. Similarly, I might skip a lab entirely for everyone, in which case the col is blank for all rows. Never had a problem computing the avg with these conditions (the avg computation is very old and does NOT use the AVERAGE function; I still haven't figured out how Hans did it - the trick was in ignoring the lowest 2 lab scores when computing the avg).

    But under either of the 2 conditions above (an "nl" or a skipped col), the COUNT(...) gives exactly what it should: a count of the # of numeric values. This ends up being smaller than the col in which the latest lab score is recorded.

    I'm guessing I could use COUNTA(...) + COUNTBLANK(...) to get the last col but this won't work entirely right either since cols for labs not yet done are blank.

    Another thought is to keep the COUNT but add a COUNTIF to see the # cols with "nl" but I'm not sure how to account for the blank cols for labs skipped but NOT for labs not yet done (ie, those are cols beyond the currently-last col).

    Any other thoughts on how to find the last col filled in a range?

    TIA

    Fred

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I have to admit that I am a little confused by your description of the setup and formula. I work best playing with an example file to see what works and what doesn't. Could you post an example (with no personal or proprietary info) that demonstrates the problem? The detail exactly what you do and what you see happen so we can see what happens and compare to what we expect. Named formulas can be tricky, since they are relative to the active cell (per design).

    Steve

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Steve,

    I am attaching a very stripped down version of my gradebook spreadsheet. Even tho there are some #REF errors, I don't believe this affects anything regarding my questions.

    However, you may have already answered the question that I thought was a bug - the first problem I spoke about in my original post. That is, that named formulas being relative to the active cell. That could explain why the named formula shifts when I insert a col. If you insert a col as I mentioned, you'll see that the references in the name highest_lab_msg do indeed change.

    Sounds like the simple answer is to change to a cell formula.

    That still leaves the question about my logic problem.

    TIA.

    Fred
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You can "lock" the named formula on the desired cols with something like:

    =IF (nbr_labs_left>nbr_labs_for_msg, "not yet–not enough done",IF(INDEX($B13:$Z13,1,COUNT($B13:$Z13))=MAX($B13:Z1 3),IF(COUNTIF($B13:$Z13,MAX($B13:$Z13))>1,"last is tied for highest","last is highest"),"last is NOT highest"))


    Remember in your inserting you changed 2 references, the end of the range (Z to AA) and he cell with the formula (AE to AF). It was the AE to AF which affected the named formula, the other parts were NOT affected by the insert, the references remain relative to the "active cell" so since AE was shifted to AF, the referenced cols shifted from C to AA. The insert acted like you copied the formula from AE to AF.

    That is the "power" of the named formula, it remains relative (unless you lock rows or columns) to the "active cell". [It may be clearer if you looked at the cell formulas in R1C1 nomenclature since that is what they are based on. In this nomenclature, the formula is identical for all cells, and indicates that the ROW is the same as the active cell and the columns are 29 and 5 columns to the left of the active cell respectively. So when you shifted the active cell from AE to AF, 29 cols to left changed from B to C and 5 cols to left changed from Z to AA.]

    To find the last number, instead of using Count, which ignores blanks and text, or counta which ignores blanks, you can use something like
    MATCH(100000,$B13:$Z13)
    [where 100000 is just a large number greater than any possible max score]:
    =IF(nbr_labs_left>nbr_labs_for_msg,"not yet–not enough done",IF(INDEX(labs!$B13:$Z13,MATCH(1000,labs!$B13 :$Z13))=MAX(labs!$B13:$Z13),IF(COUNTIF(labs!$B13:$ Z13,MAX(labs!$B13:$Z13))>1,"last is tied for highest","last is highest"),"last is NOT highest"))

    Steve

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Thanks Steve.

    So for the first issue - the shifting of cols when I insert a col: either change to a cell formula or use abs refs for the col. I wasn't thinking of that when I decided on a named formula when I did the design and, in fact, had never done it over 6 years until this semester after creating this.

    Any idea, whether a named formula is any more efficient (storage or performance) than putting the formula in a cell and copying down?

    (One advantage of the named formula is that you change it once and you're done. With a cell formula, you have to remember to fill the formula down.)

    And thanks for the MATCH fix. I note that it does result in an #N/A initially until some numbers populate cols B:Z. That's OK with me - there are other errors when scores have not yet been entered.

    For anyone looking at your post, it should be noted that the 2nd arg of the INDEX function (refers to the row# of the array) was left out in your fix. (The MATCH should be the 3rd arg to refer to the col#.)

    Fred

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The cell formula would also have to be abs if you were going to copy it to a new column and you didn't want the cols to adjust.

    I believe a named formula is not only more efficient but takes up less memory, but unless you are using it in thousands of cells, I doubt you would see a difference.

    The MATCH will give an error until there is at least one number, it could be fixed with an IF.

    BTW, The INDEX does NOT need a row designation if the range is only 1 row (just like the column argument is not needed if the range is only 1 column). It only needs a row and column designation if the range contains multiple rows and columns. The formula I gave will work in your spreadsheet.

    Steve

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    My bad - I did recall that INDEX does work w/o an arg for row or col if there is only of that item.

    I will live with the error on the MATCH since it's only me looking at this. And I live with other errors that occur when nothing has been entered.

    Fred

Posting Permissions

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