Graph with stepped increases or decreases

[skiwidad]

I would like a graph which reflects the true position of inventory/goods on
hand. Currently we have a line graph with quantity on the x-axis and dates on
the y-axis. The problem is that the if you have 30 units on the 1st May and
then 50 units come into store on the 15th May the line joining the two points
is a slope which incorrectly shows stock increasing consistently from the 1st
of May until the 15th of May. The correct graph would show a line of 30 units
along the period 1st to 14th May and then jump to 80 units on the 15th May.
Is there a graph type which reflects this situation?

Thank you for your assistance.

 

[Lori Miller]

Create an ordinary line (or area) chart and in Chart Options make sure to
set the category axis to time / date scale. Now select your data series and
change your series formula so that the values are duplicated but offset one
row.
eg:
=SERIES(,Sheet1!A2:A10,Sheet1!A2:A10,1)
to:
=SERIES(,(Sheet1!A3:A10,Sheet1!A2:A10),(Sheet1!B2:B9,Sheet1!B2:B10),1)

 

[skiwidad]

Thanks for the reply. I couldn't get this to work I think I see what you are
suggesting but my dates are random (the stock can arrive and depart on any
day over a non fixed period of time). I am guessing that your suggestion
works if you have an evenly spaced period of time?

 

[Jon Peltier]

Spacing doesn't matter. Lori's first equation is incorrect, though, it
should be

=SERIES(,Sheet1!A2:A10,Sheet1!B2:B10,1)

with dates in column A and values in column B. When you almost double the
ranges, you get her second formula, which is correct:

=SERIES(,(Sheet1!A3:A10,Sheet1!A2:A10),(Sheet1!B2:B9,Sheet1!B2:B10),1)

Note the dates are A3:A10 and A2:A10 (A2 is missing from the first repeat),
and the values are B2:B9 and B2:B10 (B10 is missing from the first repeat).

I have written about step charts in my blog and web site:
http://peltiertech.com/WordPress/line-chart-vs-step-chart/
http://peltiertech.com/Excel/ChartsHowTo/StepChart.html

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______

 

[Jon Peltier]

I didn't mean to step all over Lori's post. I mentioned my web articles
because in one or both of them I showed how the data looks to the chart's
time scale axis, which internally sorts the data. Sometimes it helps to see
something explained two or three different ways, before that stubborn light
bulb turns on.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______