1. problem with array formula

Hi All,

I'm having trouble with the following array formula - hoping someone can see what probably is an obvious oversight.

Code:
`{=SUM(IF( (MATCH(O11:O45,A48:A61,0)>MATCH(passing_grade,A48:A58,0)) * (MATCH(O11:O45,A48:A61,0)<=MATCH("F",A48:A58)) * (P11:P45>=nbr_times_absent_fail),1,0) )}`
See below on why I colored part of the formula red.

The arrays and named cells in the above represent:
O11:O45 is an array of student grades (one row per student)

P11:P45 is an array of #student absences (one row per student)

A48:A61 is an array of possible grade entries - A, A-,...C, C-, D+, D, F, Inc, withdrew, too early (the last 3 are not really grades but are possible entries)

passing_grade is whatever the passing grade is for the class; usually C but can vary

nbr_times_absent_fail is 5 but can be changed

What the formula is trying to do is find out how many students got less than a passing grade BUT excluding the last 3 grade entries (Inc, withdrew, too early) AND were absent 5 or more times.

I've tried the above formula as an array formula (got #NA error) and as a regular formula (got #VALUE error).

I've tried it as a SUMPRODUCT with appropriate adjustments by turning the * into comma (got #NA error).

Tried eliminating parts of the formula (the problem seems to be in the red part since the formula works if I only keep the black part).

Tried "evaluating" the formula and using F9 for partial results - it seems fine up until the last step where the formula bar shows SUM({0;1;0...}) where ... represents another 32 1's and 0's. The last step goes to #NA.

Thinking maybe 35 entries was too many, I adjusted the formula to go only up to O25 and P25 in the respective parts. No help. Besides, Excel treated the array all as argument1, so that was of no help anyway.

And a very strange attempt/result: after selecting the entire argument1 and pressing F9 to get SUM({0;1;0...}), I added ,0 after the } to create a 2nd argument to SUM. Regardless of using a regular formula or an array formula, I got the correct result of 3.

Any ideas?

TIA.

Fred

2. This worked for me.

3. Thanks for the help but your suggestion did not work.

I was going to mention in my original post that I was using Excel 2003. So I just tried both your suggestion and my original in 2010. Both work!

