Forum Discussion
IFS function not working
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?