Help with IFS statement

[Vasylian223]

Hello.
I'm currently doing a project, in which i must do something like this:

If value of a cell, let's say B4 is less than 1.5 it is "1", if it's Equal to or Greater than 1.5 BUT Less than 2.5, it is "2", if it's Equal to or Greater than 2.5 BUT Less than 3.5, it is "3"... until if it's Equal to or Higher than 4.5 but not greater than 5 then it's "5"...

The formula i would use is:
IFS(B4<=1.5,"1",B4>=1.5<2.5,"2",B4=2.5<3.5,"3",B4>=3.5<4.5,"4",B4>=4.5<5,"5")
but the formula doesn't work and it results in #N/A.
What would correct formula be for that?

 

[Fluff]

Hi & welcome to MrExcel.
You don't need to test for >=, so you can just use
=IFS(B4<=1.5,"1",B4<2.5,"2",B4<3.5,"3",B4<4.5,"4",B4<5,"5")
Although the result of the formula will be text, not numbers.

 

[Vasylian223]

Thank you, but i need, for example, "2" to be in range from 1.5 to 2.5, but not equal to 2.5, not just less than 2.5

 

[Fluff]

Have you actually tried it?

 

[RoryA]

Why don't you just round it, adding a test for any outlier conditions?

 

[Vasylian223]

Have you actually tried it?

I'm so sorry, i've tried it and it worked, though i had to add "=" here ...,B4<=5,"5") , it didn't worked in the coloumn that had 5 so that's why i thought that it didn't work. Thank you for helping me!

Why don't you just round it, adding a test for any outlier conditions?

Because it's pretty important to be in ranges like this <1.5 , >=1.5<2.5, etc...

 

[Fluff]

Rory's idea gives the same result as the IFS, depending on what should happen if it's above 5

+Fluff New.xlsm
	A	B	C
4	IFS	Round	Value
5	1	1	0.2
6	1	1	1.5
7	2	2	1.6
8	2	2	2.4
9	3	3	2.5
10	3	3	3.4
11	4	4	3.5
12	4	4	4.4
13	5	5	4.5
14	5	5	5
15	#N/A	5	5.1
Master

Cell Formulas
Range	Formula
A5:A15	A5	=IFS(C5<=1.5,"1",C5<2.5,"2",C5<3.5,"3",C5<4.5,"4",C5<=5,"5")
B5:B15	B5	=IF(C5<=1.5,1,ROUND(C5,0))