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 3asort 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
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.
Originally Posted by 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
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
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?
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
[email protected]
Replace@mailinator with @tiscali.co.uk
"London" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> 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
>
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" <[email protected]> wrote in message
news:[email protected]...
> 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
[email protected]
Replace@mailinator with @tiscali.co.uk
>
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
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
<[email protected]> wrote in message
news:[email protected]...
>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
[email protected]
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
[email protected]
Replace@mailinator with @tiscali.co.uk
"London" <[email protected]> wrote in
message news:[email protected]...
>
> 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!
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
[email protected]
Replace@mailinator with @tiscali.co.uk
"London" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
<[email protected]> wrote in message
news:[email protected]...
>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
[email protected]
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
"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> 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
[email protected]
Replace@mailinator with @tiscali.co.uk
>
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.
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
[email protected]
Replace@mailinator with @tiscali.co.uk
"London" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> 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
>
<[email protected]> wrote in message
news:[email protected]...
>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
[email protected]
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
[email protected]
Replace@mailinator with @tiscali.co.uk
"London" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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
"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> 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
[email protected]
Replace@mailinator with @tiscali.co.uk
>
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.
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
[email protected]
Replace@mailinator with @tiscali.co.uk
"London" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> 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
>
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
[email protected]
Replace@mailinator with @tiscali.co.uk
"London" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> 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
>
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.
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
[email protected]
Replace@mailinator with @tiscali.co.uk
"London" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> 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
[email protected]
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
<[email protected]> wrote in message
news:[email protected]...
>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
[email protected]
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
"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> 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
[email protected]
Replace@mailinator with @tiscali.co.uk
>
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.
<[email protected]> wrote in message
news:[email protected]...
>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
[email protected]
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
[email protected]
Replace@mailinator with @tiscali.co.uk
"London" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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
[email protected]
Replace@mailinator with @tiscali.co.uk
"London" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> 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
>
"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> 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
[email protected]
Replace@mailinator with @tiscali.co.uk
>
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
[email protected]
Replace@mailinator with @tiscali.co.uk
"London" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> 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
>
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.
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
<[email protected]> wrote in message
news:[email protected]...
>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
[email protected]
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
[email protected]
Replace@mailinator with @tiscali.co.uk
"London" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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
[email protected]
Replace@mailinator with @tiscali.co.uk
"London" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
<[email protected]> wrote in message
news:[email protected]...
>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
[email protected]
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
"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> 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
[email protected]
Replace@mailinator with @tiscali.co.uk
>
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.
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
[email protected]
Replace@mailinator with @tiscali.co.uk
"London" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> 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
>
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
[email protected]
Replace@mailinator with @tiscali.co.uk
"London" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> 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
>
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" <[email protected]> wrote in message
news:[email protected]...
> 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
[email protected]
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
<[email protected]> wrote in message
news:[email protected]...
>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
[email protected]
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
[email protected]
Replace@mailinator with @tiscali.co.uk
"London" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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
[email protected]
Replace@mailinator with @tiscali.co.uk
"London" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
<[email protected]> wrote in message
news:[email protected]...
>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
[email protected]
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
"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> 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
[email protected]
Replace@mailinator with @tiscali.co.uk
>
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.
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
[email protected]
Replace@mailinator with @tiscali.co.uk
"London" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> 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
>
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
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
[email protected]
Replace@mailinator with @tiscali.co.uk
"London" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> 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
[email protected]
Replace@mailinator with @tiscali.co.uk
>
<[email protected]> wrote in message
news:[email protected]...
>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
[email protected]
Replace@mailinator with @tiscali.co.uk
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.
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
[email protected]
Replace@mailinator with @tiscali.co.uk
"London" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> 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
>
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.
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
[email protected]
Replace@mailinator with @tiscali.co.uk
"London" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> 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
[email protected]
Replace@mailinator with @tiscali.co.uk
>
<[email protected]> wrote in message
news:[email protected]...
>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
[email protected]
Replace@mailinator with @tiscali.co.uk
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
[email protected]
Replace@mailinator with @tiscali.co.uk
"London" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> 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
>
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
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
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
[email protected]
Replace@mailinator with @tiscali.co.uk
"London" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
<[email protected]> wrote in message
news:[email protected]...
>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
[email protected]
Replace@mailinator with @tiscali.co.uk
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.
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
[email protected]
Replace@mailinator with @tiscali.co.uk
"London" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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
[email protected]
Replace@mailinator with @tiscali.co.uk
"London" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> 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
>
<[email protected]> wrote in message
news:[email protected]...
>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
[email protected]
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
"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> 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
[email protected]
Replace@mailinator with @tiscali.co.uk
>
<[email protected]> wrote in message
news:[email protected]...
>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
[email protected]
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
"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> 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
[email protected]
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
[email protected]
Replace@mailinator with @tiscali.co.uk
"London" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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.
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
[email protected]
Replace@mailinator with @tiscali.co.uk
"London" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> 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
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks