database for education

[MrsH07]

Hello I am a teacher (not ICT) and I need to devise a database which tracks whether pupils are; not expected to meet, on track to meet, have met, or exceeding their end of year target level - I want to show this through colour so that it is easy to decipher where pupils are making progress or not.
Each pupil has a different target, across different subjects each year and they make varied levels of progress each term (semester).

As you can probably guess I am completely new to excel so I am finding it a diffciult task.
I'm thinking that I could attribute a hidden numerical value to the actual value shown in the cell and that I could then use this hidden value to dictate whether the cell is coloured red, amber, green or blue. The levels run from 2c, 2b, 2a up to 8a. I'm thinking of attributing numerical values of 8,10, 12 etc.

Is this possible or is it simply too difficult for a novice like myself?

I greatly appreciate any help you can offer

 

[Dendro]

I don't quite understand the solution you suggest. Also i don't really understand what the target level is, is this a number/grade? could you explain this further? But what you want is certainly not that advanced to do, it won't take much time to create.
What you need:
1. a table containing all the pupils (let's say column A), the level they are on now/or reached at the end of the semester (column B), the target level for each pupil (column C). This is the largest thing to do in the project, but I expect you already have a pupil list making this pretty easy.
2. When do you give the pupil these status: "not expected to meet", "on track to meet", "have met", "or exceeding their end of year target level" As the first one is quite subjective how do you determine which status the pupil gets?could we translate this into a formula?
3. Which cell do you want to color? the cell of the name of the pupil? If so select that cell and click inside the start ribbon on conditional formatting and create a new rule with the formula above with the corresponding levels.

 

[MrsH07]

Hi Dendro. thanks for replying.

The target level is a number and letter, such as 4a. Within each number (level) there are sublevels; c,b, and a (a being the highest).

I do indeed have a table containing the pupils and their levels at the moment. I couldn't paste an example of it to this thread unfortunately.

If a pupil has a target of 4a and they are assessed during the first semester as a 3a they would then be more than 2 sublevels away from the target level so we would not expect them to meet that target as we try to progress them 2 sublevels over the course of a year (which consists of 6 semesters). If they are at a 4c then they would be 'on track to meet' as it is 2 sublevels away from the target.


The cell I want to colour is the one which contains their current level.

I hope this helps. I am grateful that you took the time to respond.

 

[Dendro]

Ok, thanks for explaining.

so create in the next column the target levels( would this be calculated based on the semester he/she is in? or is there a more personalized approach and would the target lvl be calculated by taking the target level of 1 year ago and adding 2 levels to it?) I think you made an error there??:

try to progress them 2 sublevels over the course of a year (which consists of 6 semesters)

dont you mean levels? In that case the pupil has to reach one sublevel per semester? Could you confirm this if correct or say how many sublevels per semester is expected?
You should set the conditional formatting on the cell of the current level. I'll give you some code to try out while you can answer the above questions

 

[Dendro]

Hi, i created some code you could use in your rules for conditional formatting. I used the ascii code to work with the sublevels.
-color-rule for not expected to meet:

(LINKS(C5;1)-LINKS(B5;1))*3-(CODE(RECHTS(C5;1))-CODE(RECHTS(B5;1)))>2

-color-rule for on track:

2=>(LINKS(C5;1)-LINKS(B5;1))*3-(CODE(RECHTS(C5;1))-CODE(RECHTS(B5;1)))>0

-color-rule for have met:

(LINKS(C5;1)-LINKS(B5;1))*3-(CODE(RECHTS(C5;1))-CODE(RECHTS(B5;1)))=0

-color-rule for exceeding:

(LINKS(C5;1)-LINKS(B5;1))*3-(CODE(RECHTS(C5;1))-CODE(RECHTS(B5;1)))<0

 

[MrsH07]

Hi Dendro

I really am baffled by the code that you have kindly submitted. I think I am going to have to admit defeat on this particular task and just go through and colour each cell individually until I am a lot more tech savvy. Thank you so much for the time you have spent on this. You have been most helpful in helping me to discover that I really am more stupid than I thought,

I bow to your obvious knowledge and I'm sorry if I've wasted your time. Once again, many thanks

 

[Dendro]

Why give up? Let me explain what the code means and you will see it looks more complicated than it actually is. BTW i forgot to translate the above code to English and make it more general, my apologies. I will edit them after this post.
I will try to explain how i got to the code, try to follow the reasoning.

Lets start first with the first formula LEFT this will return the characters on the left of the string: in your example, take the first one(that's why there is a "1" in the formula) character on the left from target level. Lets say we take 4a as target, the formula returns "4".
The second formula is just the same but for the current level. In this example we will take 3a. The formula returns one character starting from the left, that is "3".

Now we subtract the current level from the target level ( LEFT(Targetlvl)-LEFT(Currentlvl)): so we have a difference of 1 lvl between 4a and 3a. That is correct!

But know we have a problem with the sub levels: for example 4a and 3b also gives 1lvl difference as result, which isn't correct, this has to be 1lvl and 1sublvl OR 4sub-levels(check this: 3b-3a-4c-4b-4a makes a sublevel difference between current and target). So from now on it would be easier to translate the amount of levels into sub-levels. Each level has 3 sub-levels so we only need to multiply the targetlvl and the currentlvl by 3 (remember i'm only talking about the left part of the level ie the numbers).
So we come to this code: LEFT(targetlvl)*3-LEFT(Currentlvl)*3 OR we could simplify this to this code [LEFT(targetlvl)-LEFT(Currentlvl)]*3

This was the first part of the code, which i hope is more clarified for you. However we haven't tackled the problem of the sublevels,we only substracted the levels and changed them to the sublevels.

Now we have a little problem, how do we use a,b and c in a formula and do some calculations with it? But we could translate a,b and c to numbers and for this we could use the ascii code( this is a code your PC uses to define each character on your keyboard). It so happens that the code for a is 97, b is 98, and c is represented by 99, and a formula in excel exists to translate it into that numeric value: CODE(...)now we can calculate with the characters a, b and c!

So from our total lvl we need to get those sub levels and translate them with theformula CODE(). How do we get those sublevels? The same way as we did with the levels(numbers), but with the code RIGHT(). This code does exactly the same as LEFT() but starts from the right. Again we set the amount of values we want starting from the right to 1.

We get the following code for the sublevels: RIGHT(Targetlvl)-RIGHT(currentlvl) : an example 4a and 3b will become a-b. now it's time to implement the formula code.
we get the following code: CODE(RIGHT(Targetlvl))-CODE(RIGHT(Currentlvl)): our example a-b becomes 97-98=-1

Now we subtract this part from the levels(who are translated into sub levels):

(LINKS(C5;1)-LINKS(B5;1))*3-(CODE(RECHTS(C5;1))-CODE(RECHTS(B5;1))

so let's see what happens with out example: target 4a and current 3b
(4-3)*3-(a-b)
or
(4-3)*3-(97-98)
or 3-1
or 2 , and that's exactly how much sublevels the pupil is removed from reaching the targetlevel!!!
</pre>

 

[L. Howard]

Hi MrsH07,

I hope you will reconsider your last sentence of your last post. Bowing to the obvious knowledge of the experts on this forum is what it is there for, in my opinion. If you knew what they knew, you would not have posted in the first place.

The real waste of Dendro's time is if you don't at least give Dendro a listen.

As a lower level contributor to the forum myself, I was duly impressed by Dendro's grasp of what you wanted to do, that is after a bit more explanation from you.

And I don't fully understand the clear explanation of the codes offered either, some not all.

I might also submit you may get a fully operational solution that does exactly what you want, and still not know how it all works. And if you need to make some changes to it and don't know how, you could submit back to the forum what you have and what you want changed.

Regards,
Howard

Hi Dendro

I really am baffled by the code that you have kindly submitted. I think I am going to have to admit defeat on this particular task and just go through and colour each cell individually until I am a lot more tech savvy. Thank you so much for the time you have spent on this. You have been most helpful in helping me to discover that I really am more stupid than I thought,

I bow to your obvious knowledge and I'm sorry if I've wasted your time. Once again, many thanks