Forum Discussion
IFS function not working
Hello,
I recently brought a spreadsheet over to a new laptop and the IFS statements that I had been using are no longer recognized. I get the #NAME? error in all cells that are currently using them.
Please advise on how to fix this. Thanks.
44 Replies
I recently installed Office 365 officially given by our university and I am trying to work in it for statistical application. I am having error #name in it if try to work with IFS function. Kindly provide help as I entered the same formula in Google sheets and it worked well.
It looks like IFS() is not available in your version of Excel.
- are you on English version? For other languages this function has another name
- could you please share what do you have under File->Account, that's like
Hi experts, I have a subscription of Office 365 (in fact my employer has). I am working on a database using the IFS function. Everything works well, I save the work and set the file aside. After a while, if I reopen the file, the formula with the IFS function is substituted with "=_xlfn.IFS((LEFT([@FR],3)="101")...".
When the sheet updates the values, then the formula is not recognised, and if I re-write the initial formula (that is "=IFS((LEFT([@FR],3)="101")...") the formula is not recognised either.
This problem arose several times. I am not sure how I fixed it. Maybe I closed excel, or restarted the computer. What is sure is that it keeps occurring.
The table I am using is of about 5200 lines and the file size is about 7MB.
I am also syncing the files with onedrive.
In the past this syncing function caused me enormous problems as instead of updating the new files I worked on, the syncing kept downloading and substituting the new with previous versions - yet this is another annoying problem.
Thank you for your assistance.
Regards,
Damiano
I kept getting "N/A" in the output.
checked the help notes
"Remarks
To specify a default result, enter TRUE for your final logical_test argument. If none of the other conditions are met, the corresponding value will be returned. In Example 1, rows 6 and 7 (with the 58 grade) demonstrate this.
If a logical_test argument is supplied without a corresponding value_if_true, this function shows a "You've entered too few arguments for this function" error message.
If a logical_test argument is evaluated and resolves to a value other than TRUE or FALSE, this function returns a #VALUE! error.
If no TRUE conditions are found, this function returns #N/A error."Since I was comparing combinations of files looking for duplicates, there were frequently no "true" result.
IFS(C4 = C3,"DUP",C4=C5,"DUP")
if neither condition is true, you get "N/A"
So:
IFNA(IFS(C4 = C3,"DUP",C4=C5,"DUP"),"")
This solved it for me. Does this help?
malcolmwri , 7-years old question was why IFS() is not available in some version of Excel. As for yiour case I'd use
=IF( (C4 = C3) + (C4=C5), "DUP", "")that is slightly better from performance point of view.
Thank you for your reply.
The compute I work on initially had a Microsoft Office Professional Plus 2016 (by the way, I had discovered that the release was installed with a pirate licence that the IT service provider - now sacked - had sold to me as authentic).
The firm I work for, opted to purchase few licences of Office 365. I installed one of the licences and everything worked well for a while.
Last week, after writing on this forum, I checked and for some reason it appeared that the version installed is MSOffice Professional Plus 2016.
I tried to switch licence, but I did not work. I tried something more drastic: I uninstalled everything (MSOffice and all other software related to that: Skype, Skype for Business, OneDrive, all spellcheck plug-ins everything that I could recognise related to MSOffice) and downloaded and installed Office 365 from the website over the weekend using the licence .
The process went through well and I got the message of a successful installation. Nevertheless, when checking, there you go, the zombie MSOffice Professional Plus 2016 is there again.
Whilst this explain why IFS does not function, reveals another problem: I can't switch to another licence and I am not able to totally remove the MSOffice Professional 2016.
I am tempted to reinitialise the entire pc, but maybe you can suggest a more focused, less drastic solution to kill the zombie once for all.
thank you,
Damiano
Please help me, my IFS function is not working.
=ifs(E13>0,"5",E13<-2.5,"4",E13<-5,"3",E13<-7.5,"2",true,"1")
why do i always get's a 4 result ?
Hi Tour,
That's separate topic, better to start new conversation in such case.
As for the function, IFS works till first TRUE condition. If E13 is -10 it's definitely less than -2.5, condition TRUE and formula returns 4/
Hey I figured it out my self after 2 hours...
I've had to switch the other way around...
=ifs(E13<-8,A$17,E13<-6,A$16,E13<-4,A$15,E13<-2,A$14,E13>0,A$13)
start from the smallest first
and it's working
- Our IT team switch me from office 365 to excel pro 2016 and I’m having these problems with existing spreadsheet that have IFS formulas. What can I do
Hi Jim,
Replace IFS on nested IF or wait for a build where IFS appears.
Dear Aaron,
IFS function is only available for Office 365 subscribers, it came to Excel with January 2016 updates for Office 365. Follow the below link:
https://support.office.com/en-us/article/IFS-function-36329a26-37b2-467c-972b-4a39bd951d45
Make sure that you have Office 365 subscription with version 1601 (Build 6568.2025) and above!
If you have in your new laptop an earlier version of Excel or Excel 2016 as a one-time purchase, it is normal to see the #NAME? instead of IFS function, because it's not defined in these versions of Excel.
- Hello Haytham,
I am using office 365 (through my office MAC laptop) and also having the same error. I am using Excel version 16.16.1Hi Fred,
The Excel team says that this function is now available in the latest update to Excel for Mac (version 16.x).
Please read this https://excel.uservoice.com/forums/304933-excel-for-mac/suggestions/14450448-add-recent-excel-windows-functions-to-mac-version.
Please make sure you have the latest Excel update and verify your Office 365 subscription.
If the problem still exists, please send feedback to Microsoft or ask this https://answers.microsoft.com/en-us as it's the best place to get support.Hope that helps
