Forum Discussion
IFS and AND functions
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!
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}))
2 Replies
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!
