View Full Version : who's got the mad excel skillzszasz?

12-04-2006, 11:50 PM
So I could do this some lame way that doesn't involve leet skills, but I'd like to know how to do it the cool way that uses probably a decent amount of code. I have a bunch of grades and I want to disinclude grades that don't fall within the standard deviation from the class average calculation so they don't fuck up the curve. I think I have to do something like sum the column of student averages and subtract the averages that are more than one standard deviation from the pure average, and then divide by the number of summed items (total averages minus removed averages). Mmmm. Yes. Otherwise I'll have to hand pick the two or so averages that are affected! Poor me!

12-05-2006, 02:16 AM
oh, good lord. that is terrible!

Manic Subsidal Boy
12-05-2006, 04:08 AM
you said it

12-05-2006, 04:11 AM


Hope that helps!!

12-05-2006, 08:12 AM
Disinclude may not be a word, but disclude doesn't mean the right thing:

Main Entry: disclude
Part of Speech: verb
Definition: to disclose, make known

12-05-2006, 09:11 AM
Exclude is probably what you're looking for.

EDIT - Sorry, I mean I can't help you.

12-05-2006, 11:58 AM
sKratch, handpicking the grades sounds decidedly amateurish, so I agree you'd have to use code for that. I'd like to help, but my excel is in French. What I do use, though, in order to sort out grades or turnovers according to standard deviations or indeed any criterion, is the function "dynamic crossed table". But I'm translating that from French, so perhaps you don't see what I mean. I think it could work in your case. To select the standard deviations I use this kind of formula :=BDECARTYPE(base1;"CA";C$97:C$98) (CA = turnover, ecart-type = standard deviation) in the database. It is very basic excel, I don't have mad excel skillscszas, only a smattering.

Anyway, hope you got the hang of it in the end.

12-05-2006, 12:35 PM
I had the same kind of problem when doing an IT project last year. I'm not certain how i finally solved the problem, but i'm pretty sure 'and' and 'if' macro's are used.

For example:

if cell B5 > 'x' don't show on graph 'x'

12-05-2006, 02:50 PM
python, fuck excel.
avg = sum(grades)/len(grades)
std = (sum([ (grade - avg)**2 for grade in grades ])/len(grades))**(1/2)
nicegrades = [ grade for grade in grades if abs(grade - avg) < std ]
return sum(nicegrades)/len(nicegrades)

12-05-2006, 03:00 PM
Endy's the only one on the right track <3

The moral of the story is, I think I only have to exclude (thanks) two grades, which is totally not worth figuring out crazy code for, but I wanted to know how to do it the cool way anyways.

12-05-2006, 05:00 PM
on the right track? it's correct! it's the average of all grades within one standard deviation of the class average.

12-06-2006, 07:06 AM
But it's in python, you homo.

12-06-2006, 11:17 AM
But it's in python, you homo.

you'll have to use some VB script in excel, because i'm pretty sure cell manipulations aren't powerful enough to do what you want.

12-06-2006, 12:35 PM
Don't ever use a word "skillzszasz" again. It has Hungarian feel.