plot dots on top of bar graph

[helenkim]

hello,
I created a bar graph and now i want to plot dots on top of the existing bar graph. does anyone know how i need to a)organize my data in order to do so and b) what i need to actually do in order to insert the dots on top of the bar graph?

so for example, i have mon-fri listed vertically along the axis and i have calories graphed to the right. well i want a dot on the calories bar indicating what my ideal should have been. to be more specific, if i graphed 2000 calories in monday, i want a dot on 1200 calories to show that is my ideal.

any idea? thank you!!

 

[CWatts]

You'd just need another series in your chart, use the line-with-dots format, and then just delete the lines and you'll be left with the dot where your target is.

 

[helenkim]

thanks! but could you explain how i can plot it horizontally? it seems to plot vertically if i switch it to lines. and also, how do you delete the lines?

thanks so much!

 

[CWatts]

Ugh, sorry, I answered too early in the AM and completely misunderstood.

You're looking for something like this:

I assume your data is formatted like so:

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:34px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td style="text-align:center; ">Calories</td><td style="text-align:center; ">Target</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Mon</td><td style="text-align:center; ">1504</td><td style="text-align:center; ">1796</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Tue</td><td style="text-align:center; ">1500</td><td style="text-align:center; ">1532</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Wed</td><td style="text-align:center; ">1387</td><td style="text-align:center; ">1184</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Thu</td><td style="text-align:center; ">1218</td><td style="text-align:center; ">1497</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Fri</td><td style="text-align:center; ">1441</td><td style="text-align:center; ">1872</td></tr></table>

Highlight A!:B6 and create a generic bar chart. Highlight A1:B6 and C1:C6 by holding CTRL and dragging and selecting both, then choose copy. Click in your chart and then choose paste. For the new series it created, right click and chante it's chart time to an XY Scatter with straight lines.

Edit the series so that the X Axis is =C2:C6 and set the y axis to ={2,3,4,5,6} and click ok to close the dialogue boxes. (The numbers correlate to the days of the week Sunday being 1, monday 2, etc)

Now, you'll see a secondary vertical axis has appeared. Change the scale of that axis to a Min of 1.5 and a max of 6.5 which will line your target dots with your calorie bars. Then choose to hide your secondary axis

Now you can change or populate your calories/targets with whater you want. Good luck!

 

[helenkim]

yup i was. Thank you SOO much!

 

[CWatts]

Your welcome, and if you dont want lines, then just change the chart type to the XY Scatter without lines (sorry for that typo). Have fun burning calories!

 

[3xxx]

For this one
in 2010 excel

I can EITHER
produce scatter chart
or
produce line chart

the essence is creating 2 axis, one series for calorie , one series of target intake

http://i.imgur.com/ZGCId.jpg == click this URL


how to create one bar for one series, one scatter(st line) for another series??

I missed out the essence of same X-axis but 2 separate X-axis for both range ??? do you know !