formula problem - UK national curriculum levels

[London]

Bit complicated for me... might be easier for someone tho..

I have a set of school assessement data that runs 1a 1b 1c 2a 2b 2c
etc. I have to gather together 3 codes and calculate an average. So
like someone may get 2b 2b 3b so there average might be say 3a
sort of...

I have thought about doing this:

1a = 1
1b = 2
1c = 3
2a = 4
2b = 5 etc

That will give me an average or sorts. But can i send up a worksheet so
that i can continue using the codes of 1a 1b etc?

John
London

 

[Morrigan]

Combine with a VLOOKUP() function. You can still keep your letter
grading system (1a. 1b. etc) but when you want to carry out your
calculation use VLOOKUP to return a number (1, 2, etc). At the end if
you want to transfer number back to grade, either round up or down
(depends on what you want) and use VLOOKUP again to return letter
grades.


Hope this helps.

 

[London]

yeah cheers.. i've been playing about with vlookup... but not 100% sur
how to point everything in the right place. Just a case of trail an
error

 

[Sandy Mann]

John,

To convert the assessment in, say cell F20, to the appropriate number try:

=3*(CODE(F20)-48)-(2-(CODE(UPPER(RIGHT(F20)))-65))

ie 5c will be converted to 15

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Harlan Grove]

Sandy Mann wrote...

To convert the assessment in, say cell F20, to the appropriate number try:

=3*(CODE(F20)-48)-(2-(CODE(UPPER(RIGHT(F20)))-65))

ie 5c will be converted to 15

....

Alternatively,

=3*LEFT(F20,1)-SEARCH(MID(F20,2,1),"cba")+1

which has the added advantage of catching invalid entries. If F20
contained "9x", this formula would return #VALUE! rather than 48.

 

[Sandy Mann]

Sandy Mann wrote...
...

Alternatively,

=3*LEFT(F20,1)-SEARCH(MID(F20,2,1),"cba")+1

which has the added advantage of catching invalid entries. If F20
contained "9x", this formula would return #VALUE! rather than 48.

You forgot to say - and with one fewer function call - you must be slipping
<g>

--
Regards

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Robert_Steel]

I have taken a slightly different approach to the other suggestions and
would welcome all feedback on it.

The op's aim is to take a number of grades, average them and return a
grade not a number.

Firstly define a Name with a standard list of grades
eg, Grades ={"1a","1b","1c","2a","2b","2c","3a","3b","3c","4a","4b","4c","5a","5b","5c"}

then the average grade from a range is
=INDEX(Grades,ROUND(AVERAGE(MATCH(A1:A10,Grades,0)),0)) array entered -
control shift enter

change A1:A10 to reference a list of grades

what do you think?

Cheers RES

 

[London]

lovely....

is there a simple way to explain the formula?!

also ...

I nw have a column of different codes (grades) - and i'd like to kno
how many and what % are at grade. Should I try to do this with th
returned code or on the original number (which i assume would b
easier) ?

So it could read...
% at '2b' ... 15%
% at '3c' ... 68%

etc...


Joh

 

[Sandy Mann]

I have taken a slightly different approach to the other suggestions and
would welcome all feedback on it.

The op's aim is to take a number of grades, average them and return a
grade not a number.

Firstly define a Name with a standard list of grades
eg, Grades
={"1a","1b","1c","2a","2b","2c","3a","3b","3c","4a","4b","4c","5a","5b","5c"}

then the average grade from a range is
=INDEX(Grades,ROUND(AVERAGE(MATCH(A1:A10,Grades,0)),0)) array entered -
control shift enter

change A1:A10 to reference a list of grades

what do you think?

Cheers RES

Well I don't know about anyone else but I think that it is very good and
well thought out. I've certainly learned some more.

I was checking up on this thread via Google at work and found a strange
thing happening that I have never encountered before. This is probably more
to do with Google than Excel but it
may be a problem for people reading on Web Based readers. When I copied your
grades, which looked fine in Google, and pasted into a worksheet I got:
Grades
={"1a","1b","1c","2a","2b","2c­-","3a","3b","3c","4a","4b","4c-­","5a","5b","5c"}

I don't know where the two minus signs came from but they disappeared again
when I copied from the spreadsheet and pasted into this post. In your
formula the MATCH function ended up as: MA-TCH on the worksheet.

On my 1st post in this thread the UPPER became UPP-ER when pasted into a
worksheet but in Harlans reply it showed up in the worksheet as UP-PER and
my reply pastes it as U-PPER.

In Harlan's formula the 2 in the MID function became -2 when posted.

This happened at work in XL 2002 and at home in XL97. As I said it is
almost certainly something to do with Google but at least XL highlights the
error when you paste form the net page.


Regards

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Sandy Mann]

John,

You don't indicate who's post you are replying to and as I think the both
Harlan's and Robert's formulas are better than mine I will leave them to
explain them to you.

To get a percentage of the various codes: say your codes are entered in
A1:A100, enter a list of the codes you use - say in C1:C12. format D112
as Percentage and in D1 enter the formula:
=COUNTIF($A$1:$A$100,C1)/COUNTA($A$1:$A$100) and copy down to D12

If your data will always be a fixed number of codes in Column A then you can
shorten the formula to:
=COUNTIF($A$1:$A$100,C1)/100

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[London]

thanks

actually all I did was a countif (2b for example) and use the returned
number to generate my stats. Not very clever but at least I can
understand it!