Two useful Excel tricks

I was using Excel today and find that I need to do two things. The first one is to automatically alternate row colors (one shaded, one white), so I searched online and found this tutorial very useful.

http://www.techonthenet.com/excel/questions/cond_format2.php


And the other thing I would like to do is to create a list from data validation, so in each cell of one column, I can select the car makers. And I would like to use the next column to record the car model, which mean the second list is dependent on the selection of previous cell's input. I searched online and found that it is called : dependent list for data validation. Here's the tutorial that I followed:

http://www.contextures.com/xlDataVal02.html

Very helpful!

Concatenate text and convert to numbers in Excel

I have a long Excel file which keep the date, month, year, hour, minute, and second in separate columns (figure 1). I want to use only one column to present the time, so it would be easier to plot the time as x-axis. Here is the function I used in cell G2:

=VALUE(CONCATENATE(A2,"/",B2,"/",C2," ",D2,":",E2,":", F2))

The Value function convert the text into numbers.
The Concatenate function connects all the text and convert them into one string.

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:

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)'

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:

how to calculate payments on a loan using MS Excel?

Excel has lots of useful built-in functions, and PMT is one of them that can calculate payments on a loan for you. Here is a simple example:

If I get a loan of $5000 from a bank, with the annual interest rate of 8%, and I am supposed to pay it off in 3 years, how much should I pay (OR they will ask me to pay) each month?

And here is how to do the calculation:
Input following into any cell of a Excel worksheet:

=PMT(0.08/12, 3*12, 5000)

and press Enter.

The value shown in the cell ($156.68 ) is the money I am going to pay to the bank each month. Here 0.08/12 is used because the 8% is annual rate, so divide it by 12 to get the monthly rate. And in 3 years, there are 3*12 months, which means the number of payment is 36.

Then, what is the total I pay to the bank? As you might have guessed, it is $156.68*36 (=$5640.55). So the bank will earn $640.55 in this deal at the end. Well, that is a lot of money. Now I am thinking about how to own a bank...


P.S: If you didn't buy MS Excel, that's OK! The Google Docs spreadsheet will do the same thing for you! It is right here: docs.google.com


P.S: this has nothing to do with Matlab, but it is still interesting (at least to me) and useful.