IFS function not working

[Aaron Fitzgerald]

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. 

[#2]

@ApolloJFK , okay, good to know

[#3]

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

[#4]

@DamianoStella 

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

[#5]

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

Damiano

[#6]

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

[#7]

@Sergei Baklan 

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

cheers

[#8]

@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

[#9]

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

[#10]

@Sergei Baklan 

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-Oct	02-Oct	03-Oct
Restday	Thursday	Friday	Saturday
Monday	6:14	6:12	6:39

Thanks

[#11]

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")

[#12]

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

[#13]

@Sergei Baklan 

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")

[#14]

I think IFs is not supported on my excel work sheet

[#15]

@Wipsupervisor 

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

[#16]

i want this kind of formula.

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

Thanks

[#17]

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

[#18]

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. 

[#19]

@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

[#20]

@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

[#21]

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