The following formula works and gives the required value. i.e. 5, 4 & 3
IF(A1=21, 5, IF(A1=20, 4, IF(A1<20, 3, "out of range")))
I want add another 4th IF. to give a value of 2 for any number over 9 but under 15
and a 5th IF to give a value of 1 for any number over 4 but under 10
any number under 5 to result in a 0 (zero)
Can anyone help?
iMac 24″, macOS 13.3
Use IFS. It's much easier than getting tangled up with nested IFS.
=IFS(A1<5,0,A1<10,1,A1<15,2,A1<20,3,A1=20,4,A1=21,5)
If this isn't exactly what you wanted or you need to add/subtract ranges you will find the function easy to edit.
More on IFS here (or via Help > Formulas and Functions Help in your menu):
SG
Use IFS. It's much easier than getting tangled up with nested IFS.
=IFS(A1<5,0,A1<10,1,A1<15,2,A1<20,3,A1=20,4,A1=21,5)
If this isn't exactly what you wanted or you need to add/subtract ranges you will find the function easy to edit.
More on IFS here (or via Help > Formulas and Functions Help in your menu):
SG
XLOOKUP is easiest to use with an extra table, so it would be good if you have many more ranges than in your example. It is possible to use XLOOKUP in a formula without an extra table cluttering up your document, but the syntax gets complicated.
IFS is clean, succinct, straightforward, and easy to edit, and avoids the "nested ifs" tangles of the original IF function.
SG
👍 Brilliant, exactly what I wanted. Thanks
why not just use a lookup...
Jason
Thanks Jason.
I'm not familiar with the "lookup"method, how to set it up or how to use it. I will look it up as I'm interested to find out about it.
Cheers
Thanks again 👍
For my current application IFS and if are OK
Number formula function
