SOLVED

IFS and AND functions

%3CLINGO-SUB%20id%3D%22lingo-sub-2187809%22%20slang%3D%22en-US%22%3EIFS%20and%20AND%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2187809%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20guys!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELooking%20for%20a%20help%20with%20a%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EIFS%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%3Etest_array%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22number%22%3E0%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%25%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22number%22%3E1%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EAND%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%3Etest_array%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%26gt%3B%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22number%22%3E0%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22number%22%3E1%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%25%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%3Etest_array%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%26lt%3B%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22number%22%3E9%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22number%22%3E99%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%25%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22number%22%3E2%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EAND%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%3Etest_array%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%26gt%3B%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22number%22%3E10%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%25%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%3Etest_array%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%26lt%3B%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22number%22%3E59%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22number%22%3E9%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%25%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22number%22%3E3%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%3Etest_array%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%26gt%3B%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22number%22%3E60%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%25%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22number%22%3E4%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EAs%20you%20can%20see%20at%20attached%20screenshot%20there%20is%20a%20mistake.%20Function%20stop%20working%20after%20first%20AND.%20Can%20you%20point%20out%20what%20the%20problem%20please%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EThank%20you!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2187809%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

Hello guys!

 

Looking for a help with a formula:

 

=IFS(test_array=0%, 1, AND(test_array>=0,1%, test_array<=9,99%), 2, AND(test_array>=10%, test_array<=59,9%), 3, test_array>=60%, 4)

 

As you can see at attached screenshot there is a mistake. Function stop working after first AND. Can you point out what the problem please?


p.s. test_array correspond to named range for data set. I've tried to specify not a range but single cell outcome still the same

Thank you!

2 Replies
best response confirmed by npucmyn (New Contributor)
Solution

@npucmyn 

Your screenshot appears to indicate that you use point as decimal separator, so you should use 0.1% instead of 0,1% in the formula, etc.

 

=IFS(test_array=0%, 1, AND(test_array>=0.1%, test_array<=9.99%), 2, AND(test_array>=10%, test_array<=59.9%), 3, test_array>=60%, 4)

 

I would do it as follows:

 

=IFS(test_array=0%, 1, test_array<10%, 2, test_array<60%, 3, test_array>=60%, 4)

 

or

 

=IF(test_array=0%, 1, LOOKUP(test_array, {0, 0.1, 0,6}, {2, 3, 4}))

Thank you! Amazing!