• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

data analysis on sample data

T

thakur

Member
Hi Chandoo..


I have a sample data which is basically to evaluate candidates based on the score meeting criteria.


Name of the candidate, role, parameters like pronunciation, listening, vocabulary, fluency and total score column


The maximum score the candidate can get is 20 and the cut-off score is 15 to get selected in the role.


on each parameter the maximum the candidate can get is 5.


Now my question here is How do I evaluate each parameter to see which candidate has performed well on parameters, which candidate has just missed the cuttoff score and on which parameter he just met. Which parameters we need improvement on to concentrate and improve on that parameter.


Please advise..
 
Hi Narayan,


Using pivot table, if function and charts can I show the improvement areas on each parameter in the sample data based on the scores. Can you show me different ways to see improvements on the sample data provided using functions, pivots and charts or any other method to understand and analyse the data which I am currently working on to practice more..I also wanted to calculate avg of listening category using pivot table.


here is the sample data..


NAME ROLE SENTCON VOCAB FLUNCY PRONCT LISTNING TOT SCR

CAND 1 VOICE 2 3 3 2 2 14

CAND 2 VOICE 3 3 3 3 3 18

CAND 3 VOICE 2 3 3 2 2 14

CAND 4 VOICE 2 3 3 2 2 14

CAND 5 NON V 2 3 3 2 2 14

CAND 6 NON V 2 3 3 2 2 14

CAND 7 NON V 2 3 3 2 2 14

CAND 8 NON V 3 3 3 3 3 18

CAND 9 NON V 2 3 3 2 2 14

CAND 10 VOICE 3 3 3 3 3 18

CAND 11 NON V 3 3 3 3 3 18

CAND 12 VOICE 2 3 3 3 3 16
 
Hi Thakur ,


The data and its visualization is not really so complex ; my understanding is that you would like to see where each candidate falls short of the requirements ; you need to decide whether the cut-off mark in each category is the same , or is it different for each category i.e. do you want a candidate to be 4 or above in each category , or should he / she be above 4 in vocabulary and pronunciation , 5 in listening , and 3 in sentence construction and fluency ?


My suggestion would be to have an interactive bar / column chart where you can select the candidate and the chart displays his / her score in each category ; if the cut-off is the same for every category , you can have a straight line to show this. The average score can be an area chart , since the average in each category can be different ; this will show where each candidate stands in relation to the overall average.


Check the following simple chart :


https://www.dropbox.com/s/wbvzr20lfvbh03w/Thakur_Chart.xlsx


Narayan
 
Hi Narayan,


Thank you for the chart..This is one method of showing the data. However, my question How do i get details from the data if any candidate is falling short of some points to meet the cuttoff mark in all the parameters. Can I show this using pivot table...


Also get which candidate is having full points in all the parameters and find improvement and concentration areas in weaker section of the parameters mentioned in the table.


Also I wanted to create buckets of >14 and 18..so which function can I use to create these buckets from the data.. please advise..
 
Hi Narayan,


Sir, My question is How do i get details from the data if any candidate is falling short of some points to meet the cuttoff mark in all the parameters. Can I show this using pivot table...


Also get which candidate is having full points in all the parameters and find improvement and concentration areas in weaker section of the parameters mentioned in the table.


Also I wanted to create buckets of >14 and 18..so which function can I use to create these buckets from the data.. please advise..
 
Hi Thakur ,


A pivot table will summarize data , but it is still a lot of numbers ; if you want to visualize data , charts are a very good way.


As you can combine a bar / column chart and a line chart , instead of the line showing the average of each category , it can show the cut-off requirement , so that from the chart you will get an idea of whether a candidate has gone above the cut-off mark or fallen short , and if so , by how much.


You can use a second chart to show the top 5 or top 10 candidates , as well as the candidate who has got the maximum marks in each category.


Regarding your point about the buckets , I have not understood the purpose of creating these ; assuming that you have two limits of 14 and 18 , how do you want to use them ? These limits are just numbers , and depending on how you wish to use them , Excel functions will be required.


Narayan
 
Hi Narayan,


Thank you so much for clarifying. However I have bigger data sets to create bucket system to anaylise the given data. How do I create buckets of any value in excel and which function can be used to create these buckets..


Please advise.
 
Hi Thakur ,


I can only repeat myself ; 14 and 18 are just numbers !


What is it you wish to do with such numbers ? If you wish to know how many candidates fall in each slab , you can use the FREQUENCY function.


If you can upload your complete workbook , and specify everything that you wish to do , help will be forthcoming.


