|
formula problem - UK national curriculum levels
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 :rolleyes: 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 -- London ------------------------------------------------------------------------ London's Profile: http://www.excelforum.com/member.php...o&userid=25363 View this thread: http://www.excelforum.com/showthread...hreadid=388459 |
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 Wrote: 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 :rolleyes: 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 ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=388459 |
yeah cheers.. i've been playing about with vlookup... but not 100% sure how to point everything in the right place. Just a case of trail and error? -- London ------------------------------------------------------------------------ London's Profile: http://www.excelforum.com/member.php...o&userid=25363 View this thread: http://www.excelforum.com/showthread...hreadid=388459 |
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. |
"Harlan Grove" wrote in message
ups.com... 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. You forgot to say - and with one fewer function call - you must be slipping <g -- Regards Sandy Replace@mailinator with @tiscali.co.uk |
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 |
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 know how many and what % are at grade. Should I try to do this with the returned code or on the original number (which i assume would be easier) ? So it could read... % at '2b' ... 15% % at '3c' ... 68% etc... John -- London ------------------------------------------------------------------------ London's Profile: http://www.excelforum.com/member.php...o&userid=25363 View this thread: http://www.excelforum.com/showthread...hreadid=388459 |
wrote in message
... 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 Replace@mailinator with @tiscali.co.uk |
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 D1:D12 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 Replace@mailinator with @tiscali.co.uk "London" wrote in message ... 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 know how many and what % are at grade. Should I try to do this with the returned code or on the original number (which i assume would be easier) ? So it could read... % at '2b' ... 15% % at '3c' ... 68% etc... John -- London ------------------------------------------------------------------------ London's Profile: http://www.excelforum.com/member.php...o&userid=25363 View this thread: http://www.excelforum.com/showthread...hreadid=388459 |
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! :) -- London ------------------------------------------------------------------------ London's Profile: http://www.excelforum.com/member.php...o&userid=25363 View this thread: http://www.excelforum.com/showthread...hreadid=388459 |
| All times are GMT +1. The time now is 03:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com