Student Attainment Levels

[backspace]

I would really appreciate some help in creating an Excel 2003 spreadsheet, that will automatically create 3 additional 'attainment levels' based on the 'start level'.

In cell "A1" - Student's Name.

In cell "A2" - Their start level.

Based on the value in cell "A2", I need to automatically create 3 additional

attainment levels in the adjacent cells. Each value given by the level in the

cell to the left.

Name - 1a - 1c* - 2b* - 3a* (* new grades)
Name - 1b - 2a* - 2c* - 3b*
Name - 1c - 2b* - 3a* - 3c*
Name - 2a - 2c* - 3b* - 4a*
Name - 2b - 3a* - 3c* - 4b*
Name - 2c - 3b* - 4a* - 4c*
Name - 3a - 3c* - 4b* - 5a*
Name - 3b - 4a* - 4c* - 5b*
Name - 3c - 4b* - 5a* - 5c*
Name - 4a - 4c* - 5b* - 6a*
Name - 4b - 5a* - 5c* - 6b*
Name - 4c - 5b* - 6a* - 6c*
Name - 5a - 5c* - 6b* - 7a*
Name - 5b - 6a* - 6c* - 7b*
Name - 5c - 6b* - 7a* - 7c*
Name - 6a - 6c* - 7b* - 8a*
Name - 6b - 7a* - 7c* - 8b*
Name - 6c - 7b* - 8a* - 8c*
Name - 7a - 7c* - 8b*
Name - 7b - 8a* - 8c*
Name - 7c - 8b*
Name - 8a - 8c*
Name - 8b
Name - 8c

Many thanks to the team for a solution on this.

Regards

Paul

 

[jbeaucaire]

Since it's all organized, I would put the reference of Attainment levels in an empty column for reference. I chose column I, then a simple lookup formula can find the level entered in B1 and give answers in C1, D1, E1. I extended your list so that it would provide 0 answers beyond the end of the levels. Then I went into Tools > Options > View > [ ] Zero values and set this particular sheet to not display zero values. That way the zeros you see here don't show in your final worksheet.

The formula shown in C1 is copied down/across as needed.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Name	1a	1c	2b	3a				Levels
2	Name	1b	2a	2c	3b				1a
3	Name	1c	2b	3a	3c				1b
4	Name	2a	2c	3b	4a				1c
5	Name	2b	3a	3c	4b				2a
6	Name	2c	3b	4a	4c				2b
7	Name	3a	3c	4b	5a				2c
8	Name	3b	4a	4c	5b				3a
9	Name	3c	4b	5a	5c				3b
10	Name	4a	4c	5b	6a				3c
11	Name	4b	5a	5c	6b				4a
12	Name	4c	5b	6a	6c				4b
13	Name	5a	5c	6b	7a				4c
14	Name	5b	6a	6c	7b				5a
15	Name	5c	6b	7a	7c				5b
16	Name	6a	6c	7b	8a				5c
17	Name	6b	7a	7c	8b				6a
18	Name	6c	7b	8a	8c				6b
19	Name	7a	7c	8b	0				6c
20	Name	7b	8a	8c	0				7a
21	Name	7c	8b	0	0				7b
22	Name	8a	8c	0	0				7c
23	Name	8b	0	0	0				8a
24	Name	8c	0	0	0				8b
25									8c
26									0
27									0
28									0
29									0
30									0
31									0
32									0
Sheet1

 

[Steve=True]

Hi

I would think we would need your actual progressions as i assume this is a sample of what you want it to look like.

I.e.

Col A......Col B
1a goes to 1b
1b goes to 2a
7a goes to 8c
8c no progression

Then i think a formula can be written as long as the progression is defined this way.

Steve=True
www.exceldashboardtemplates.com

 

[backspace]

Many thanks for the help . . . formula worked perfectly with just a little tweak (=INDEX($I:$I, MATCH(B2,$I:$I, 1)+2) . . . otherwise brilliant.

Regards

 

[trunten]

Now you just need to get the targets in cmis/sims