Please help

[Mrs Mc]

Hi i hope somebody can help me.

I have a data set that i want to group by age like this.

Under 5

Age 5 to 10

Age 11 to 15

Age 16 to 19

Age 20 to 25

what is the best way to do this please, I thought of an IF statement but I can't work it out, can someone please help me?

 

[arthurbr]

Hi
if your data layout permits, use a Pivot Table and group as required An example How to Group Data in Pivot Table (3 Different Examples)

 

[Mrs Mc]

Thank you, I am joining two datasets together first so I would like to do an IF statement if possible, do you know if that would be possible please? Thanks so much for replying

 

[FormR]

Hi, welcome to the forum! Here is one way you can try:

Book4
	A	B
1	Age	Group
2	0	Under 5
3	1	Under 5
4	2	Under 5
5	3	Under 5
6	4	Under 5
7	5	5 to 10
8	6	5 to 10
9	7	5 to 10
10	8	5 to 10
11	9	5 to 10
12	10	5 to 10
13	11	11 to 15
14	12	11 to 15
15	13	11 to 15
16	14	11 to 15
17	15	11 to 15
18	16	16 to 19
19	17	16 to 19
20	18	16 to 19
21	19	16 to 19
22	20	20 to 25
23	21	20 to 25
24	22	20 to 25
25	23	20 to 25
26	24	20 to 25
27	25	20 to 25
28	26	26+
29	27	26+
30	999	26+
Sheet1

Cell Formulas
Range	Formula
B2:B30	B2	=LOOKUP(A2,{0,5,11,16,20,26},{"Under 5","5 to 10","11 to 15","16 to 19","20 to 25","26+"})

 

[Mrs Mc]

this is amazing but the formula is a little beyond me. Is it not possible to do this with an IF statement?

 

[FormR]

Is it not possible to do this with an IF statement?

Sure it is, it's just a lot of nesting.

Book4
	A	B
1	Age	Group
2	0	Under 5
3	1	Under 5
4	2	Under 5
5	3	Under 5
6	4	Under 5
7	5	5 to 10
8	6	5 to 10
9	7	5 to 10
10	8	5 to 10
11	9	5 to 10
12	10	5 to 10
13	11	11 to 15
14	12	11 to 15
15	13	11 to 15
16	14	11 to 15
17	15	11 to 15
18	16	16 to 19
19	17	16 to 19
20	18	16 to 19
21	19	16 to 19
22	20	20 to 25
23	21	20 to 25
24	22	20 to 25
25	23	20 to 25
26	24	20 to 25
27	25	20 to 25
28	26	26+
29	27	26+
30	999	26+
Sheet1

Cell Formulas
Range	Formula
B2:B30	B2	=IF(A2<5,"Under 5",IF(A2<=10,"5 to 10",IF(A2<=15,"11 to 15",IF(A2<=19,"16 to 19",IF(A2<=25,"20 to 25","26+")))))

 

[Mrs Mc]

Thank you so much, you have helped massively