Forum Discussion
IFS Compatibility Issues with Older Excel Versions
There was a mistake in the original SUMPRODUCT formula. Thank you for pointing this out.
COUNTIFS($E$19:$E$700,E19,$L$19:$L$19,">"&L19)+1)
In another column, it's actually the RANK.AVG formula that results in the #NAME error. I wonder if there's an alternative to that?
I'm not totally sure that I follow that re: the helper column, but here's an example of what I'm working with. The goal is to rank schools by % poverty in two ways: first, across all schools in the district, and second only in comparison to other schools that serve the same grade span. The user enters the % of students who are low income and then the rankings happen based on that. Both of these functions produce the #NAME error for certain users.
For the district-wide rank, I think I can just use RANK. For the Grade-span rank, that's where I have the COUNTIFS. The formula is =IF($D2=0,"",COUNTIFS($C$2:$C$14,C2,$D$2:$D$14,">"&D2)+1)
D2 = the user entered value for Low income. Basically, if that is blank, I want to hide the formula.
So this grade-span rank is the one causing me the problem in particular.
School Type Grade Span % Low Income District-Wide Rank Grade-Span Rank School A Elementary P-8 77 8 1 School B High 9-12 75 10 3 School C High 9-12 76 9 2 School D High 9-12 88 4 1 School E Middle 6-8 97 1 1 School F Middle 6-8 87 5 2 School G Elementary K-5 55 12 3 School H Elementary K-2 89 3 1 School I Elementary P-5 90 2 1 School J Elementary K-5 86 6 1 School K Elementary 3-5 84 7 1 School L Elementary K-5 40 13 4 School M Elementary K-5 58 11 2 Maybe SUMPRODUCT works for all users. This formula should be available since Excel 2007.
Wow, thanks! Yes, that does seem to work pretty well. There's an issue if there's a tie, though. Example: if school C and D had the same low income percent, they both get the rank of 2 with SUMPRODUCT vs 1 with COUNTIFS. Wonder if there's a solution.
School Type Grade Span % Low Income District-Wide Rank Grade-Span Rank School A Elementary P-8 77 9 1 1 School B High 9-12 75 10 3 3 School C High 9-12 88 4 1 2 School D High 9-12 88 4 1 2