Narayan
 
Hi Narayan,


I tried uploading the workbook but unable to do so. Can you please tell me how to upload or attach workbook so that it will be easier for you to understand the data.


Regards,

K.K.Singh
 
Hi Singh ,


This forum does not have a provision for either uploading files or attaching files to posts.


What you can do is to use your preferred file-sharing website ( RapidShare , Hotfile , DropBox , SkyDrive , GoogleDocs , SpeedyShare ... ) to upload your file , give others permission to access and download the file , and then post that access link here , in this same topic.


Narayan
 
Hi Narayan,


I am providing the link which has sample data


https://www.dropbox.com/sh/tovdou4mt5hgrfe/KD878psFt2


The total score the candidate can score is 30

cutoff scr is 25


1. I wanted to create three buckets


bucket 22, bucket >=24 and bucket 25


2. How do I create "Biggest contributor" performed by the candidate in all the parameters?


3. Which parameter a candidate has just missed with the cutoff score.?


4. Can you also help me with the charts and graphs to show the improvement areas in all the parameters?


Thakur
 
Hi Thakur ,


There is no problem in helping out ; I hope this requirement is not urgent.


Give me a day or two , and I'll get back to you.


Narayan
 
1. Add a new field like Bucket then in J4: =IF(I4<22,"A",IF(I4<25,"B","C"))

Adjust values to suit

Copy down


2. By biggest in all categories do you mean by the Total Score field or the count of How many they were highest in


You can sort by the Total Score Field

Select the table A3:J1199

Data, Sort

Sort by Total Score


3. Now Filter

Select the table A3:J1199

Data, Filter

Now select the Drop Down in total Score I3

Number Filters, Less than

put the value in

apply
 
Hi Hui,


The Biggest in all categories, which candidate has contributed his best in all the parameters "Sentence Construction", "Vocabulary", "Fluency", "Pronunciation", "Listening & Comphrehension", "Volume, Intonation, PACE".


My question is which candidate has given 4 or 5 in all the parameters and which candidate has just missed the cuttoff score by not performing well in any of the one parameters.


Regards,

Thakur
 
Thakur

You may want to add another Column like "Score"

Then in J4: =COUNTIF(C4:H4,">="&4)

Copy down


You can then Filter that as appropriate eg: if a person has a score of 5 or 6 they will have got a score of 4 or 5 in 5 or 6 categories
 
Hi Hui,


The formula you suggested will give me count of how many candidates got all 4's or any number from the parameters.


1. What is the formula to know how each candidate has performed on each parameter.


2. How will I know on which parameter I need to improve on by seeing the data?


3. I want to know on which parameter the candidate has just missed the cuttoff score?


4. Who is the biggest contributor in all the parameters?


5. Using graphs or charts how do I show improvement areas in the parameters?


Please advise..


Thakur
 
Hi Narayan,


I am providing the link which has sample data


https://www.dropbox.com/sh/tovdou4mt5hgrfe/KD878psFt2


The total score the candidate can score is 30

cutoff scr is 25


1. I wanted to create three buckets


bucket 22, bucket >=24 and bucket 25


2. How do I create "Biggest contributor" performed by the candidate in all the parameters?


3. Which parameter a candidate has just missed with the cutoff score.?


4. Can you also help me with the charts and graphs to show the improvement areas in all the parameters?


Thakur
 
Hi Hui,


The formula you suggested will give me count of how many candidates got all 4's or any number from the parameters.


1. What is the formula to know how each candidate has performed on each parameter.


2. How will I know on which parameter I need to improve on by seeing the data?


3. I want to know on which parameter the candidate has just missed the cuttoff score?


4. Who is the biggest contributor in all the parameters?


5. Using graphs or charts how do I show improvement areas in the parameters?


Please advise..


Thakur
 
Hi Hui,


The formula you suggested will give me count of how many candidates got all 4's or any number from the parameters.


1. What is the formula to know how each candidate has performed on each parameter.


2. How will I know on which parameter I need to improve on by seeing the data?


3. I want to know on which parameter the candidate has just missed the cuttoff score?


4. Who is the biggest contributor in all the parameters?


5. Using graphs or charts how do I show improvement areas in the parameters?


Please advise..


Thakur
 
Hi Narayan/Chandoo..


When can I expect resolution to the data provided.. please let me know.


Thanks and Regards,

Thakur
 
Hi Narayan/Chandoo..


When can I expect resolution to the data provided.. please let me know.

Thanks and Regards,

Thakur
 
You must log in or register to reply here.
Back
Top