ExcelBanter - A range of values transformed

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - A range of values transformed (https://www.excelbanter.com/excel-programming/283037-range-values-transformed.html)

A range of values transformed

I want to make a rule that if a cell has a range of
values, it is coded a certain way. How would I do this
this?

For example,
I have a row of values
5, 79, 21, 0

With the key of
0 = 1
1-10 = 2
11-25 = 3
26-75 = 4
75 = 5

Leading to:
2,5,3,1

How would I write this with a simple excel program?
I was thinking of combining if statements, but the formula
gets too large.

PLEASE HELP!
Thank you!
Alex

A range of values transformed

Alex

try this:

=IF(A7=0,1,IF(AND(A7=1,A7<=10),2,IF(AND(A7=11,A7 <=25),3,IF(AND(A7=26,A7<=
75),4,5))))

This tests cell A7; modify to your requirements.

Regards

Trevor

"Alex" wrote in message
...
I want to make a rule that if a cell has a range of
values, it is coded a certain way. How would I do this
this?

For example,
I have a row of values
5, 79, 21, 0

With the key of
0 = 1
1-10 = 2
11-25 = 3
26-75 = 4
75 = 5

Leading to:
2,5,3,1

How would I write this with a simple excel program?
I was thinking of combining if statements, but the formula
gets too large.

PLEASE HELP!
Thank you!
Alex

I don't think this works because I tried it and all I got
back was 2's.
-----Original Message-----
Alex

try this:

=IF(A7=0,1,IF(AND(A7=1,A7<=10),2,IF(AND
(A7=11,A7<=25),3,IF(AND(A7=26,A7<=
75),4,5))))

This tests cell A7; modify to your requirements.

Regards

Trevor

"Alex" wrote in
message
...
I want to make a rule that if a cell has a range of
values, it is coded a certain way. How would I do this
this?

For example,
I have a row of values
5, 79, 21, 0

With the key of
0 = 1
1-10 = 2
11-25 = 3
26-75 = 4
75 = 5

Leading to:
2,5,3,1

How would I write this with a simple excel program?
I was thinking of combining if statements, but the
formula
gets too large.

PLEASE HELP!
Thank you!
Alex

.

Alex

it works fine ...

Put the numbers from 0 to 99 in cells A1 to A100. Then put the formula in
cell B1 and drag down.

=IF(A1=0,1,IF(AND(A1=1,A1<=10),2,IF(AND(A1=11,A1 <=25),3,IF(AND(A1=26,A1<=
75),4,5))))

0 1
1 2
2 2
3 2
4 2
5 2
6 2
7 2
8 2
9 2
10 2
11 3
12 3
13 3
14 3
15 3
16 3
17 3
18 3
19 3
20 3
21 3
22 3
23 3
24 3
25 3
26 4
27 4
28 4
29 4
30 4
31 4
32 4
33 4
34 4
35 4
36 4
37 4
38 4
39 4
40 4
41 4
42 4
43 4
44 4
45 4
46 4
47 4
48 4
49 4
50 4
51 4
52 4
53 4
54 4
55 4
56 4
57 4
58 4
59 4
60 4
61 4
62 4
63 4
64 4
65 4
66 4
67 4
68 4
69 4
70 4
71 4
72 4
73 4
74 4
75 4
76 5
77 5
78 5
79 5
80 5
81 5
82 5
83 5
84 5
85 5
86 5
87 5
88 5
89 5
90 5
91 5
92 5
93 5
94 5
95 5
96 5
97 5
98 5
99 5

Have you copied and pasted the formula and adjusted it or retyped it ?

Regards

Trevor

"Alex" wrote in message
...
I don't think this works because I tried it and all I got
back was 2's.
-----Original Message-----
Alex

try this:

=IF(A7=0,1,IF(AND(A7=1,A7<=10),2,IF(AND
(A7=11,A7<=25),3,IF(AND(A7=26,A7<=
75),4,5))))

This tests cell A7; modify to your requirements.

Regards

Trevor

"Alex" wrote in
message
...
I want to make a rule that if a cell has a range of
values, it is coded a certain way. How would I do this
this?

For example,
I have a row of values
5, 79, 21, 0

With the key of
0 = 1
1-10 = 2
11-25 = 3
26-75 = 4
75 = 5

Leading to:
2,5,3,1

How would I write this with a simple excel program?
I was thinking of combining if statements, but the
formula
gets too large.

PLEASE HELP!
Thank you!
Alex

.