Hello,
I have created a spreadsheet, a user told me that once he tried to change values he got a #NAME error and in front of the functions "xlfn." appears. I googled it and found out that it has to do with Excel versions, because if the version is too old it can't support some functions. I suspected that a "new" function would be HLOOKUP and IFERROR, so i deleted them but the error keeps coming. The user said he tried to open it with Excel 2016 and it still didn't work. It works perfectly fine in my MacBook though, and some user's windows laptops (but they had the newest excel version). My question is: does it only have to do with the version or is there something wrong with that users settings?
Greetings,
Stefi
It is the version. If Excel 2016 has the problem, I'd guess you are using a function that is restricted to subscription versions/Excel 2019. Why don't you ask him which cell(s) have the error?
Hi,
You can check at Microsoft's web page https://support.office.com/en-us/article/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188 when a function was introduced.
Br,
Anders
Hello Velouria and Anders,
He said that the error appears in all cells, i think the problem might be in the function IFS(), since i have used it in all cells. I'll check the link Anders 🙂
Thank you both for your replies!
Greetings,
Stefi
Why have you used IFS in every cell? IFS is not particularly efficient as it evaluates all the criteria expressions and all the value expressions no matter what. It's not like IF that only evaluates the parts it has to - i.e. either the true or false expression but never both.
Hi Velouria,
I have tried to use IF() instead of IFS() but it keeps telling me that i add too many arguments, and it seems like only IFS() work in this case. For example one cell looks like this:
IFS(B8<'Fill up & Results'!$B$15;IF(H9<2300; 64/H9;IF(H9>4000;1/(-2*LOG('Fill up & Results'!$F$5*0,001/(3,7*F9)-(5,02/H9)*LOG(('Fill up & Results'!$F$5*0,001/(3,7*F9))+(13/H9))))^2;"Error"));B8='Fill up & Results'!$B$15;"";B8>'Fill up & Results'!$B$15;"")
and depending on a value in another cell it uses the right function.
Greetings,
Stefi
Since your last two criteria are just returning "", all you need is this:
=IF(B8<'Fill up & Results'!$B$15;IF(H9<2300; 64/H9;IF(H9>4000;1/(-2*LOG('Fill up & Results'!$F$5*0,001/(3,7*F9)-(5,02/H9)*LOG(('Fill up & Results'!$F$5*0,001/(3,7*F9))+(13/H9))))^2;"Error"));"")
which should be more efficient.
Hi Velouria,
Thank you very much the help, i’ll change all the functions from IFS() to IF(). 😀
Greetings,
Stefi
