Work out an average that converts a number with a letter to a numeric value

halbert

New Member
Joined
Feb 1, 2014
Messages
8
Formula solution to do the following:

In cell AB:5 I want to work out how many levels of progress each pupil is making ( I have shown the levels below), to work this out I need to take the value from cell Q5 away from the value in cell I5 to return a numerical value. For example, as the pupil got a 3a in cell I5 and then a 4c in cell Q5 he/she has made 1 sub level of progress, I want this to show as 0.5 and if he/she moved from a 3a to a 4b it would be 1.0 and then from a 3a to a 4a would be 1.5 and so on.

Can I then do the same in cell AB:5 but take the average of the comparison from I5 and Q5, J5 and R5, K5 and S5, L5 and T5, M5 and U5, N5 and V5, O5 and W5, P5 and X5 to show what average levels of progress the pupil is making. Can the formula also work with cells in that row being empty?

Levels work as follows:

2c
2b
2a
3c
3b
3a
4c
4b
4a
up to 8a

So every time a pupil for example moves from a 2c to a 2b this is a sub level and from 2c to 3c would be one whole level. I want each sub level to be represented as a numerical value of 0.5 so one whole level would represent 1.5. Is this possible? Any help would be greatly appreciated as I have been racking my brains for days now
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Maybe something like this

Array formula
=0.5*SUM(IF($Q5:$X5<>"",MATCH($Q5:$X5,$AA$2:$AA$22,0)-MATCH($I5:$P5,$AA$2:$AA$22,0)))/COUNTA($Q5:$X5)

confirmed with Ctrl+Shift+Enter, not just Enter

where in column AA you have the grades like this



AA

1

Grades​

2

2c​

3

2b​

4

2a​

5

3c​

6

3b​

7

3a​

8

4c​

9

4b​

10

4a​

11

5c​

12

5b​

13

5a​

14

6c​

15

6b​

16

6a​

17

7c​

18

7b​

19

7a​

20

8c​

21

8b​

22

8a​

<TBODY>
</TBODY>


Hope this helps

BTW, could you be more specific about

Can the formula also work with cells in that row being empty?

M.
 
Upvote 0


Hi Rick,

Thanks for the quick response, the formula identified by yourself below works, but, is it possible for me to show my pupils progress as a numerical value instead of a "Y" or "N". therefore comparing the "current working level" to their "Target level". So, for example, if Pupil A has a working level of a 4c and has a target level of 4a this would be -1.0 as it is 2 sub levels lower than the target level. But on the other hand if Pupil B has a working level of a 4a and a target level of a 4a they would be 1.5 as they have made 3 sub levels (1 level) of progress.


=IF(LEFT(C2)&(101-CODE(RIGHT(C2)))>=LEFT(B2)&(101-CODE(RIGHT(B2))),"Y","N")
 
Upvote 0
You are welcome. Glad for helping :)

M.

Just a quick question, instead of showing the progress as 0.5, 1, 1.5 and so on could I show it as 0.3 for each sub level? So, for example if Pupil A was working had a 3a then moved up to a 4b this would be 0.6 and if Pupil B got a 3a then moved up to a 4a this would be 0.9 ( can I round that up to 1, to show 1 full level) and still have it showing the average from the cells mentioned. Thanks again
 
Upvote 0
Just a quick question, instead of showing the progress as 0.5, 1, 1.5 and so on could I show it as 0.3 for each sub level? So, for example if Pupil A was working had a 3a then moved up to a 4b this would be 0.6 and if Pupil B got a 3a then moved up to a 4a this would be 0.9 ( can I round that up to 1, to show 1 full level) and still have it showing the average from the cells mentioned. Thanks again

Replace 0.5 by 0.3 in the formula above (#3)

M.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top