Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  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

    a shorter formula

    Hi All,

    The following formula does what I want but I'm hoping someone can come up with something shorter. I tried many things but had to settle for this:

    =COUNT(INDIRECT(ADDRESS(16,INDEX(COLUMN(test_resul ts_cols),1,nbr_tests_sem),4)):INDIRECT(ADDRESS(47, INDEX(COLUMN(test_results_cols),1,nbr_tests_sem),4 )))

    What I want to do is get a count of the number of numerical entries (hence COUNT, that's easy) in 1 of 2 columns. The column to be chosen is that corresponding to the last test of the semester (as given by nbr_tests_sem, which would be 3 or 4 depending on the class).

    Test results are stored in 4 columns (3 if that class only has 3 tests per semester, with the 4th col being blank); the cols are L:O. The top of the columns (but not row 1, since there is other header info in the sheet) has the numbers 1, 2, 3, 4 to represent the tests; this horizontal set of 4 adjacent columns is named test_results_cols.

    The values to be examined are in rows 16:47 (hence the first arg to the ADDRESS BIF). The above formula gives a count of numerical entries for the column corresponding to the last test; there can be other non-numerical entries representing a withdrawal from class (as a reason for not taking the test), etc.

    What I'm also trying to do is avoid having to remember to re-edit this formula if I insert columns to the left of col L in the future.

    I will have some similar formulas examining the numerical entries in rows 16:47 in 1 of the 2 cols. In particular, I will need to see the number of numerical entries >0 (yes, people do get 0's on tests), sum the values to get an average, etc. So I was hoping that someone would have some ideas on simplifying the basic approach above before I proceed further.

    TIA

    Fred

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,813
    Thanks
    132
    Thanked 479 Times in 456 Posts
    HI Fred

    This will make it simpler:
    1. name the range [N16:N47] as block3
    2. name the range [O16:O47] as block4

    Now your count formula is simply:
    Code:
    =COUNT(INDIRECT("block"&nbr_tests_sem))
    zeddy

  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 Zeddy, Maud,

    Thks for the thoughts.

    First, all I can say is DUH to Zeddy's comments since I do exactly that in another spreadsheet totally unrelated to what I was doing now.

    As far as Maud's comment: I spent a good part of today trying an alternative that seems to work. That is, to use some "helper" cells to get intermediate results use them in some complicated formulas, but not as complicated as what I had posted. I'll try Zeddy's suggestion in my original (w/o the helper cells) and see where I get to. I'm betting that will work. But if not, I'll post back and include a sample.

    Fred

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,813
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Fred

    So, you were underwhelmed by the simplicity of my solution??
    It works of course because I created a spreadsheet following your details and then tested it!
    The advantage of naming the specific ranges as I suggested is that apart from being able to insert columns to the left of col L, you can also insert addition rows between rows 16:47 without having to remember to re-edit the formula.

    For the benefit of others, Fred's original formula essentially becomes
    =COUNTIF(INDIRECT("N16:N47") if the value of nbr_tests_sem is 3
    or
    =COUNTIF(INDIRECT("O16:O47") if the value of nbr_tests_sem is 4

    You can see this in the attached demonstration file:
    Place the cellpointer in cell [E4], and in the formula bar, highlight the part of the formula starting with the first ADDRESS and highlight up to the first ,4)
    i.e. use your mouse to highlight ADDRESS(16,INDEX(COLUMN(test_results_cols),1,nbr_t ests_sem),4)
    ..and then press the calc function key [F9]
    ..you will see a partial calculation in the formula bar which shows "N16".
    Press [Esc] to cancel the highlighted partial formula.

    In cell [E2], change the number from 3 to 4, and repeat as above to see "O16"

    zeddy
    Attached Files Attached Files

  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
    Hi Zeddy,

    Yes, I forgot to mention in my original post that a rqmt was that you have to be able to insert rows since that is always happening in this case (not the other one that I had already done but forgotten about). So INDIRECT with hard coded column letters and row numbers are not good. Inserting columns are not a big deal but you never know - that's why I wanted something that was more general.

    I wouldn't say I was underwhelmed by the simplicity of your solution - after all, I thought of it too for another application. Great minds think alike.

    I'll give your solution a whirl today and see how that works out. I already finished a solution with "helper" cells.

    Fred

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,813
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Fred

    Well here's an even shorter formula than the previous one:
    After you define named ranges block3 and block4 as mentioned before,
    in the name manager, define a new name as block
    In the Refers to:
    enter
    ="block"&nbr_tests_sem

    Now your formula becomes:
    =COUNT(INDIRECT(block))

    I only mention this as I'm sure Maud will come back with a shorter formula!

    zeddy

  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
    Zeddy,

    OK - great minds don't think alike. I only took it as far as the previous post. Clearly your new approach is even better. Almost tempted to go back to my other spreadsheet, which is for a friend, and change that one too. And I'd have plenty of time since that other spreadsheet will not be needed until late this year.

    In the interim, I can use that for my current grading spreadsheet. Don't know if I'll get to applying it today since jogging took longer than expected.

    Thanks much.

    Fred

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    As an alternative:
    =COUNT(INDEX($L$16:$O$47,0,nbr_tests_sem))
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    I decided to go with a combination of rory and zeddy's posts:

    count(index(block,0,nbr_tests_sem-2))

    where above "block" encompasses both the test 3 and test 4 columns (N:O). Somewhat similar to Rory's post; I just didn't include the first 2 cols so had to do the -2. Maybe that wasn't too elegant; non-obvious to someone reading the formula - which no one will be. Now I just have to remember in the future why I did the -2.

    Maybe not as elegant either as zeddy's last post where block would be a name with the refers to = block&nbr_test_sem. One reason I didn't go with this is that I wasn't quite sure why I needed an INDIRECT once we've defined block here. Didn't have time to experiment. May still go back to this.

    Thks all.

    Fred
    Last edited by fburg; 2015-01-20 at 09:15. Reason: "rows" in 3rd para should obviously have been "cols"

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,813
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Fred

    I liked Rory's alternative, but based on the subject matter of the oringinal post ("a shorter formula"), I believe my =COUNT(INDIRECT(block)) is the shortest so far.
    Well, it is shorter than the original
    =COUNT(INDIRECT(ADDRESS(16,INDEX(COLUMN(test_resul ts_cols),1,nbr_tests_sem),4)):INDIRECT(ADDRESS(47, INDEX(COLUMN(test_results_cols),1,nbr_tests_sem),4 )))

    Now, as for your 'remembering why I did the -2', you could either add a cell comment, or, if you really don't mind having a 'longer formula', then you can document the formula directly within the cell itself by adding a note at the end of the formula as in:
    =count(index(block,0,nbr_tests_sem-2))+N("<< the -2 is because I didn't include the first 2 rows in the range")

    zeddy

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I thought it was implicit in my post that my formula was actually a named formula called 'a' so the cell formula is actually just:
    =a
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    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 Zeddy, Rory,

    There's no doubt that anything you geniuses did would be shorter than my monstrosity - talk about brute force.

    As to commenting the formula - brilliant! I never thought to use the N function for this. I recall (altho the mind could be playing tricks on me) that a quite common request by Excel users converting from Quattro Pro (like me) was to have self documenting formulas.

    BTW Zeddy, I noticed an error in my post of last night (it was late): I referred to "rows" in my 3rd para but it should have been "cols" - I decided to use just the last 2 "cols" of the L:O range for the named range rather than all 4 as Rory had suggested. I edited my post to reflect that. But it seems like my error filtered over to your post also.

    Well you can't get any shorter than 1 character.

    But that makes me wonder - I guess you can have a name refer to another name refer to... Does that make Excel's job easier when recalc'g a sheet? I just did another spreadsheet for my friend where "shorter" could refer to really "faster". Her spreadsheet slows down just a bit (you can see the left side of the task bar "calculating") for about 1/2 to 1 sec when you update a cell. It's not a big spreadsheet (about 1.5M with about 7 sheets, none very big). So maybe we need a new thread with guidelines about "do this instead of that". I've seen people write in asking questions in this vein. My original post in this thread was aimed at that dimension of "shorter" too. On her spreadsheet, I was very concerned about this and tried a few alternatives in a few places. Nothing very scientific or rigorous but I was concerned about it.

    I know you can turn off auto-recalc but then you have to recalc manually. That's not something I would do for her.

    Fred
    Last edited by fburg; 2015-01-20 at 09:35.

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Shorter formulas will make your workbook smaller, but not necessarily faster.
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    There's no doubt in my mind that my original formula made the sheet both slower and bigger.

    One of the things that I sometimes see is that people, including myself, try to get everything in 1 formula. The alternative of using "helper" cols or rows is something I try to steer clear of, even tho that might make things faster.

    I'm sure there are other "tricks" that help.

    Fred

  15. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,813
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Rory

    I understood your hidden unspoken implicit point implied in your post#11 as it is in your inherent nature to give the best answers. And the fastest.
    Apologies for mistyping your name as roy in another post.

    eddy

Page 1 of 2 12 LastLast

Posting Permissions

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