Thursday, May 10, 2007

Add second y axis on a chart in Excel

In Excel 2007:
Plot two sets of data on a single chart.
Then, you must select data series you wish to plot on the second axis by either clicking on it on the chart or selecting it from a drop down menu in top left corner under Layout tab. Then click 'Format Selection' under Layout tab and select "Plot on Secondary Axis".

In Excel 2003 and earlier:
Select chart, press Ctrl+1 and proceed with data selection...

Note: that not all charts support this feature.

Wednesday, May 09, 2007

Format elapsed time in excel

I have a set of times saved in seconds and fractions of a second. I want to be able to transpose that into something that can be understood easily, such as hours:minutes:seconds
Because Excel does not provide automatic support to measure elapsed time, this unfortunately needs to be done by hand.
Sample input data set (in columns A1-A4):
4508.507
4452.111
4423.425
4592.724

Sample output:
1:15:8.507
1:14:12.111
1:13:43.425
1:16:32.724

Formula (for first value only (i.e., in cell A1)
=INT(A1/3600)&":"&RIGHT("00"&MOD(INT(A1/60),60),2)&":"&ROUND(MOD(A1, 60),3)

Cell value (e.g., A1) can be replaced with things such as SUM (cell range), AVERAGE (cell range), MAX (cell range) to get formatting of the result of processing a range of cell values.