Forum Discussion
IFS Compatibility Issues with Older Excel Versions
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?
There was a mistake in the original SUMPRODUCT formula. Thank you for pointing this out.
8 Replies
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.
- 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?- 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.
