Log In
Register
Home
February 20, 2019
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
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?
VIP
Trusted Members
December 7, 2016
Offline
Hi,
You can check at Microsoft's web page https://support.office.com/en-.....d90033e188 when a function was introduced.
Br,
Anders
February 20, 2019
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
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.
February 20, 2019
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
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.
1 Guest(s)
