excel masters

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 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 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.

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 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.

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*.

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?).

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?