line chart - time format

[carics]

Hi mates,

I have a series of data with 3 columns:
A = date
B = time (hh:mm)
C = stock

I want to make a chart with the time evolution of the stock. The problem is that in some days I have 6 different entries (different times and different stock) and in others I have 1 or even 0 entries.
How can I make a chart that has all days with proportional length in the x axis?


Thank you for taking a shot at it.

tiago

 

[Faseeh]

Can you please upload a sample file

 

[carics]

Sure! It is very simple as an example.
I forgot to mention that this file is to be filled in by different people, so the number of entries per day will vary a lot.
In this example I would like all days to have the same length in the axis and also the two missing dates in the range (5 and 6/jan) to appear.
I do not have the need of having the time mark written in the axis values, only the date.

 

[Hui]

I;

1. Combined the date and Time in 1 column
2. Changed the chart to an X-Y Scatter Chart
3. Adjusted X-Axis number format and Spacing
4. Added Vertical grid lines to show each day
5. Used Named Formula for the Chart so that it will expand as data is added automagically

See attached file:

 

[carics]

Hello Hui,

Thank you very much for the tips. I think I never messed with scatter charts before, so it did not come to mind to change chart type.

I have adjusted it to the real data set, adding other cool features I had in my mind from the beginning.
Nevertheless I cannot get the chart to take data from different hours in one day. This means that if there are two different stock levels in one day, I just get a vertical line between min and max values. Maybe it has some to do with the #n/a values I added to limit the chart range.

I attach the file with comments in blue; what did I do differently?

thank you!

 

[andrewjohn1317]

Hi Hui,

Great Tip's it’s very useful and more specific details, the more like that an Expert will be able to advice.Thank you very much for the tips

 

[carics]

Any idea?

 

[Faseeh]

@carics,

Have you tried Hui's tips?

 

[carics]

I did, but...

"...I cannot get the chart to take data from different hours in one day. This means that if there are two different stock levels in one day, I just get a vertical line between min and max values."

I uploaded a new file with the result in my post from April 13 and there is something wrong.

 

[Faseeh]

Hi carcis,

Please see attachment.

 

[carics]

Hi Faseeh,

Thanks for the reply, but I don't really know what I am looking at. I get the chart you made in the same way as mine - only one time per day. The difference is that on mine I have date+hour and on you do not.

 

[Faseeh]

..get the chart you made in the same way as mine - only one time per day.

What i understood from your post was that you have multiple values that you want to plot on a single date showing max, min and some other values in between for a date. The ideas is similar to plotting stock values (a stock chart in other charts) like max, min and values, opening and closing values and values in between. If that is true, then my chart is doing the same thing. If not then please explain your idea further.

 

[carics]

The file I uploaded before works perfectly except for one point.
This point is working in Hui's file, which is that inside a day there are several points (in 01-01-2015 there is one for 00:00, other for 06:12, for 12:36, etc.). In my file it seems that one day is only one specific point so I cannot see the stock evolving from one hour to the other.

 

[Faseeh]

Hi carics,

Please see attached file, if it meets the requirment.

 

[carics]

If you see Hui's file, the vertical axis has the stock levels, and the horizontal axis has the days. But inside those days there is hour information, so that from 1Jan to 2Jan we have multiple stock updates.
That is what I wanted and I still cannot get in my file.

 

[Faseeh]

Please see attachment.

If that is not correct i will request you to draw one using paper and pencil and post the picture. Thanks.

 

[carics]

That is exactly what I want. But can you do that in the file I uploaded? I cannot get the same result you have. Yes, my file has other series of data, but the end result is not what I expected.

I look at your settings and for once I cannot even have my axis parameters the same as yours; mine is selecting the data as Days, so I cannot have min/maj unit the same as yours, for example...

 

[Faseeh]

Hi carics,

Glad to know that it was finally one that you wanted. Please see this, it is ur file. If it is ok then i will tell how to do it. Its very simple.

 

[carics]

Thanks again for your help Faseeh. I posted a "final" file on the 13th of April. That is the one I cannot modify.

 

[Faseeh]

Please see sheet 2 of attachment. I think what is still required is an hourly time line on x-axis. But partly the requirement is fulfilled.

 

[carics]

Again thank you very much for the help.
I could not understand what was going wrong so I re-took all the steps and tried to copy your chart. I now see that the problem came from adding two series of data as area chart and this was breaking my previous scattered chart. I added all the other data series without problem and they looked perfect until then.

So now I have a different question: in that same chart I want to see if all or most of my scattered points are in target (meaning over minimum and under maximum). That was the reason why I needed the area chart series, as they were marking the green zone where all dots should land. Is it possible to have this in a scattered chart?

 

[Faseeh]

Ok, Please see attachment for Max and Min line at 24.2 and 50.4..