countif text values to represent different numerical values

[PSword]

Hi all,
I have an excel sheet with grades in ie A,B,C etc, but some have P fo
pass, M for merit etc.
My query is after using the countif formula to total how many grades
list of students have achieved (that is that the cells with something i
have been totalled), is there a way of making some of the values adde
in the countif function have a different numerical weighting. eg A,B,
etc all equal 1 as they are GCSE quals, however the P and M used ar
from different quals some have carry a weighting of 2, others carry
value of 0.5 etc

eg- if a students has a result of 8 quals but 5 were gcses (each worth
qual), 2 were btec (each worth 2 quals) and 1 was a short coures(wort
0.5 quals) the total quals for this students should be 9.5 and not 8.

I have the value for each qual at the top of each indervidual colum
under the header (which is the name of the subject).

Can anyone help?

Regards Pau

 

[PSword]

Could you post a sample workbook with dummy data included?

I have attached a dummy copy in a zip.

It is the second last column i need help with, all the totals etc are a
the bottom. I hope its self explanatory.
Cheers Pau

+-------------------------------------------------------------------
|Filename: qual totals 2012.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=651
+-------------------------------------------------------------------

 

[PSword]

I have attached a dummy copy in a zip.

It is the second last column i need help with, all the totals etc are a
the bottom. I hope its self explanatory.
Cheers Paul

I think i have done it! but had to use hidden columns rather than do th
whole thing in one go.
see attachement
Pau

+-------------------------------------------------------------------
|Filename: yr11 nov 2012 formula test.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=654
+-------------------------------------------------------------------

 

[Spencer101]

I think i have done it! but had to use hidden columns rather than do th
whole thing in one go.
see attachement
Paul

A slightly more efficient formula would be:

=IF(COUNTIF($AX$4:$AX$6,T4)>0,T$2,0

in cell AF4 and copied down and to the right.

In order for it to work, cells AX4 to AX6 contain the letter P, M &
(one in each). These could be any cells anywhere... I just picked thos
because they were nearby and empty

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[PSword]

A slightly more efficient formula would be:

=IF(COUNTIF($AX$4:$AX$6,T4)>0,T$2,0

in cell AF4 and copied down and to the right.

In order for it to work, cells AX4 to AX6 contain the letter P, M &
(one in each). These could be any cells anywhere... I just picked thos
because they were nearby and empty.

Nice one that works really well cheers, I must be honest i dont totall
understand it all, but works and that all that counts.

I can follow it up to this bit, >0,T$2,0)
I get the T$2 bit but not sure why 0 is needed either side.

Thanks again for all you hel

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Spencer101]

Nice one that works really well cheers, I must be honest i dont totall
understand it all, but works and that all that counts.

I can follow it up to this bit, >0,T$2,0)
I get the T$2 bit but not sure why 0 is needed either side.

Thanks again for all you help

=IF(COUNTIF($AX$4:$AX$6,T4)>0,T$2,0)
=IF(Condition,ValueIfTrue,ValueIfFalse)

The first 0 is part of the IF statement condition. The second is th
'value if false'.

So in plain English the formula says, "if a count of the number of time
the value in T4 appears in AX4, AX5 and AX6 is greater than zero, sho
the value in T2, otherwise show zero."

Does that explain it for you

+-------------------------------------------------------------------
+-------------------------------------------------------------------