Graph

[rkoenig]

I'm pretty new to Excel (using 2000), and I have a start on a graph, my first. I cannot get it to plot correctly, probably because I have the wrong X scaling.

Actually this is a step toward my goal, which is to show data for just the hour and minute, and perhaps for a defined period, such as 8am to 8pm.

As you can tell, columns C and D are the two components of B. So ideally the graph would be a bar graph showing the total of B broken down in to the C and D parts. I've seen graphs like this, but have no clue how to do this. I'm stuck on a simple graph, so that sounds like advanced stuff to me.

Anticipating the result, though, the problem is having so many data points. 60 per hour, and even if it was limited to 8 hours, that's 480. Maybe a bar chart would look ok, but all those lines smushed together? Maybe an area graph instead?

And eventually I want to add a horizontal line that shows the 95th percentile value. I've tinkered with calculating that, but not the foggiest idea of how to layer that in.

So - a simple graph, but some interesting twists. I'd appreciate learning and help.

Rick

[Richard Buttrey]

Hi Rick,

Does the attached offer you any ideas?

As you suggest charting each individual point, whilst perfectly feasible, might look messy for 8 hours worth of data. I've added a second chart which plots the 10 minute means, which might be another option - could also be Median or Mode if you prefer.

I've assumed you've only got Excel 2003 and not 2007/2010 and hence have used SUMPRODUCT() formulae. If you had 2007 you could use the more useful AVERAGEIF() type formula.

[Andy Pope]

Your x axis values are date/time. Column charts only go down as far as individual days for date charts.
If you format the Scale to be Category rather than Automatic or Time series you will see all the individual data points.

[rkoenig]

Your x axis values are date/time. Column charts only go down as far as individual days for date charts.
If you format the Scale to be Category rather than Automatic or Time series you will see all the individual data points.

I'm grateful for your comments. Honestly, I really don't understand. I see that my suggested graph uses date/time for x axis. Column charts are day only? Yet I saw individual time periods, briefly, before it converted itself apparently to a singe day.

So how would I format the scale to be Category?

[Richard Buttrey]

Hi,

I subsequently realised I'd charted the 90th percentile not the 95th as you'd specified, but I guess you probably realise that and just need to change the 0.9 to 0.95 in the =PERCENTILE() formula.

Regards

[rkoenig]

Hi,

I subsequently realised I'd charted the 90th percentile not the 95th as you'd specified, but I guess you probably realise that and just need to change the 0.9 to 0.95 in the =PERCENTILE() formula.

Regards

Yes, I noticed that, no problem. In my previous testing I had the value as a named cell variable. Easy to change it to anything the customer wants.

Rick

[Andy Pope]

Right click chart and pick Chart Options.
On the Axis tab set the Value axis to Category.

I have altered the chart and there is screen shot of the Chart Options dialog on worksheet