Conquer Club

Well not really homework...

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.

Thanks guys!!

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

OK...u lost me in the second sentence

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

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

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

me too

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

haha, do I have to bribe you?

hey guys, no offense, but posting to say you can't help is idiotic. if you can't help, don't spam the thread.

ummm, with free points maybe, lol.

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

how come ur doing schoolwork in the sumer

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

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

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

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

sam_levi_11 wrote:I have no idea how to do it

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.

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....

As in

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

yeah, that'd be it...

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

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

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%?

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...

glad to help

tis OK BB. I'm used to the attitudes on here. 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.