After sorting I need to select the top and bottom quartile. Top is simple as it is fixed. How do I select the bottom quartile which varies with each spreadsheet?
Can you please attach a sample file, explaining what you want to achieve, indicating the expected result.
please do not set additions as answers, but edit the question,
do you wnt to make the analysis ‘per question’? - what about students from percentile 20 to 40 having the same result?
or do you want the classification into the categories be made according to the overall result? (sorry, i’m not a native english speaker, maybe your question is clearly defined for you and native speakers, for me it is unclear)
What do you mean by “selecting”? Do you mean something like “mark as if using mouse, so that relevant rows are ready for copying to clipboard”? Or do you mean something like “my formula needs to process only that part of data”? In any case, please describe how do you do that in “simple” case of “fixed” top quartile. That would help others to understand your problem.
You just want to identify which students are in the top and bottom quartile based on their total scores? In the spreadsheet you sent, there are 43 students, sorted in descending order of their total scores; the first 11 students (student numbers 1 through 11) are in the top quartile and the last 11 (students number 33 through 43) are in the bottom quartile. Mission accomplished? Perhaps this simple response will help you clarify what more difficult question you’re getting at. I’m not sure what you mean by the top quartile being fixed and the bottom quartile varying with each spreadsheet.
This isn’t responding directly to the question you asked, but: if you’re interested in the discriminatory value of each question, you might consider calculating an item-to-total correlation for each item. For example, with the data set you sent, I entered into cell d53 =CORREL(D4:D46;M4:M46) to find that item 2 correlated about 0.87 with the total score! Item 10, by contrast, correlated at 0.72 with the total score. Both of these items are discriminating quite well. With an item-to-total correlation of 0.87, you might consider just giving item 2 and skipping the rest of the quiz!
I am attaching a spreadsheet of student marks which has been sorted in descending order based on final percentages. I now wish to select the top quartile of students and the bottom quartile of students in order to do a question by question analysis of the quiz. This will allow me to assess the difficulty and discriminatory value of each question.Student Marks.ods
Please edit your question or use the comments.
If “select” means “visually mark”, and “quartile” means not “quartile” (a single value, not necessarily actually present in data set, or single), but all values below 1st quartile and after 3rd quartile, then you may use a conditional expression with PERCENTILE like
$M4<PERCENTILE($M$4:$M$46;0.25)
$M4>PERCENTILE($M$4:$M$46;0.75)