A question about adding High-Low lines to Excel Line Charts

[taryn_1]

I have a question about adding High-Low lines to an Excel Chart. From the office help it says that these are available in 2D line charts.

http://office.microsoft.com/en-us/e...s-or-up-down-bars-in-a-chart-HP010007460.aspx

But with the sample data set I am using I am not sure about where to place them.

<table width="515" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:10093;width:207pt" width="276"> <col style="mso-width-source:userset;mso-width-alt:5778;width:119pt" width="158"> <col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt;width:207pt" width="276" height="17">
</td> <td class="xl66" style="border-left:none;width:119pt" width="158">Homes Sold</td> <td class="xl66" style="border-left:none;width:61pt" width="81"> Average Price</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt;border-top:none" height="17">Jan</td> <td class="xl66" style="border-top:none;border-left:none" align="right">280</td> <td class="xl66" style="border-top:none;border-left:none" align="right">410</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt;border-top:none" height="17">Feb</td> <td class="xl66" style="border-top:none;border-left:none" align="right">150</td> <td class="xl66" style="border-top:none;border-left:none" align="right">450</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt;border-top:none" height="17">Mar</td> <td class="xl66" style="border-top:none;border-left:none" align="right">220</td> <td class="xl66" style="border-top:none;border-left:none" align="right">430</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt;border-top:none" height="17">Apr</td> <td class="xl66" style="border-top:none;border-left:none" align="right">275</td> <td class="xl66" style="border-top:none;border-left:none" align="right">425</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt;border-top:none" height="17">May</td> <td class="xl66" style="border-top:none;border-left:none" align="right">155</td> <td class="xl66" style="border-top:none;border-left:none" align="right">410</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt;border-top:none" height="17">Jun</td> <td class="xl66" style="border-top:none;border-left:none" align="right">255</td> <td class="xl66" style="border-top:none;border-left:none" align="right">400</td> </tr> </tbody></table>

I go to insert line chart with the complete data set highlighted, and chose stacked line with markers but now am not sure about which series I add the High-Low Lines to - is it for Homes Sold or Average Price - and what the High-Low lines are actually representing.

 

[Gerald Higgins]

Hi.

I think you need to be clear yourself about why you want to add high low lines, and what you want to do with them.

For example, if you had data on every single house sold, you could show high and low values for each month.

OR, if you are looking at a period of several years, analysed in 6 monthly chunks, then you could show high and low values for each 6 month period.

If the data you have posted is your entire data set, then personally I don't think showing high and low values adds much value, and I wouldn't bother.

 

[taryn_1]

I'm not actually very clued up on their use entirely, I'm studying for an exam and high low lines are included in the objectives. It says that they are used mainly with stock charts and line charts but am trying to conceptualize their use.

 

[Gerald Higgins]

OK well stock charts are an example of where they are used.
The price of a single stock can change many many times a day and there's often no point in reporting on every single price.
So there are several ways of simplifying reporting of the price.
The easiest way is to just report a single price, let's say the closing price, the last price of a trade on that day, let's say 100.
For many purposes, that's fine.
For some purposes, a little more detail is required, and the next stop is often the High/Low/Close/Open chart, which shows 4 numbers - the highest price recorded on the day, the lowest price, and the closing and opening prices. This might be, for example, 105, 98, 100, 99.

For example, you might have two stocks that both closed at 100.
But the High/Low/Close/Open for one might have been 106, 94, 100, 95, and the other might have been 100,99,100,99.
So you get a bit more useful information with the HLCO.

It isn't restricted to stock prices obviously. You could do it for house prices, or even things like monthly temperature.

 

[Jon Peltier]

If all you have is average price, then you only have one data point per month, and high-low lines make no sense. Gerald explained how high-low lines might make sense if you had all sales amounts.

You really ought to be careful about plotting number of sales and average price on the same chart, since these are conceptually very different numbers. Think twice, then think twice more, about confusing the issue by using secondary axes. You can scale and rescale the axes to make the lines cross at various places, from which the audience may draw invalid conclusions.