Solved

Time Intervals on X axis PivotChart

Posted on 2011-09-30
5
850 Views
Last Modified: 2012-06-21
Hi

I have a pivot chart w/ an x axis w/ times.

Example

4:30, 4:31, 4:32, etc.... going all the way up to 10pm

I want to know how to make it look clean

Right now its it trying to display every value which looks liek way too much on a graph.

If i group by hours it doesnt really do the trick. Maybe i can group by 15-30 min intervals?

Any suggstions?
0
Comment
Question by:kwarden13
5 Comments
 
LVL 10

Expert Comment

by:SANTABABY
ID: 36892725
Could you please attach a sample?
0
 
LVL 8

Expert Comment

by:WileECoyote45305
ID: 36894115
Look at this article on how to use timescale values on the X-axis - one example for dates, and one example for times:
http://www.mrexcel.com/articles/excel-time-series-chart.php

I tried the procedure for the time values and was able to successfully set up a clean chart with time values on the x-axis. I used 15 minutes as the Major Unit (0.01042) and let it auto-pick the Minor Unit on the Scale tab. Use the directions to help determine the values for Minimum and Maximum based on your range of time values.

So although I had data for times such as 4:30, 4:31, 4:52, 5:05, 5:12, 6:27, 7:09 and 10:00, I had a chart with x-axis labels of 4:00, 4:15, 4:30, 4:45, etc. all the way to 10:00 and all the points plotted properly.
0
 
LVL 8

Expert Comment

by:WileECoyote45305
ID: 36894133
My comment was for controlling the x-axis labels. If you really do have a data point for every minute, what I did doesn't address all the plotted values in the chart, but cleans up the x-axis. Sorry if I misunderstood.
0
 

Author Comment

by:kwarden13
ID: 36894158
Attached is a sample
TestBook.xls
0
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
ID: 36896201
Hello,

does that sample data span several days? The cells in column A show only time values and they seem to be in roughly hourly increments, around 23, 24 or 29minutes after the full hour. Formatting the Time values with General, though,  I can see that some of them have a 1 in front of the decimal, others have a zero. In Excel terms that means that some time values are on a different day than others. Some have no date, some have a date of 1/1/1900.

Is that the intention? Before you can do any meaningful pivot analysis, you will need to clean up your data. There either should be a date value with EVERY time value or with NONE.

If you create a pivot table of that data and want to group in 15 or 30 minute increments, then you will need data that fits that pattern. The sample data comes in hourly increments. What good would 15 or 30 minute increments do?

So, either post a sample set that is more representative of your real data, explain some more what the data is supposed to portray and how you want to summarise it.

You may need to use a helper column in the source file to create a meaningful pivot chart, but that can only be determined if you provide some more info on what the data shows and how you want to summarise it.

For example: You can have several data items per hour for a series of days. The pivot chart should disregard the date and just summarize the data by its time slot, so you see a statistic of values typical for the hour of the day (summing up all data for 9am, 10am, 11am, etc, across ALL days). In this case, the X axis would only show the time and the lines would be the sum (or average) across all days.

Or: You have several dozen data items per hour for a series of days. The pivot table should show the data in a time line, but summarize data in 15 minute blocks per day. The X axis would show each day, broken down into time slots.

Which of these do you want to show?

cheers, teylyn
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now