IFS function not working

Copper Contributor



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. 

39 Replies

@ApolloJFK , okay, good to know

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.




Could you please share your current Excel version which is File->About and looks like


@Sergei Baklan 

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,



You may try complete uninstall, that's option 2 here https://support.office.com/en-us/article/uninstall-office-from-a-pc-9dd49b83-264a-477a-8fcc-2fdf5dbf.... Uninstalling from Control Panel keeps some elements of the Office. 

@Sergei Baklan 

Thx, I'll try right away. I'll keep you posted.


@Sergei Baklan 

Thx, it worked. I had to run the Microsoft Support and Recovery Assistant a couple of time, and i succeded to remove the previous installation download the new and install it.

Now IFS works and a new "@" operator appeared in some formula.



@DamianoStella , great, glad to know you sorted this out

@Sergei Baklan 


Hi Sir,

can you help me with this ifs error







Hi sir,
can you help me with this ifs formula error,


That could be

=IFS( (BG8>7/24)*(BG8<19/24),"LT",TRUE,"P")


=IF( (BG8>=7/24)*(BG8<=19/24),"LT","P")


In Excel datetime is actually the number their date is integer part and time is decimal one. In days calculation one day is equal to 1, thus one hour is 1/24.


@Sergei Baklan 


Thanks for your response,


but seems the formula was not meet the condition

 #NAME?=_xlfn.IFS( (BG8>7/24)*(BG8<19/24),"LT",TRUE,"P")   
 LT=IF( (BG8>=7/24)*(BG8<=19/24),"LT","P")    
I think IFs is not supported on my excel work sheet


Yes, it's not supported. For such case simple IF() is even easier.

i want this kind of formula.

When time is



Sorry, I didn't catch the logic. Let say your time is 10. It's >7 (thus LT), but at the same time it's <19 (thus P). What shall be taken?

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


@Sergei Baklan HI, tried Case changing also and i am a 365 user still it's not working, And i am a beginner in excel can you suggest any other formula to get the value withthe alternate function


That's strange, IFS() shall work on 365. You always may use set of IF() instead, google "excel nested if" - lot of samples and posts.