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
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,405
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.
 

halbert

New Member
Joined
Feb 1, 2014
Messages
8


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")
 

halbert

New Member
Joined
Feb 1, 2014
Messages
8

ADVERTISEMENT

Thanks you so much, that was exactly what I was looking for. :)
 

halbert

New Member
Joined
Feb 1, 2014
Messages
8
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
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,405
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,915
Members
413,952
Latest member
JGer

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
Top