Typically, a rank $r$ (between $1$ and $n$ for $n$ data) is converted to a percent $p$ via the formula
$$p = 100\frac{r-\alpha}{n+1-2\alpha}$$
for some predetermined "plotting position" $\alpha$ between $0$ and $1$, inclusive. Solving for $r$ in terms of $p$ gives
$$r = (n+1-2\alpha) (p/100) + \alpha.$$
Excel has historically used $\alpha=1$ for its PERCENTILE and QUARTILE functions. The online documentation for QUARTILE.INC and QUARTILE.EXC is useless, so we have to reverse-engineer what these functions are doing.
For example, with data $(1,2,3,4,5,6,7,8,9,10)$, we have $n=10$ and $p \in \{25, 50, 75\}$ for the three quartiles. Using $\alpha=1$ in the preceding formula yields ranks of $9(0.25)+1 = 3.25$, $9(0.50)+1 = 5.5$, and $9(0.75)+1 = 7.75$, reproducing the results for QUARTILE.INC.
If instead we set $\alpha=0$ the corresponding ranks are $11(0.25) = 2.75$, $11(0.50) = 5.5$, and $11(0.75) = 8.25$, reproducing the results for QUARTILE.EXC.
Further testing on your part (I do not have a recent version of Excel) may establish the validity of my guess that these two versions of the quartile function are determined by these two (extreme) values of $\alpha$.
By the way, fractional ranks are converted into data values by means of linear interpolation. The process is explained and illustrated in my course notes at Percentiles and EDF Plots--look near the bottom of that page. There is also a link to an Excel spreadsheet illustrating the calculations.
If you would like to implement a general percentile function in Excel, here's a VBA macro to do it:
'
' Converts a percent, computed using plotting position constant A,
' into a percent appropriate for the Excel Percentile() and
' Quartile() functions. (The default value of A for Excel is 1;
' most values in use are between 0 and 0.5.)
'
Public Function PercentileA(P As Double, N As Integer, A As Double) As Double
If N < 1 Or A < 0# Or A > 1# Or P < 0# Or P > 1# Then
Exit Function
End If
If N < 2 Then
PercentileA = 0.5
Else
PercentileA = ((N - 2 * A + 1) * P + A - 1) / (N - 1)
End If
End Function
It converts a nominal percent (such as 25/100) into the percent that would cause Excel's PERCENTILE function to return the desired value. It is intended for use in cell formulas, as in =PERCENTILE(Data, PercentileA(0.25, Count(Data), 0.5)).
Best Answer
Use a Scatter Plot where the horizontal axis is time. Below is a screenshot of an Excel sheet where two temperatures are shown. Temp 1 starts at 9:00:00 AM and increments by 1 minute and 26 seconds. Temp 2 starts at 9:30:00 AM and increments by 2 minutes and 53 seconds. They are both plotted in the graph.
As shown in the plot, the blue Temp 1 has been "selected" in the graph and the corresponding graph formula is:
=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$27,Sheet1!$B$2:$B$27,1)
When I select Temp 2, the corresponding graph formula is:
=SERIES(Sheet1!$E$1,Sheet1!$D$2:$D$27,Sheet1!$E$2:$E$27,2)
Both Temp 1 and Temp 2 uses the same x-axis and y-axis.
There is a second way to do this where the blue series has its own x-axis and y-axis, and the red series has a different x-axis and y-axis. However, I wouldn't recommend this scheme because it is typically too confusing for others to interpret the results. It requires that you assign one (or more) of the lines to a secondary y-axis and then a secondary x-axis. Here are the steps:
1) Right click on the series that you want to assign to the secondary axes. Select "Format Data Series". Under "Series Options" select "Secondary Axis". That will assign the selected series to a secondary y-axis.
2) With the series still selected, go to the "Chart Tools" "Layout" "Axes" selection on the ribbon. Click "Secondary Horizontal Axis" and pick whatever options you want. That will assign the selected series to a secondary x-axis.
Below is an example. I purposely changed the color of the text of the lower x-axis and the left y-axis to blue to signify that these are the axes to use for the blue series.
I changed the color of the text of the upper x-axis and the right y-axis to red to signify their use for the red series.
Notice that the two y-axes have different scales. The blue y-axis runs from 0 to 90, while the red y-axis runs from 0 to 100.
Notice also that I set both x-axis to start, stop, and increment at the same values. If you set the increment of the two x-axes to two different values, this graph can be even more confusing.