So there must be some bug in 2003 that has a problem with both formulas that was fixed (don't have 2007 anymore to test). Both formulas seem like perfectly good approaches to the problem.

I also tried decreasing the range, as you did to go go O22 instead of O45. That did not help in 2003.

I also noticed you changed the range of the 2nd MATCH in each test (eg, MATCH(passing_grade,A48:A61) instead of MATCH(...:A58). That doesn't seem to be the answer either. I think the logic of the formula and the layout of the letter grades (plus the 3 possible entries of INC, withdrew, too early) could use either upper bound. But that's not the answer to the #NA error.

Fred

4. Could you attach a working example worksheet with various cases and also list the correct values for each row?

Steve

5. Hi Steve,

In trying to prepare a stripped down version of the file (13 sheets - not very big), I ran across a strange occurrence.

The array formula in question is on the last sheet, which is a summary sheet of students' test grades, etc. This is what I use for what I need to submit to the department head at the end of the semester (I cut and paste relevant columns and rows for another file which is actually what is submitted).

On the last sheet are a few summary stats just for my purposes (and not submitted to the dept). One of them uses the formula originally posted - the number of people not passing AND being absent >= 5 times.

What I was going to post to the Lounge was just the last sheet. To do that, I had to get rid of all other sheets and just substitute the actual values in the 2 critical columns mentioned in my original post: col O with the final grade (which is pulled from other places in the file) and col P with the #absences. Starting with col O, I started by copying col O to an empty column and then did a paste special | values back to col O. The problem disappeared and I was correctly told by the array formula in question that there were 3 students who had not passed and had been absent >= 5 times.

I undid the paste special and started putting in the values one by one starting from the first student. This suggests a problem with something in col O (recall my original post said that if I eliminated the red part, which dealt with col O, the array formula was fine). What I found was that there were certain rows in col O where having the formula for pulling the grade created the #NA error. In particular, there are 12 rows seemingly randomly spread across the 35 rows of students that result in the #NA if the grade is pulled with a formula; change the formula to a value and the #NA goes away.

What I'm going to try to do is check if there's some reason for these particular rows to create a problem but don't have time for more experimenting for now. A quick look at the grades and absences doesn't suggest something obvious.

If you have any thoughts on this, let me know. Also recall that whatever the problem is with these 12 rows doesn't affect Excel 2010 - using formulas throughout is fine (of course, the formula was created in the first row and then just filled down for the rest of the 34 students).

Fred

6. Hi Steve,

I've stared at the last sheet until I think Z is a passing grade. I have no clue as to why those 12 rows create an #NA if they pull the final grade with a formula.

I'm not sure what you meant by "list the correct values for each row" - the grades in col O are the correct grades for all rows as they are (either with a formula or by me putting in the constant).

Also not sure what you meant by "various cases" - the 35 rows of student grades do give various cases (there are 2 places a grade is pulled from: either an override by me as the teacher upping the grade to the next level, or the earned grade).

I also tried another experiment:
- with the 12 "critical" rows overridden to just have a constant and the other 23 rows pulling the grade with the formula, I examined the array formula (the one I originally posted) by selecting the entire contents after "SUM" in the formula bar (from the opening paren up to and including the closing paren) and then hitting F9
- with all 35 rows using a formula to pull the grade and selecting the same thing as above in the formula bar and hitting F9

As far as I can see with my aging eyes, the result is exactly the same: SUM{0;0;....0} with 3 1's in the sea of 35 values (that is, there are 3 students, as there should be, who didn't pass who were also absent >= 5 times).

As far as posting the file, I would prefer to send it privately to you. I should also mentioned there is a macro in the file based on using the CELL function.

Thanks.

Fred

7. Hi All,

I received email that Jeremy Villa responded and asked me to post the file. But I don't see his post here.

Jeremy - pls see my post #6 responding to Steve on this.

Thanks.

Fred

8. Hi All,

I finally got around to creating a sample spreadsheet to illustrate the array-formula problem I mentioned when I started this

The spreadsheet is the gradebook I use for my classes. There are only 2 sheets relevant to this problem:
- "Dept Submsn" - abbreviated DS
- "tests & grades" - abbreviated TG

Unless otherwise mentioned, all the references are to the DS sheet. The other sheets have information pulled from them for the TG
and DS sheets.

I should also mention there is a "macro" in the file that uses one of the Excel 4 functions.

The problem occurs in the DS sheet in the array formula in G52. What I am trying to do there, as the label above suggests, is find
how many students "failed" (got lower than the passing grade) AND were absent 5 times or more (number arbitrarily chosen by me).
In some classes, C is passing; in others, D is passing. I also use phrases that represent other than letter grades (see cells
A47:A60 on the DS sheet). So the array formula has to be sensitive to what is a passing grade (per cell named "passing_grade" on
the TG sheet) and ignore any "final grades" with non-letter grades (eg, INC, etc).

Col O of the DS sheet has the final grade given to the student. The TG sheet has a "proposed" final grade based solely on
converting the student's term average to a letter as shown on the TG sheet (P57:Q67). In some cases, I may override that grade. So
Col O has the final grade either from the TG sheet or from the override on the DS sheet.

As shown on the DS sheet in T3 and the explanation to the side, certain rows in Col O cause the array formula in G52 to return an
#NA error; those rows also have a red box in Col T for the corresponding row. If I set O25 to copy the grade from the TG sheet or
use the override, there is no problem (hence T25 is NOT colored red). But if I set O14 (student named "XXXX") to copy the grade
from the TG sheet or use the override, G52 becomes #NA. Any ONE row with a red box in Col T with a corresponding formula in col O
to get the grade causes G52 to become #NA. But I'll just focus my explanations on row 14 (O14).

Here are various items I've tried:
- brought file into 2010 (didn't try with 2007 since I no longer have it loaded): works fine - no problems at all. So below
explanations are for 2003.

- if I change the G52 array formula to just have a range of 1 cell (eg, G52 reads "...MATCH(O14:O14,...), G52 is fine (MATCH and
rest of formula should work on the range 11:44 for the corresponding cols). But if row 14 is included in a range of greater than
1 row, G52 becomes #NA (hence T14 is red to indicate a problematic row)

- if, as currently set, the array formula is "...MATCH(O13:O14,...), G52 is #NA

- if I change ALL problematic grades to the value they should be, rather than using a formula to get it (ie, the rows with a red box in Col T),
G52 is fine (you can see the grade value in, for example, O19 and others).

- for a row that causes a problem, if I change the corresponding grade in Col X of the TG sheet to its value, G52 is fine.

- if I change the formula in O14 to refer to the same cells as the formula in O13 (which is not logically correct), G52 is fine

- if I change the test grades on the TG sheet for student XXXX on row 15 so that that student's final grade is some other letter instead
of an A, there is no change to any of the observations above.

- I've used F9 on G52 to see the various parts of the formula (also used the Tools | Formula Auditing | Evaluate Formula). Nothing
odd there. For example, the formula, with formula on all rows in Col O, comes down to SUM({0;0;0;0}) when I set the range in G52
to be just ...MATCH(O11:O14,...). I've looked at G52 with the full range 11:44 and nothing seems odd.

In posting #5 in this thread, I observed that it seemed like it was Col O that caused the problem in G52, not the references to Col P. The above certainly supports this. Also my references to the number of problematic rows was different since I was using the gradebook from the previous semester. Unfortunately, the problem did not go away in the subsequent summer semester, which is what the attached file represents.

The above represents all of the various things I've tried to find the problem.

Any thoughts welcome!

TIA

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
•