Homework Help - Excel & Stats

by **wicked** on Fri Jul 06, 2007 11:26 am

Well not really homework... :lol:

I know we have a plethora of Excel experts on here, and I'm needing help with some formulas. I have a data set that typically includes zeros, yet I need to exclude zero values from my calculations - standard deviation, mean and normal distribution. Yes I can do it manually pretty easily, but I need this to be automated when I dump data. I'm assuming it's an If Then statement, but I suck at those.

And speaking of stats, anyone know what percentage of a normal Gaussian (?) distribution 1.5 standard deviations will give me? I know 1 is 68% and 2 is 95%, but I need something in between, so was hoping I could use 1.5?

If I can do this easier in access, that'd work too. But I suck at access... that's why they have me doing this database. :lol:

Thanks guys!!

by **0ojakeo0** on Fri Jul 06, 2007 11:28 am

i could tell u in 2 months.... my brain is turned off to anything besides video games and sports during the summer

by **spinwizard** on Fri Jul 06, 2007 11:28 am

OK...u lost me in the second sentence

by **diddle** on Fri Jul 06, 2007 11:28 am

damn, normally i'd spam a thread like this, but since its wicked..........

by **s.xkitten** on Fri Jul 06, 2007 11:29 am

well...i made it through, but i have no idea what to do...

by **0ojakeo0** on Fri Jul 06, 2007 11:29 am

spinwizard wrote:OK...u lost me in the second sentence

me too

by **sam_levi_11** on Fri Jul 06, 2007 11:29 am

oh, standard deviation and stuff is easy- but im not doing it unless i have to

by **wicked** on Fri Jul 06, 2007 11:31 am

haha, do I have to bribe you? :lol:

by **wicked** on Fri Jul 06, 2007 11:32 am

hey guys, no offense, but posting to say you can't help is idiotic. :roll:

if you can't help, don't spam the thread.

by **sam_levi_11** on Fri Jul 06, 2007 11:32 am

ummm, with free points maybe, lol.

nah its easy, you have a calculator i hope- if not ur fcked.

by **0ojakeo0** on Fri Jul 06, 2007 11:33 am

how come ur doing schoolwork in the sumer

by **wicked** on Fri Jul 06, 2007 11:34 am

Sam you missed the part where I said I need help in setting up the spreadsheet formulas, not with how to calculate the stats.

by **sam_levi_11** on Fri Jul 06, 2007 11:35 am

oh, formulas are even easier...but i cant remember them at moment- what formulas do you need

by **wicked** on Fri Jul 06, 2007 11:38 am

have you ever used Excel? ya might wanna re-read what I'm actually asking for.

by **sam_levi_11** on Fri Jul 06, 2007 11:40 am

oh, sorry i skimread. anyway, i get what u need now...........but quite frankly im a lazy sht today so no

by **wicked** on Fri Jul 06, 2007 11:43 am

sam_levi_11 wrote:I have no idea how to do it

by **Bertros Bertros** on Fri Jul 06, 2007 11:53 am

Why not try firing up google - typing

"excel average all except zero"

and clicking the first link... You'll find it leads to an MS page which has the a link on it entitled "Calculate the average of numbers, ignoring zero (0) values"...

Unsuprisingly that page has a formula for it... I mean for f*ck sake surely that is easier than asking here.. Oh f*ck it I'll save you the bother..

=AVERAGE(IF(A2:A7<>0, A2:A7,""))

Will give you the average of A2 - A7 ignoring zeros...

Now RTFM FFS please.

by **wicked** on Fri Jul 06, 2007 11:56 am

I can do the average w/o zeros, and have seen that formula, and have searched that MS page, so calculating the mean is no problem, sorry shouldn't have included that one. But how to do stddev & normdist? hmm.. maybe I can modify that same formula....

by **Bertros Bertros** on Fri Jul 06, 2007 11:59 am

As in

=STDEV(IF(A2:A7<>0, A2:A7,""))

yeah, that'd be it...

by **wicked** on Fri Jul 06, 2007 12:04 pm

Yep, got it. Thanks. Like I said, I suck at If statements, and wasn't entering that as an array.

by **fisherman5** on Fri Jul 06, 2007 12:05 pm

i could be very wrong but i'm assuming this is the Gaussian thing

.5%|2%|13.5%|34%|34%|13.5%|2%|.5%

not sure if it will help any i'm pretty sure the numbers are right

by **Phobia** on Fri Jul 06, 2007 12:05 pm

wicked wrote:And speaking of stats, anyone know what percentage of a normal Gaussian (?) distribution 1.5 standard deviations will give me? I know 1 is 68% and 2 is 95%, but I need something in between, so was hoping I could use 1.5?

Found these two cool graphs on the net, i made the third one lol -

Can't give you an accurate percentage, but I would predict around 80-85%?

by **Bertros Bertros** on Fri Jul 06, 2007 12:09 pm

wicked wrote:Yep, got it. Thanks. Like I said, I suck at If statements, and wasn't entering that as an array.

Good good. Sorry for being an arse, having a bad day, a server has popped and i have client spitting venom in my general direction, its Friday, the datacentre guys are disinterested and I can't do anything but sit and wait. Normally I would have just given you the answer without the attitude...

by **0ojakeo0** on Fri Jul 06, 2007 12:12 pm

glad to help

by **wicked** on Fri Jul 06, 2007 12:17 pm

tis OK BB. I'm used to the attitudes on here. :wink:

I was trying to figure this out yesterday at the end of a 10 hour workday, so it just wasn't clicking.

Thanks everyone, I think sigma=1.5 will work to flag my bad data.

Homework Help - Excel & Stats

Homework Help - Excel & Stats

Re: Homework Help - Excel & Stats

Who is online