Possible VLookup formula

Topic

New Reply

I am tryingto determine the top and bottom 30 % of students answering a question. Thenumber changes depending on the number of students in each class. The number ofstudents is in C16, I need to determine the answer in d18.
P = [Number of Students who answered correctly / [/COLOR]Number of Students who took the test[/FONT]
D = [P-indexfor High group] – [P-index for Low group]

[TABLE=”class: cms_table_grid, width: 500, align: center”]
[TR]
[TD=”align: center”]A[/TD]
[TD=”align: center”]B[/TD]
[TD=”align: center”]C[/TD]
[TD=”align: center”]D[/TD]
[/TR]
[TR]
[TD=”align: center”]1[/TD]
[TD=”align: center”]Student[/TD]
[TD=”align: center”]Initials[/TD]
[TD=”align: center”]Correct Answer[/TD]
[/TR]
[TR]
[TD=”align: center”]2[/TD]
[TD=”align: center”][/TD]
[TD=”align: center”][/TD]
[TD=”align: center”]B[/TD]
[/TR]
[TR]
[TD=”align: center”]3[/TD]
[TD=”align: center”][/TD]
[TD=”align: center”][/TD]
[TD=”align: center”][/TD]
[/TR]
[TR]
[TD=”align: center”]4[/TD]
[TD=”align: center”]1[/TD]
[TD=”align: center”]AB[/TD]
[TD=”align: center”]A[/TD]
[/TR]
[TR]
[TD=”align: center”]5[/TD]
[TD=”align: center”]2[/TD]
[TD=”align: center”]CG[/TD]
[TD=”align: center”][/TD]
[/TR]
[TR]
[TD=”align: center”]6[/TD]
[TD=”align: center”]3[/TD]
[TD=”align: center”]SG[/TD]
[TD=”align: center”]D[/TD]
[/TR]
[TR]
[TD=”align: center”]7[/TD]
[TD=”align: center”]4[/TD]
[TD=”align: center”]SW[/TD]
[TD=”align: center”][/TD]
[/TR]
[TR]
[TD=”align: center”]8[/TD]
[TD=”align: center”]5[/TD]
[TD=”align: center”]JN[/TD]
[TD=”align: center”][/TD]
[/TR]
[TR]
[TD=”align: center”]9[/TD]
[TD=”align: center”]6[/TD]
[TD=”align: center”]KY[/TD]
[TD=”align: center”]D[/TD]
[/TR]
[TR]
[TD=”align: center”]10[/TD]
[TD=”align: center”]7[/TD]
[TD=”align: center”]TL[/TD]
[TD=”align: center”][/TD]
[/TR]
[TR]
[TD=”align: center”]11[/TD]
[TD=”align: center”]8[/TD]
[TD=”align: center”]EC[/TD]
[TD=”align: center”]C[/TD]
[/TR]
[TR]
[TD=”align: center”]12[/TD]
[TD=”align: center”]9[/TD]
[TD=”align: center”]LM[/TD]
[TD=”align: center”]C[/TD]
[/TR]
[TR]
[TD=”align: center”]13[/TD]
[TD=”align: center”]10[/TD]
[TD=”align: center”]AW[/TD]
[TD=”align: center”]A[/TD]
[/TR]
[TR]
[TD=”align: center”]14[/TD]
[TD=”align: center”]11[/TD]
[TD=”align: center”]TH[/TD]
[TD=”align: center”]C[/TD]
[/TR]
[TR]
[TD=”align: center”]15[/TD]
[TD=”align: center”][/TD]
[TD=”align: center”][/TD]
[TD=”align: center”][/TD]
[/TR]
[TR]
[TD=”align: center”]16[/TD]
[TD=”align: center”]Total[/TD]
[TD=”align: center”]10[/TD]
[TD=”align: center”][/TD]
[/TR]
[TR]
[TD=”align: center”]17[/TD]
[TD=”align: center”]P=[/TD]
[TD=”align: center”][/TD]
[TD=”align: center”][/TD]
[/TR]
[TR]
[TD=”align: center”]18[/TD]
[TD=”align: center”]D=[/TD]
[TD][/TD]
[/TR]
[/TABLE]

Reply | Quote

Replies

Jumper,

Welcome to the Lounge as a new poster! :cheers:

I’ll admit that I’m no math wiz but this makes no sense to me. If you have 100 students and only 10 get a question right (10%) how can you possibly have a top 30%? I think you’ll have to provide some sample data (a posted workbook would be most helpful) with the answer you are looking for for us to come up with an answer, or at least for me to come up with one as there is probably a math wiz or 2 here who may know exactly what you are talking about. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

If you have 100 students and only 10 get a question right (10%) how can you possibly have a top 30%?

..as a math jedi, this seems like a Sith Grade question to me:
..the ‘top 30%’ applies to the 10 that got the question right.
10% of the 10 that got the question right = 10% of 10 = 1 person,
..so 30% = 3 x 10% = 3 x 1 = exactly three students are in the top 30% (and ditto for the bottom 30%)

But I agree with you, Ja-Ja-Jumper maybe should post a sample workbook

zedi
PS: I’ve traded my ToyJoda for a new car have I – may the porsche be with you

Reply | Quote

Zeddy,

Puzzle me this?

46680-Riddler

If 10 students got it right which 3 students are in the top 30%? 😆

“Statistics are like bikinis, what they reveal is interesting, what they hide is vital!” sorry can’t remember who said that…wish it was me.

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

..as the dark night rises here in the UK..aha! I see what you’re getting at!

..I assumed each student had an overall score (e.g. total number of correct answers), so it’s just a case of ranking them.

zeddy

Reply | Quote

I am trying to determine the top and bottom 30 % of students answering a question. The number changes depending on the number of students in each class. The number of students is in B28, I need to determine the answer in C31.

P = [Number of Students who answered correctly / Number of Students who took the test
D = [P-index for High group] – [P-index for Low group]

When all students are placed in order of final score, and sorted highest to lowest, it will determine high performers and low performers. In the example provided, there are 22 students, so high performers would be the top three questions and the low performers would be the bottom three questions. I hope this helps explains for better understanding.

Reply | Quote

So If I understand you correctly, there are 5 questions worth 20 each. Horizontally, regardless of whether or not the 5 questions were answered (either correct or blank), in column I, 0 wrong is a score of 100, 1 wrong is 80, 2 wrong is 60, 3 wrong is 40, 4 wrong is 20, and all wrong is 0.

Vertically, for each question, the Total row (28) is the number of correct and/or blank answers and P= that total/by the number of students.

The median is student 11 and 12 with 10 above and 10 below. 30% of the upper 10 is 3.3 or the first 3 people while 30% of the lower 10 is the last 3 students.

So the question now is, how do you define index?

If index is the average the 3 students then D equals Average high – Average low

Code:

D=((100+100+100)/3) – ((60+40+40)/3) = 53.33

If index is the sum of the 3 students then D equals Sum of high – Sum of low

Code:

D=(100+100+100) – (60+40+40) = 160

So how are you defining Index?

Maud

Reply | Quote

I guess I am explaining this problem incorrectly. Since the total number of students change with each exam, B28, I am trying to calculate the top 30% and bottom 30% of each question answered correctly in C31. If there is 22 students taking the test and the top 30% equals 7 students and the bottom 30% equals 7 students, then the calculation should be as follows:

In Column C: the top 7 students answered correctly which equals 7/7=1. The bottom 7 students answered correctly which equals 4/7=.57. The overall answer is 1 – .57 = .43.

I understand I may be explaining this incorrectly. I am still a novice when it comes to these advanced formulas.

Reply | Quote

Hi Jumper

..you have a choice of formulas (which give the same answer of course)..

In cell [C31] you can put either of these formulas..
=(COUNTBLANK(C5:C11)/7)-(COUNTBLANK(C20:C26)/7)
=(COUNTBLANK(C5:C11)-COUNTBLANK(C20:C26))/7
..and copy [C31] formula across to [C31:G31]

zeddy

Reply | Quote

Zeddy,

Those formulas work great if I do a manual input for the top and bottom 30%. I really appreciate the help. Is there a formula addition which could auto-calculate if the number of students change for each class in cell B22?

Jumper

Reply | Quote

Hi Jumper

Yes, we can put formulas in to auto-calculate if the number of students change.
..I’m not sure about your exact arrangement for different class sizes, I assume you would have more rows for a larger class???

I created a sample file (attached) which has 35 students.
I added a named cell (to make it easier when changing class sizes):
cellStudentCount

I also added two more named cells..
cellPercent
..this named cell is used to set your Top/Bottom percent e.g. 30%, 20% etc.

cellPercentCount
..formula in this named cell converts your percentage to number for Top/Bottom students depending on class size. I used =CEILING to ’round-up’ to ‘whole-number-of-students’.

I also added some conditional-formatting to automatically highlight the relevant Top/Bottom records in yellow.
For example, if you change the 30% to 20% the highlighted records will be shown etc etc etc.

Check out the attached file and see if this works out for you.
(you can hide the cells in [A4] and [B4] if required, by applying a custom-number format of three semi-colons i.e. ;;; which gives a non-visible and non-printing cell)

zeddy

Reply | Quote

Hello Zeddy,

Your formulas worked great, but I think when I placed them in my actual spreadsheet I made a mistake. If you could look at the attached spreadsheet and give me some guidance I would appreciate it.

The help you, and the others, have provided has taught me a lot. I thank you.

Reply | Quote

Hi jumper

..see attached file.

The formula in [C46] is
=(COUNTBLANK(OFFSET(C$1,6,0,cellPercentCount))-COUNTBLANK(OFFSET(C$1,6+cellStudentCount-cellPercentCount,0,cellPercentCount)))/cellPercentCount

The formula part.
OFFSET(C$1,6,0,cellPercentCount)
..means start at the top cell [C1] in the column.
..go down 6 rows (i.e. to row 7, the first Student entry)
..go across 0 columns (i.e. stay in the same column)
..and select cellPercentCount rows i.e. 8 cells

Does that help explain it, or do you want a more detailed explanation?

Also, very importantly, because we are working with the TOP and BOTTOM student results, these results must be sorted from top to bottom i.e. ranked in order, so I sorted the data based on the Score in column

zeddy

Reply | Quote

Zeddy,

Thanks you very much for the explanation. Your formulas work great. Thanks for all the help. I really appreciate it.

Jumper

Reply | Quote