excel masters

bluechair84

Hi guys, anyone familiar with excel?
I'm creating a grade sheet for my classes and want to compare cells with letters (grades) in. Can I compare A to B and say that B is lower than A?
Also, I have a very large sum being used repeatedly in the cells when working out grades (it calculates a load of scores across several modules then tests it against grade boundaries). Is there anyway to have this calculation written once and then call upon it instead of having it written for every grade boundary? My codes are over three lines long because of repeating calculations in =IF statements.

Cheers.

Chunkers1980

Vlookups. Google search it, that is pretty standard stuff that loads if people want to do and pretty sure I've seen that exact example solved when looking for other things

pesky_jones

I'm ok at it, but I wouldnt say I excel



I'll get my coat

bluechair84

Chunkers1980 wrote:

Vlookups. Google search it, that is pretty standard stuff that loads if people want to do and pretty sure I've seen that exact example solved when looking for other things

The Vlookup seems to search for an item in a collum, then return a another value. I'm not sure how I could use that function to say; this student has a C, they were targetted an A, so return 'under achieved'. I'm thinking it needs to be an IF statement; if ([student grade] > [target grade] return thumbs up if ([student grade] = [target grade] return ok etc...

I've fixed my reference query by putting the code in an unused cell, defining that cell as a name then using the name instead of the formula which has dramatically reduced my code size - and updates mean I only have to change the referred to cell.

njee20

I'm sure you can do this with a combination of Indirect formulae and fixed cell references, but I'm not quite sure I understand what you're trying to do!

bluechair84

njee20 wrote:

I'm sure you can do this with a combination of Indirect formulae and fixed cell references, but I'm not quite sure I understand what you're trying to do!

The students all have target grades which we hope they will achieve. Their GCSEs are made up of modules and each is given a grade. If their most recents grades are lower than their target grade, I want the cell to return a warning so it's easy to see which students need targeting. If you're familiar with code or excel, this is the best way I can describe what I want to do:
if ([student grade] > [target grade] return 'above target' if ([student grade] = [target grade] return 'on target', if([student grade] < [target grade] return 'below target'.
The trouble is you can't compare characters like this. I'm wondering if I would have to use some kind of reference to give each letter a numerical value which would be much easier to compare (A*=5, A=4, B=3 for instance)

360

bluechair84 wrote:

njee20 wrote:

I'm sure you can do this with a combination of Indirect formulae and fixed cell references, but I'm not quite sure I understand what you're trying to do!

The students all have target grades which we hope they will achieve. Their GCSEs are made up of modules and each is given a grade. If their most recents grades are lower than their target grade, I want the cell to return a warning so it's easy to see which students need targeting. If you're familiar with code or excel, this is the best way I can describe what I want to do:
if ([student grade] > [target grade] return 'above target' if ([student grade] = [target grade] return 'on target', if([student grade] < [target grade] return 'below target'.
The trouble is you can't compare characters like this. I'm wondering if I would have to use some kind of reference to give each letter a numerical value which would be much easier to compare (A*=5, A=4, B=3 for instance)

Using a number substitution would be best, you can just use a hidden column if you want to keep everything tidy.

As for your other problem:

Also, I have a very large sum being used repeatedly in the cells when working out grades (it calculates a load of scores across several modules then tests it against grade boundaries). Is there anyway to have this calculation written once and then call upon it instead of having it written for every grade boundary? My codes are over three lines long because of repeating calculations in =IF statements.

No easy way to solve that as far as I'm aware but in my experience if you're using massive strings of nested if statements then you're doing it wrong.

The Northern Monkey

can't you just change the grades to numbers in the next cell?
Assuming your letter is in cell a1

ie: =IF(A1="A",3,IF(A1="b",2,IF(A1="c",1,"")))

then do you lower/higher calc? basically breaking down what you have already done by the sounds, but easier to manager in more than 1 cell.

as above regarding the 2nd conundrum, but there is always an easier way.... can't really help without seeing anything though.

The Northern Monkey

actually. bollox to that. Use a vlookup as chunkers mentioned.

Make yourself a tab with a lookup table similar to:

Grade A - 3
Grade B - 2
Grade C - 1

Then use vlookup to bring back the corresponding number, then do your comparison.

bluechair84

360 wrote:

Using a number substitution would be best, you can just use a hidden column if you want to keep everything tidy.

Any idea how I'd best go about defining each letter as a number? I've looked at substitution but not sure how I'd go about writing a function for to swap characters A-G for numbers.

360 wrote:

As for your other problem:

I figured it out. The problem is the sum calculates a final score from 8 cells with a complicated set of averages - so the calculation is pretty long. Their final score is then tested in nested if statements against the grade boundaries to return their score as a grade. The If statements are good, and I worked out I can place the sum in an un-used cell and call upon that (define_name) each time it's needed which is brilliant as any changes I make to it will apply to every instance it's referred to in the function. The code is now considerably smaller.

bluechair84

The Northern Monkey wrote:

ie: =IF(A1="A",3,IF(A1="b",2,IF(A1="c",1,"")))

I think with this, I'd need a new column to display the number value, then refer to that to check their grades. I could make it work.
But,

The Northern Monkey wrote:

Make yourself a tab with a lookup table similar to:

Grade A - 3
Grade B - 2
Grade C - 1

Then use vlookup to bring back the corresponding number, then do your comparison.

I'll explore this method first. Cheers guys Next is to then do a conditional thing to make the cell red if they are below grade, blue if on grade and green if above!

Lagrange

...use conditional formatting - easy.

bluechair84

I used the vlookup method in the end and got it working a treat. Very happy with it. Used conditional formatting to change the font colour to read if a student is below target.
Now, I'd like to copy and paste this code to new collumns so that students can see whether they are above, on, or below target for each module. However, when I do this, it automatically reformats the code and shifts the reference cells to new locations. I need all of the referred to cells to stay unchanged, then I will alter one of the ranges. Any tips?
Thanks all

Chunkers1980

You need to use absolutes

njee20

Highlight the cell ranges you don't want to move in the formulae and press F4, or put dollar signs in the appropriate place:

=A4+1 will change when you copy it
=$A4+1 will not change when you copy it to another column, but will you you copy down to other rows
=A$4+1 the opposite of the above
=$A$4+1 will not change no matter where you copy it.

bluechair84

njee20 wrote:

Highlight the cell ranges you don't want to move in the formulae and press F4, or put dollar signs in the appropriate place:

=A4+1 will change when you copy it
=$A4+1 will not change when you copy it to another column, but will you you copy down to other rows
=A$4+1 the opposite of the above
=$A$4+1 will not change no matter where you copy it.

Congratulations, you have won the Excel Master award 8) I've just used the dollar sign to lock my references and the sheet is working perfectly.
Thanks for everyones contributions

bartimaeus

...if you are using vlookup then make sure you define your lookup data as a named range (Insert... Name... Define) - that way you can change it without changing all the references to it, and it saves typing.

e.g. =VLOOKUP(A2,MyRange,2,FALSE)

If you make MyRange bigger all references to it will still work... if you've defined it as e.g. $E$20:$F$22 then all your cells that do the lookup will need to change if you add additional rows of reference data.

bluechair84

Bartimaeus wrote:

...if you are using vlookup then make sure you define your lookup data as a named range (Insert... Name... Define) - that way you can change it without changing all the references to it, and it saves typing.

e.g. =VLOOKUP(A2,MyRange,2,FALSE)

If you make MyRange bigger all references to it will still work... if you've defined it as e.g. $E$20:$F$22 then all your cells that do the lookup will need to change if you add additional rows of reference data.

Cheers, I didn't go down that route but I have defined ranges for other parts of the sheet. The vlookup is used in four calculations, and is referred to 6 times in each so, defining it would make future changes much simpler. Ah well, it's a section that I won't be adding new reference data to.

mcdiejdh

Hi there, if you email me I have what your looking for already I use it to compare students against the target grade and they light up red, amber or green depending on whether on target off target or at.

njee20

He's fixed it now!

bluechair84

Yes, my sheet is now basically the greatest thing to have happened to our department. Thanks anyway McD. I'd also rather learn it and know how to use it myself so I can manage future changes.

bluechair84

Dear Excel Masters, I call upon your jedi skills again!

To recap; I have one complex sum working out a mark for each student, and that mark then being compared by IF statements to the grade boundaries. Orginally, I had that complex sum written out every time I wanted to compare it to a grade which made the function huge. I then moved the sum into a 'defined' cell and called upon it rather than have it written out every time I needed it.

I have since discovered though, that the referenced cells in the 'defined' (Unit 3) formula aren't changing as it gets called upon in each new row - the formula is always referring to the students at the top. The formulas look like this:

[=IF((unit3)>=72,"A*",IF((unit3)>=64,"A",IF((unit3)>=55,"B",IF((unit3)>=46,"C",IF((unit3)>=36,"D",IF((unit3)>=27,"E",IF((unit3)>=18,"F",IF((unit3)>=9,"G"))))))))]

Unit 3 [SUM((N6+O6)/2)+((Q6+R6)/2)+((P6+S6)/2)+T6+U6]

Basically, every time Unit 3 is called upon, the result never changes. In fact, I don't think Unit 3 is recognising the cells in its formula as non are highlighted when you click it, unlike any of my other formulas...
When I evaluate the formula to see what's happening, it doesn't work out the sum step by step (unlike all other sums), instead just returning true for the first IF statement so all my students are now at A*.

paul.skibum

You get an A grade for over 64% these days - I think all my o levels would have been A's by that system!

I am pretty good with excel but I cant figure out what you are doing without seeing the cells and numbers, for that reason I am out.

bluechair84

Well, I've just sort of resolved it. It's not perfect, but better. The problem is that when I drag the grade boundary code (which refers to Unit3) down for each student, Unit3 doesn't know to change the refered cells which remain static. This is a bugger because when I copy the page to create sheets for 15 classes, I'll have to make any future amendments 15 times. I can't just change the referand (grammar?).

bails87

Use the name manager.

If it looks up the A grade boundary from cell A3 in sheet "Grades" then define that as "GradeBoundA". The use the range name rather than the cell reference in a formula. No matter where you put the formula or copy/move it around, it will always look up to the same cell.

bluechair84

Sorry, work went a bit mental there for a while, Ofsted arrived and I've only just gotten round to continuing the database's development. I can't remember what exactly I was whinging about above, but I think I've solved dragging code around with $ signs and better managing where my code is kept.
I do have another query now; I'm referencing a function which is kept in another worksheet. But the cells calling upon that function just display it, instead of using it to calculate. The code looks like this:
[=codes!B16] - each cell with this code calls upon this:
[=V6+X6+Z6] - but instead of calculating this, they just display it instead.

Taking out the [=] in the sum doesn't help.
Any ideas masters?