Convert range of numbers to another number

[Yamasaki450]

Hello everyone. I need some help again

I need to covert range of numbers in to another number...
In column E i would like to convert all numbers from 1 to 5 in to "0" and numbers from 6 to 10 in to "1" So numbers 1,2,3,4,5 are displayed as 0 and numbers 6,7,8,9,10 are displayed as 1

Anyone know formula for this? So i can just drag it down... Or select whole column and put it in conditional formatting function.

See picture below.

Thanks guys.

 

[kevin9999]

Assuming you only have numbers from 1 - 10 inclusive:

Book1
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	3													0
2	1													0
3	9													1
4	1													0
5	4													0
6	4													0
7	5													0
8	7													1
9	3													0
10	3													0
11	2													0
12	7													1
13	8													1
14	7													1
15	6													1
16	4													0
Sheet1

Cell Formulas
Range	Formula
R1:R16	R1	=IF(E1<6,0,1)

 

[Yamasaki450]

Thanks. It works for numbers up to 10 yes. But numbers go higher than 10... What i actually need is to display numbers 1,2,3,4,5 as "0" 6,7,8,9,10 as "1" 11,12,13,14,15 as "3" 16,19,25,30,33 as "4" and so on...

Sorry i should mention this in first place. I just need an example of formula and i will continue myself.

 

[kevin9999]

16,19,25,30,33 as "4"

I don't get that logic - I would have thought 16-20 inclusive would have been 3 based on what you wanted for 1-5 and 6-10. Assuming that was a typo on your part, see if this gives you what you're after:

Book1
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	3													0
2	1													0
3	9													1
4	1													0
5	4													0
6	4													0
7	5													0
8	7													1
9	3													0
10	3													0
11	2													0
12	7													1
13	8													1
14	7													1
15	6													1
16	4													0
17	11													2
18	25													4
19	33													6
20	29													5
21	16													3
22	17													3
23	21													4
Sheet1

Cell Formulas
Range	Formula
R1:R23	R1	=CEILING(E1-5,5)/5

 

[Yamasaki450]

Oh yes big typo. I need some sleep heh What you gave me is exactly what i need and it works like magic! I thought formula will be more complicated but you made it simple...

Thanks a lot for your time

Have a nice day.

 

[kevin9999]

Happy to help, and thanks for the feedback

 

[FormR]

FWIW, here's another one you could also try:

=INT((E33-1)/5)

 

[Yamasaki450]

FWIW, here's another one you could also try:

=INT((E33-1)/5)

Yes this gives same result. Thanks guys.