Step Charts

I have occasion to need step charts in Excel to plot planned revenue vs time on programs. I know this can be done using error bars, but they're fussy and I struggle setting them up.

The alternative is creating the steps manually with additional points, which is just an extra step of fiddling with data.

I wrote a UDF that converts a set of revenue points to a step chart for plotting. It returns a two-column list with payments aggregated by period. The periods need not be in order, though they are in the example below. An option allows return of cumulative revenue (columns K:L) or cumulative percent (O:P).

	B	C	D	J	K	L	M	N	O	P
2			WBS Amt	2,180,795
3
4	Per	Milestone	Weeks ACA	Amt		Weeks ACA	*** Amt		Weeks ACA	*** Pct
5	0.	Contract Award	0	192,147		0	192,147		0	8.8%
6	1.	Milestone Name	2	124,255		2	192,147		2	8.8%
7	2.	Milestone Name	4	10,705		2	316,402		2	14.5%
8	3.	Milestone Name	4	382,639		4	316,402		4	14.5%
9	4.	Milestone Name	8	298,212		4	709,746		4	32.5%
10	5.	Milestone Name	12	38,087		8	709,746		8	32.5%
11	6.	Milestone Name	14	4,232		8	1,007,958		8	46.2%
12	7.	Milestone Name	14	298,212		12	1,007,958		12	46.2%
13	8.	Milestone Name	14	177,213		12	1,046,045		12	48.0%
14	9.	Milestone Name	18	273,361		14	1,046,045		14	48.0%
15	10.	Milestone Name	18	7,137		14	1,525,702		14	70.0%
16	11.	Milestone Name	22	74,919		18	1,525,702		18	70.0%
17	12.	Milestone Name	30	74,919		18	1,806,200		18	82.8%
18	13.	Milestone Name	38	74,919		22	1,806,200		22	82.8%
19	14.	Milestone Name	46	74,919		22	1,881,119		22	86.3%
20	15.	Milestone Name	52	74,919		30	1,881,119		30	86.3%
21			Total	2,180,795		30	1,956,038		30	89.7%
22						38	1,956,038		38	89.7%
23						38	2,030,957		38	93.1%
24						46	2,030,957		46	93.1%
25						46	2,105,876		46	96.6%
26						52	2,105,876		52	96.6%
27						52	2,180,795		52	100.0%
28						#N/A	#N/A		#N/A	#N/A
29						#N/A	#N/A		#N/A	#N/A

The function is array-entered into a range of cells tall enough to return all the points:

=StepCht(Periods, Amounts, [Scale])

I hope someone finds it useful.