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