- last edited on
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.
01-24-2020 02:56 AM
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.
01-27-2020 12:48 AM
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.
01-27-2020 12:59 AM
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.
01-27-2020 06:57 AM
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.
10-15-2020 08:32 AM
10-15-2020 08:50 AM - edited 10-15-2020 08:57 AM
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.