IFS and AND functions

[npucmyn]

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 · Accepted 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}))

[#3]

Thank you! Amazing!