Home |
Search |
Today's Posts |
#1
|
|||
|
|||
countif text values to represent different numerical values
Hi all,
I have an excel sheet with grades in ie A,B,C etc, but some have P for pass, M for merit etc. My query is after using the countif formula to total how many grades a list of students have achieved (that is that the cells with something in have been totalled), is there a way of making some of the values added in the countif function have a different numerical weighting. eg A,B,C etc all equal 1 as they are GCSE quals, however the P and M used are from different quals some have carry a weighting of 2, others carry a value of 0.5 etc eg- if a students has a result of 8 quals but 5 were gcses (each worth 1 qual), 2 were btec (each worth 2 quals) and 1 was a short coures(worth 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 column under the header (which is the name of the subject). Can anyone help? Regards Paul |
#2
|
|||
|
|||
Quote:
|
#3
|
|||
|
|||
I have attached a dummy copy in a zip.
It is the second last column i need help with, all the totals etc are at the bottom. I hope its self explanatory. Cheers Paul |
#4
|
|||
|
|||
Quote:
see attachement Paul |
#5
|
|||
|
|||
Quote:
=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 & D (one in each). These could be any cells anywhere... I just picked those because they were nearby and empty. |
#6
|
|||
|
|||
Quote:
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 |
#7
|
|||
|
|||
Quote:
=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 the 'value if false'. So in plain English the formula says, "if a count of the number of times the value in T4 appears in AX4, AX5 and AX6 is greater than zero, show the value in T2, otherwise show zero." Does that explain it for you? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting text to numerical values | New Users to Excel | |||
text and numerical values in two columns | Excel Discussion (Misc queries) | |||
how do i program alphabet letters to represent numerical values? | Excel Discussion (Misc queries) | |||
text has numerical values | Excel Worksheet Functions | |||
Copying Text vs. Numerical Values | Excel Programming |