IFS function not working

Copper Contributor

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. 

40 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.

Regards,

Damiano

@DamianoStella 

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

image.png

@SergeiBaklan 

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

@DamianoStella 

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. 

@SergeiBaklan 

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

cheers

@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.

best,

damiano

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

@SergeiBaklan 

 

Hi Sir,

can you help me with this ifs error

 

=ifs(BG8>"7:00","LT",BG8>"19:00","LT",BG8<"7:00","P",BG8>"19:00","P")

 

  #NAME? 
    
    
 01-Oct02-Oct03-Oct
RestdayThursdayFridaySaturday
Monday6:146:126:39

 

Thanks

Hi sir,
can you help me with this ifs formula error,
=ifs(BG8>"7:00","LT",BG8>"19:00","LT",BG8<"7:00","P",BG8>"19:00","P")

@Wipsupervisor 

That could be

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

or

=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.

 

@SergeiBaklan 

 

Thanks for your response,

 

but seems the formula was not meet the condition

 
 
 15:00        
 #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

@Wipsupervisor 

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

i want this kind of formula.

When time is
>7=LT
<7=P
>19=LT
<19=P

Thanks

@Wipsupervisor 

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. 

 

@Krushim 

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

image.png

@SergeiBaklan 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

@hafizsulai 

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.