1. ## 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:

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. 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. 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. 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)
..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

5. 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. 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

=COUNT(INDIRECT(block))

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

zeddy

7. 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. As an alternative:
=COUNT(INDEX(\$L\$16:\$O\$47,0,nbr_tests_sem))

9. 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

10. 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

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. 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

12. 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.

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

13. Shorter formulas will make your workbook smaller, but not necessarily faster.

14. 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. 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 Last

#### Posting Permissions

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