IFS Compatibility Issues with Older Excel Versions

[lcorry]

I created a tool to run some tests. I use Microsoft 365, and some of the functions I used in my tool are apparently new. The users of my tool have older versions of excel, and they encountered #NAME errors. Based on a compatibility report, the issues are largely due to functions using IFS or COUNTIFS.

Example: IFS(B10>=B5,"0",B10<B5,TRUNC(B10-B5))

My question: is there an alternative to IFS or COUNTIFS that works with older versions of Excel? 

[#2]

@lcorry 

First recommendation would be that those users update their Excel.

IF that's not feasible for some reason, nested IF conditions can often do what IFS does more readily. Those can get really complicated.

Another way around lots of IF and IFS conditions is to use a table and VLOOKUP for alternative values. It depends on what the consequences of the various IFS conditions are.

COUNTIFS has been around for a lot longer (since Excel 2007). I'm surprised if they're still experiencing difficulties from that specifically.

[#3]

I'd love to ask everyone update their Excel! But unfortunately that's not possible. I think for many of the issues I could probably work out what I need with nested IF. I don't think the table and VLOOKUP would work though. In one column, I'm actually using COUNTIFS to produce a ranking based on certain criteria. In the formula below, column E has the criteria, and column L has the value I want to rank by. I don't think I could find a nested IF approach to this.
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?

[#4]

Without seeing exactly what you're dealing with it's hard to say. One thought: instead of building conditions into the COUNT function, use a helper column or two in conjunction with the original data, and then just a simple COUNT or COUNTA based on values in the helper column.

[#5]

@mathetes 

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

[#6]

@lcorry 

Maybe SUMPRODUCT works for all users. This formula should be available since Excel 2007.

[#7]

@OliverScheurich 

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

[#8 · Accepted solution]

@lcorry 

There was a mistake in the original SUMPRODUCT formula. Thank you for pointing this out.

[#9]

@OliverScheurich 

Ah yes, the same +1 solution as with the COUNTIFS formula I had. Thanks very much for the help!