A range of values transformed

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

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
Click to expand...

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

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

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
 
Back
Top