Excel removing gaps in data

[Sxotty]

Ok I have a lot of data 17,000 rows of it.

I need to separate the data into blocks that are between certain time periods.

So
23:00-7:00 --> column 1
8:00-->22:00 column 2

I did this like

=IF(B3>700, IF(B2<2300,D2,""),"")
=IF(B2>2200,D2,IF(B2<800,D2,""))

That puts the data in and then blanks, but I cannot run statistics b/c there are blanks in the data.

Then I tried

=IF(B2>700, IF(B2<2300,D2,0),0)
=IF(B2>2200,D2,IF(B2<800,D2,0))

Now I can run statistics, but of course they are wrong b/c of the 0's

So I can get the average like so

=SUMIF(F2:F17519,">0")/COUNTIF(F2:F17519,">0")

But how in the world can I get Standard deviation and so forth? (I can get n, but I don't see how to get the sum of squares).

Is there some way to make the data collated without the gaps? I realize one could write a visual basic program or something, but I couldn't do that now b/c of lack of skills and lack of visual studio.


So in summary I just need to separate one set of data into two sets based on when it was recorded. If any one knows how please let me know thanks.

 

[Sxotty]

Ok I figured out how to get STDEV, but it is a horrid hack.

I still really need a way to remove gaps so I can put data into a stat package and get better info on it.

Anyway the way I got Stdev

H3
=(F2-N$3)
Where n$3 is mean

=IF(H3=-N$3,0,H3^2)
This gives the squares then 
=SUMIF(J2:J17519,">0")
gives the sum of squares.

This is a horrid way to do it though. Someone has to have a better idea eh?

 

[Mize]

I'm no spreadsheet monkey, but isn't there a boolean for whether the cell contains numerical data?

 

[Sxotty]

I'm no spreadsheet monkey, but isn't there a boolean for whether the cell contains numerical data?

I can tell if the cell has a number I want, but I don't know what to do when I discover that.

If I want two columns of the separated data I don't know how to avoid skipping cells where data I did not want resided. Maybe I am being dumb, maybe under data analysis there is a way to check the data that is above a given value. I will check that real quick.

 

[Sxotty]

Nope there isn't such a funtion in Excel, ah well. I can probably get a stats package from someone that will pull out the values that I set to 0, or " " or whatever it wants.

 

[Mize]

Are you sure?
OpenOffice.org Calc has Type(cellcoordinate) function that returns an integer corresponding to text, numerical etc.

 

[Mize]

I can tell if the cell has a number I want, but I don't know what to do when I discover that.

If I want two columns of the separated data I don't know how to avoid skipping cells where data I did not want resided. Maybe I am being dumb, maybe under data analysis there is a way to check the data that is above a given value. I will check that real quick.

You can construct an if then loop that puts a condition on inclusion in your calculation. It'll look ugly, but that's how I've done things like that before. In this case you probably have to manually write out the standard deviation rather than use STDEV.

 

[Sxotty]

Are you sure?
OpenOffice.org Calc has Type(cellcoordinate) function that returns an integer corresponding to text, numerical etc.

Mize I think you and I are not communicating clearly.

I can detect what is in the cell and whether it is
category 1
or
category 2

Lets say I have cells

A1=1am
A2=1pm
A3=2am
B4=3pm
B1=20
B2=25
B3=10
B4=13

I want a column with the
am
and another with
pm

So I want
C1=B1=20
C2=B3=10
D1=B2=25
D2=B3=13
But I use a boolean which returns something when false.
Thus I get
C1=B1=20
C2= whatever I assign, 0, blank etc..
C3=B3=10
C4= whatever I assign, 0, blank etc..

And

D1= whatever I assign, 0, blank etc..
D2=B2=25
D3= whatever I assign, 0, blank etc..
D4=B4=13

Now I cannot run statistics on column C and D b/c the non numeric value screws up stats, or because the numeric value of zero screw it up.

So I can tell whether it should be included, I just cannot figure out how to communicate that to the stat program in excel, or how to remove all the blanks short of going thru by hand, which I will not do

 

[Mize]

Okay I sort of see what you're doing. If I had time I'd populate a small sheet to take a better look, but that won't be now.

My guess is that the best way would be to use your conditionals to populate a matrix and then run stdev on matrix (though I've never done stdev on a matrix in excel).

 

[Sxotty]

No worries now I found a stat package where I can exclude values so I just excluded those <.01 and everything worked out. I am still curious if there is an elegant solution in excel, but don't have the time to figure it out now.

 

[Davros]

you can format cells so they dont display zero's is that any good to you

 

[Sxotty]

you can format cells so they dont display zero's is that any good to you

No, I know the accounting displays (-)

I could also put anything I wanted in the cells with the if statement. I need to get rid of gaps in a column where the gap is defined by a
0
" "
Or anything I chose in the if statements.

I already got around the problem by using a third party stats package that let me put a bound on inputs based on values, I am just shocked excel doesn't do it already.

 

[Mize]

I already got around the problem by using a third party stats package that let me put a bound on inputs based on values, I am just shocked excel doesn't do it already.

In all fairness it's Excel's STDEV function that doesn't allow it, right?
You could, in fact, conditionally place the values using IF/Then into a matrix and then pipe it to STDEV in Excel.

 

[PellePlutt]

The simplest solution really really would be writing a VBA macro to create nice columns.. I don't have the syntax in my head any more, but all you need is a basic for loop, shouldn't be too hard to look up.

Tools->macro->visual basic editor.

 

[Simon F]

Sxotty,
You could just write the equations for std dev etc yourself. <shrug>

 

[Sxotty]

Sxotty,
You could just write the equations for std dev etc yourself. <shrug>

I already have equations shown above for STDEV and average.

It was goodness of fit equations and AIC test for
logistic, and lognormal distributions that was the sticky wicket.

Like I said though I already got around this by using a different program.

Yeah if I had experience in VB I am sure it would not be a big deal, but it wasn't worth the time to learn at the moment. I did a bit in VB a couple years ago to make a program that of all things randomly once a month reminded me to buy flowers for my wife lol: I know but its true). That was all I ever did and it was a pain simply to find where I could actually write code instead of dragging boxes and such. I was used to borland c++ 3.0 and a blue text editor screen before that.

 

[PellePlutt]

I already have equations shown above for STDEV and average.
Yeah if I had experience in VB I am sure it would not be a big deal, but it wasn't worth the time to learn at the moment. I did a bit in VB a couple years ago to make a program that of all things randomly once a month reminded me to buy flowers for my wife lol: I know but its true). That was all I ever did and it was a pain simply to find where I could actually write code instead of dragging boxes and such. I was used to borland c++ 3.0 and a blue text editor screen before that.

Ah, but Excel's visual basic is different, the "boxes and such" are what's in the spreadsheet, so you just type in some code. I will agree though, it might not be the best solution unless you do a lot of work in Excel. If you have a basic programming ability, you'd waste maybe thirty minutes to an hour the first time you try to use it, and save a bunch of time every time after that.

Oh, and I also first learned programming using Borland's wonderful blue editor.. good times :smile:

 

[Sxotty]

Well I need to learn to code in a modern setting so I will probably, but not until after my project is finished .

Anyway thanks for all the suggestions guys. I do appreciate it. And pelleplut as I was sitting there thinking on it I actually of course thought of populating an array with the values I wanted then writing them to a column and how easy that would be if only I could code a little snippet, but I did not want to go off on the tangent at the time as I was in a rush. Now that I have the info ready though I will probably take a free moment over the break to learn how to use the editor for the next annoyance. I may be using matlab or something instead of excel though so the effort might still be wasted to some extent.