calculate average of every 5 cells in Excel

I have some data sampled at a rate of 1 sample per minutes. However, to use them as input for the following calculation, I have to convert them to the 5 minutes average. Simply put, I want to let B1=average(A1:A5), B2=average(A6:A10), B3=average(A11:A15), ... til B10=average(A46:A50).

I tried VBA code in Excel but didn't make it work. Then I tried Matlab-the code works but it's troublesome to copy/paste all the data between Excel and Matlab back and forth. Finally I figured out a simple way to do this in Excel, by using the OFFSET, ROW, and INDIRECT functions.

The formula to put in cell B1 is:

=AVERAGE(OFFSET(INDIRECT("A"&ROW(A1)*5-4),0,0,5,1))


The Row(A1) returns the row number of the cell, which is '1' in this case. So the INDIRECT("A"&ROW(A1)) *5-4, 0,0,5,1) returns the cell A1 to A5. When dragged to cell B2, the INDIRECT("A"&ROW(A2)) *5-4, 0,0,5,1) returns the cell A6 to A10. So I can drag this formula all the way down to B10 to finish this task.

The file looks like this:

How to concatenate two datasets in Matlab?

I wanted to vertically combine two datasets, so I can keep a long file of all the test results. The join function does not work here anymore. Of course this is even not a problem in Excel. However, to make it work in Matlab, it did took me a while to figure it out.

c=cat(1, a, b)

Excel slope function vs linest function

To get the slope of a pair of x and y, usually I first plot the curve and then add the trend line.

 

Actually there are two functions in Excel to help me do this. The first one is the 'SLOPE' function. However, it only gives what is shown by the red trend line (slope=0.25). The second one is 'LINEST' function, which is more flexible. By setting the third parameters in the function to 'False', it automatically set the intercept to zero (slope=0.36).

'LINEST=(y series, x series, False)'

MATLAB parallel computing

Here I would like to share a big secret with you: I found the easiest way to do parallel computing in Matlab. You don't need use any special command or functions to do that. Actually once I found that using parafor even took longer time to do the same calculation than using just for.Weird!

Okay, here's the trick: run multiple computers at the same time. Yeah!

Best way to make a stairs graph

Today I got two sets of data and wanted to present them in one stairs graph. So I tried Excel, MATLAB, and Sigmaplot. The conclusion is that Sigmaplot is the best software to draw something like this figure:

The plotyy function in matlab can be used to create figures with secondary Y axis, however, if you manually set the Xticklabel, the labels will be messed up, like this figure shows:

Join datasets by using the 'JOIN' function

Well, this is a real problem I came across in my research. I took hundreds of samples and recorded the sampling time in a spreadsheet. Then I brought them back to my lab and have them analyzed, and the results was recorded in another spreadsheet. And because I analyzed the samples in a random order, the two files look like these:

So I cannot just copy the column of one file and paste it to the other file. I then tried to import these two files into MATLAB and wrote a code by myself to combine these two files based on the BagID. That is not really easy but the code turned out to be working fine. And after a while, I happened to know that there is a much simpler way to do what I wanted to do- the join function is designed for this purpose. Here is what I should do in the first place:

%import data as two datasets

ds1=dataset('XLSFile', 'BagID.xlsx');

ds2=dataset('XLSFile', 'results.xlsx');

%Jion ds1 and ds2 based on the common variable name, which is the 'BagID' in this case.

ds3=join(ds1, ds2)

Here's the output.

That's it! Isn't it neat?

P.S. Those are fake data and of course I can sort both files by the BagID and then copy&paste in the spreadsheet. But anyway, this is a good MATLAB function to know, right?

Some useful statistic toolbox functions

I was working on some statistical problem today, with some real measured data on hand. The statistics I learned more than 10 years ago have all faded in my memory. So I have to use some books and the Matlab demos to help me recall those fundamental things in statistics. Here I got a set of measured data:

CN=[0, 53, 110, 144, 199, 199, 203, 188, 176, 171, 199, 184, 154, 129, 101, 41, 0]

It looks like a bell shape curve , so I guessed a normal distribution. But how do I know if it is normally distributed, in a more scientific way? Later I found these two functions to be very useful.

normplot(CN)

lillietest(CN)

The normplot gives a figure as this:

If the scatters follow the line, as it is in the above figure, the data can be seen as approximately normally distributed. But why? I don't know. This is what the demo says.

The lillite test can give even more scientific answer to the question of whether the data is normally distributed or not. After running the lillietest(CN), I got

ans =

     0 

This result gives me a definite answer that yes, this set of data is normally distributed. And why? I don't know, again!