Excel chart help

[raven22]

Hello Guys,
I have been trying to create a certain chart and am having lots of problems. First off, can I change the X axis to what I need exactly? I've gone to the scale option but it won't do what I want. I need to see times starting from 18:00pm to 8:00am. It doesn't even have to be a time format, I would just like to see numbers 18,19,20,etc. I have other questions but if I can't do this part, the rest is no good. Any help would be appreciated.

 

[SkipVought]

Hi,

What chart type are you using? Is your x-axis a CATEGORY or VALUE axis? You want it to be a VALUE axis.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[raven22]

HI,
I'm sorry,it should be the Y axis, I am using a bar chart.
I see where it asks for the X axis labels and I just tried that for fun but it will start at 18 and stop at 24. I will explain what I need in case it's not possible, I don't want to waste anyone's time....
I have 5 servers that run several backups each night I and want to track the start and end time of each backup. I would like to see one row per server showing each job and when it started and stopped. I'm having a heck of a time with this!

 

[SkipVought]

Please post a sample table of data that you want to plot and how you want it ploted.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[raven22]

Ok, here we go...

server job start time end time

server1 job1 18:00 18:45
job2 19:00 20:00
job3 21:00 21:30

server2 job1 18:30 19:30
job2 23:00 24:00
job3 01:30 02:30

I'd like to see one bar going across the chart for each server with a different color block for each job showing when the job started and stopped. Am I asking for too much from the chart wizard?

 

[SkipVought]

Here's how I reformatted the data. What is needed for each server is start time and THEN SPAN-TIMES and SKIP TIMES or DIFFERENCE in TIMES for each subsequent point
[tt]
job1 job1 job1 job2 Job2 job2 job2 job3 Job3 job3 job3
server start end Span start Skip end Span start Skip end Span
server1 18 18.75 0.75 19 0.25 20 1 21 1 21.5 0.5
server2 18.5 19.5 1 23 3.5 24 1 25.5 1.5 26.5 1
[/tt]
You start the chart by selecting ONLY the Server & Start columns

Select the Stacked Bar chart Type

Then in the Chart Wizard Step 2, Series tab you will Add the remaining series by

1. placing your cursor in the Values textbox and THEN

2. selecting the column of data (span or skip).

The result is that the RANGE REFERENCE is transfered to the Value for the new range.

Complete the steps thru the chart wizard

Then select the first series in the chart -- Format the series and set the Border and Area to NONE. SAME for each Skip series.

VOLA!

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

BTW,

Here are some other things to keep in mind.

1) When your TIME values reach MIDNIGHT, you have to ADD one day to the time value and all time values thereafter.

2) You may want to set a Minimum Value on the x-axis Scale other than ZERO. Otherwise there's a big gap before the first job.

3) there's a way to use formulas for the values in MY REFORMATTED TABLE. If you want to prusue that, let me know.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[raven22]

Hi Skip,
Thanks for your help. It will take me a while to get through this, I've only ever made the simplest of charts.
I will work on this this afternoon.
Thanks again.

 

[SkipVought]

This will not be a simple chart, but I have a working model. If you'd like me to send it to you, please post your eMail address.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[raven22]

Yes, that would be great! My email address is suzanne.harvey@ballin.com

Thanks.