PDA

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



sKratch
12-05-2006, 12:50 AM
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!

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

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

Llamas
12-05-2006, 05:11 AM
disinclude

disclude*

Hope that helps!!

sKratch
12-05-2006, 09: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

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

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

Duskygrin
12-05-2006, 12:58 PM
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.

Little_Miss_1565
12-05-2006, 01:19 PM
Sounds like this is a job for math skillz more than Excel skillz. I can make shit look pretty and do all kinds of sorting fun. But numbers? durr....

H1T_That
12-05-2006, 01: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'

Endymion
12-05-2006, 03: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)

sKratch
12-05-2006, 04: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.

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

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

Endymion
12-06-2006, 12:17 PM
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.

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