I'm trying to create a formula where if the resultant value of a cell return a value as follows
if 4=4
if <4 but greater than or equal to 3.5 = 3.5
if <3.5 but greater than or equal to 3 = 3
if <3 but greater than or equal to 2 = 2
if <2 =1
thanks
I'm trying to create a formula where if the resultant value of a cell return a value as follows
if 4=4
if <4 but greater than or equal to 3.5 = 3.5
if <3.5 but greater than or equal to 3 = 3
if <3 but greater than or equal to 2 = 2
if <2 =1
thanks
There are numerous ways to do this, but the shortest is probably:
=INDEX({1,2,3,3.5,4},MATCH(A1,{0,2,3,3.5,4},1))
Assuming your resultant value is in A1.
Probably the most obvious way, again with the value in A1 is:
=IF(A1<2,1,IF(A1<3,2,IF(A1<3.5,3,IF(A1<4,3.5,4))))
or...
=LOOKUP(A1,{0,2,3,3.5,4},{1,2,3,3.5,4})
Assuming value to lookup is in A1
- Moo
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks