IFS Function not working on newest Excel 365 and MacOS

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3173012%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EIFS%20Function%20not%20working%20on%20newest%20Excel%20365%20and%20MacOS%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3173012%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EI%20am%20trying%20to%20include%20a%20IFS%20Function%20on%20my%20Sheet%20but%20it%20only%20shows%20%23NAME%3F%20I%20tried%20changing%20the%20%3B%20to%20%2C%20but%20that%20didn't%20work%20too.%20I%20included%20a%20screenshot%3CSPAN%20class%3D%22%5C%26quot%3Blia-inline-image-display-wrapper%22%20lia-image-align-inline%3D%22%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fgxcuf89792%2F%5C%26quot%3Bhttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F348684i350BF5BB7209C7D8%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%5C%26quot%3B%22%20role%3D%22%5C%26quot%3Bbutton%5C%26quot%3B%22%20title%3D%22Bildschirmfoto%202022-02-16%20um%2013.55.50.png%22%202022-02-16%3D%22%22%20um%3D%22%22%2013.55.50.png%3D%22%22%20alt%3D%22Bildschirmfoto%202022-02-16%20um%2013.55.50.png%22%20%2F%3E%26lt%3B%5C%2Fspan%26gt%3B%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3173012%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EExcel%20on%20Mac%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EFormulas%20and%20Functions%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EOffice%20365%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3173012%22%20slang%3D%22en-US%22%3EIFS%20Function%20not%20working%20on%20newest%20Excel%20365%20and%20MacOS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3173012%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20include%20a%20IFS%20Function%20on%20my%20Sheet%20but%20it%20only%20shows%20%23NAME%3F%20I%20tried%20changing%20the%20%3B%20to%20%2C%20but%20that%20didn't%20work%20too.%20I%20included%20a%20screenshot%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Bildschirmfoto%202022-02-16%20um%2013.55.50.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F348684i350BF5BB7209C7D8%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Bildschirmfoto%202022-02-16%20um%2013.55.50.png%22%20alt%3D%22Bildschirmfoto%202022-02-16%20um%2013.55.50.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3173012%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3173298%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20Function%20not%20working%20on%20newest%20Excel%20365%20and%20MacOS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3173298%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1174419%22%20target%3D%22_blank%22%3E%40Quadruple_Pawn%3C%2FA%3E%26nbsp%3BDoesn't%20work%20%2C%20this%20is%20the%20result%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Bildschirmfoto%202022-02-16%20um%2015.23.56.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F348728iE114CD712A3E8473%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Bildschirmfoto%202022-02-16%20um%2015.23.56.png%22%20alt%3D%22Bildschirmfoto%202022-02-16%20um%2015.23.56.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I am trying to include a IFS Function on my Sheet but it only shows #NAME? I tried changing the ; to , but that didn't work too. I included a screenshotBildschirmfoto 2022-02-16 um 13.55.50.png

15 Replies

@PapaJiOfficial 

=IFS(D8<$H$23,10,D8<$G$18,2,D8<$D$18,0)

If i remove the " " around the numbers 10, 2 and 0 it works in my file. 

@Quadruple_Pawn Doesn't work , this is the resultBildschirmfoto 2022-02-16 um 15.23.56.png

@PapaJiOfficial 
That could be wrong quotes, from screenshot that's not clear. Please try to open attached file and compare.

It works when I remove the " " around the numbers.

@Sergei Baklan Here are the screenshots, Bildschirmfoto 2022-02-16 um 15.23.56.pngBildschirmfoto 2022-02-16 um 17.00.50.pngstill not working

@PapaJiOfficial 
Maybe you shall use semicolons instead of commas. Perhaps you may share sample file?

Not working too

@PapaJiOfficial 

Please try to open attached file if it works in your environment

image.png

It works but in the original sheet D7 is coming from a external data connection. How can I use the formula and still use external data?

@PapaJiOfficial 

That doesn't matter how the value appeared in the cell, manually entered, by formula or that's linked value. All shall work. The only be sure be sure the linked value is number, not text, otherwise you shall work with it as with text.

It still doesn't work, I tried
out every possibility
It worked when I input =WENNS (German version) but it isn’t true for all Cells on which the formula is applied on

@PapaJiOfficial If you go to Formulas - Show Formulas and also Trace Dependents do you see anything that could be causing a conflict in these cells?  Are any of them having unexpected formatting (General instead of Number?)

@allysharp @Sergei Baklan  The formulas look ok but when I input the IFS Function, it only shows wrong solutions. As seen in the screenshots in this messageBildschirmfoto 2022-02-17 um 17.11.13.pngBildschirmfoto 2022-02-17 um 17.10.58.png

@PapaJiOfficial 

1) if possible never copy/paste formula from web or text, especially if you are not in US locale. Preferably open other file with correct formula, doesn't matter in which locale it was created - Excel automatically converts formula to your locale.

 

If you have no Excel file and not sure, check with this tool how formula shall be written in your locale Translator • Excel-Translator

 

2) I think formula shows correct result, just not one you expect. Most probably data is wrong. My guess your data is texts, not numbers. Other words "$500", not number 500 formatted as currency.

 

Perhaps something else, but without sample file that could be a very long discussion.