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

What kind of moron can't do an IF statement? Especially given Excel tells you exactly how to write one.

The other day my colleague was telling me about her housemate who she once saw adding up numbers from Excel on a calculator, because she didn't realize Excel could do the sums for her.

contact ztodd

Easiest:First sort the column, then just delete all the 0 values. Empty cells aren't used in calculations.
Alternately: Find/Replace 0 with ""

Here's a table for a bunch of z-values:
https://www.mathsisfun.com/data/standard-normal-distribution-table.html

How are you dumping data?

mrswdk wrote:What kind of moron can't do an IF statement? Especially given Excel tells you exactly how to write one.

The other day my colleague was telling me about her housemate who she once saw adding up numbers from Excel on a calculator, because she didn't realize Excel could do the sums for her.

my mother in law does that. she tells me she doesn't trust excel and that she's seen mistakes sometimes... but hey... If I say something, it must be because it's me thinking I'm always right again.

I wonder why she thinks the calculator doesn't make mistakes thought!

betiko wrote:

mrswdk wrote:What kind of moron can't do an IF statement? Especially given Excel tells you exactly how to write one.

The other day my colleague was telling me about her housemate who she once saw adding up numbers from Excel on a calculator, because she didn't realize Excel could do the sums for her.

my mother in law does that. she tells me she doesn't trust excel and that she's seen mistakes sometimes... but hey... If I say something, it must be because it's me thinking I'm always right again.

'You think you're always right' is the point at which you should just slap her in the chops and tell her to use the damn spreadsheet or else.

Excel has the XXXX...if functions.

Countif
Sumif
Averageif

The second parameter is the condition, in this case "<> 0"

For standard deviation and more look at this video

I didn't watch tzor's vid, but the preview of it looks like it uses an array formula. That's one way to do it.
Another way is to put your IF function in a new column, and do your std dev formula on that column.
It would probly be somethin like =IF(a2=0,"",a2) assuming your data starts in a2.

I appreciate all the help, thanks guys!!

My turn..... little side project I am working on.

I have the following for example:

Column A:
13 Colonies1
1982
2011 World Cup1
2014 World Cup43
2015 World Cup
4 Star Meats1
8 Thoughts
Africa

What I need to do is remove the last 1 or 2 numbers ...if they exist in each cell
you see that 2014 has 2 numbers whilst 2011 only has 1.
I understand that the 82 would be removed from 1982, thats fine for what I am trying to do.

So the result I am looking for in column B is this:
13 Colonies
19
2011 World Cup
2014 World Cup
2015 World Cup
4 Star Meats
8 Thoughts
Africa

Last night tried using this:
=IF(D4="Europa","Europa",(LEFT(D4, LEN(D4)-2)))

but it doesnt distinguish between numerals and alphabet, so I am back to bashing my head again.

Doing my head in, I do not know too much about coding, so a nice easy noob solution please.

Thanks

Raz.

I don't think there is anything you could do which will consistently remove unwanted numbers from the cells in a list like that, given that the cells all contain different amounts of unwanted numbers (0, 1 or 2) and the text strings are all different lengths. So there is no consistent rule (that I can think of anyway) for identifying surplus vs non-surplus numbers.

Unless, of course, you have a separate list of CC map names in which none of them have additional numbers tagged onto their names. Then you could use that as a frame of reference.

mrswdk wrote:I don't think there is anything you could do which will consistently remove unwanted numbers from the cells in a list like that, given that the cells all contain different amounts of unwanted numbers (0, 1 or 2) and the text strings are all different lengths. So there is no consistent rule (that I can think of anyway) for identifying surplus vs non-surplus numbers.

Unless, of course, you have a separate list of CC map names in which none of them have additional numbers tagged onto their names. Then you could use that as a frame of reference.

yes ... how?

Razorvich wrote:

mrswdk wrote:I don't think there is anything you could do which will consistently remove unwanted numbers from the cells in a list like that, given that the cells all contain different amounts of unwanted numbers (0, 1 or 2) and the text strings are all different lengths. So there is no consistent rule (that I can think of anyway) for identifying surplus vs non-surplus numbers.

Unless, of course, you have a separate list of CC map names in which none of them have additional numbers tagged onto their names. Then you could use that as a frame of reference.

yes ... how?

VLOOKUP would be easiest. If your messy list is in column A and your clean list is in column D (for example), you could set enter the formula in column B and set it to scan column D and see if there is a map name in column D which approximately matches the messy map name in column A. Then you set it to display the map name from column D in column B if a match is found. There might be one or two errors across a lengthy list, but they'd be easy enough to clean up manually.

Can just do this for you later if you like.

Thanks for the help everyone... perfect