CHART - can chart columns be built from text?

[itr674]

I have a table that builds a standard Excel chart based on the number of "equip codes" that are green, yellow, or red. So, the basic chart shows three columns of how many "equip codes" for a particular "unit" is green, yellow, or red.

But, I was wandering if a chart columns could be built "equip code" text, AND if a remark would show when the mouse pointed at them?

Attached is a basic view of the table and what I want the chart to look like.

I use column D "Status Code No." to sort so Green first, then Yellow and then Red.

Equip codes can be used more than once for each unit.

**I could show only 2 units so this would fit on the board...

I have to put this in a PowerPoint presentation that is the reason for the chart.

em-Aug03-3.xls
	A	B	C	D	E	F	G	H	I	J	K
1	EquipCode	Unit	Status	StatusCodeNo.	Remarks
2	Code01	Unit1	Green	1
3	Code02	Unit1	Yellow	2	Notenoughpeople
4	Code02	Unit1	Red	3	Notenoughequip
5	Code02	Unit2	Green	1
6	Code02	Unit2	Yellow	2	Notenoughequip
7	Code02	Unit2	Red	3	Notenoughequip
8	Code03	Unit2	Green	1		Chart	Showing	Status	of	Codes
9	Code03	Unit2	Green	1
10	Code05	Unit3	Green	1
11	Code10	Unit3	Green	1
12	Code11	Unit3	Red	3	Notenoughequip
13	Code12	Unit3	Red	3	Notenoughequip
14	Code13	Unit3	Red	3	Notenoughequip				Code2
15	Code14	Unit3	Yellow	2	Notenoughequip				Code3
16	Code15	Unit3	Green	1					Code3
17	Code15	Unit3	Yellow	2	Notenoughequip	Code1	Code2	Code2	Code5	Code2	Code2
18							Unit1			Unit2
Sheet1

 

[itr674]

I was just reading in JWs Excel Charts where I might be able to make table look like a chart and then copy it into PowerPoint as a picture.

Aladin - you gave me this formula that looked at the left # characters--I was thinking about modifying to work here (wouldn't need the left four characters), but how do you make a formula go from the bottom up when it works???

=IF(ROW()-ROW(B$6)+1<=C$5,IF(AFWUS_Unit_Col=ShopCriterion,INDEX(EVAL(CHAR(123)&CHAR(34)&SUBSTITUTE(TRIM(MCONCAT(IF(LEFT(AFWUS_UTC_Col,3)=B$5,AFWUS_UTC_Col,"")," "))," ",CHAR(34)&CHAR(44)&CHAR(34))&CHAR(34)&CHAR(125)),ROW()-ROW(B$6)+1),""),"")

 

[itr674]

Throwing this back out there...

 

[Jon Peltier]

I saw this the other day, and thought it could be done a better way. Now that I look it over again, I see how to do it.

Make a pivot table of your data, columns A-C of it. Put Unit and Code in the Row area, and Status in the Column area. Put Count of Status in the Data area. Put Unit subtotals in the table, but no other totals or subtotals. Sort the colors left to right in the order Green, Yellow, Red.

Select a blank cell and make a regular (not pivot) clustered column chart. When you get to the Source Data step, click on the Series tab, and Add a series. Assign the cell that says "Green" to the name of the series, assign the cells saying "Unit 1 Total", "Unit 2 Total", and "Unit 3 Total" to the categories (use Ctrl-Click to allow selection of discontiguous cells), and assign the cells with the subtotals under green to the values. Repeat to add series for Yellow and Red.

Set the fill colors of the columns to match the series names, then you can delete the legend as redundant. Double click one of the series, click on Options, and set the gap width to 100 (to facilitiate calculations later).

Now we just need the labels. We'll use a dummy XY Scatter series and its data labels for this. Column F will hold X and G will hold Y. The labels are in column A. (I deleted the pseudo chart you built in the cells to the right of the table).

Cell F2 has this formula for X:
=RIGHT(B2,1)+(D2-2)/4
Cell G2 has this array formula for Y:
{=SUM((B2:B$2=B2)*(D2:D$2=D2))-0.5}
Don't type the curly braces. Hold down Ctrl+Shift while pressing Enter, and if you've done it right, Excel inserts the braces for you. Copy F2:G2, select F3:G17, and Paste.

Copy F2:G17, select the chart, and choose Paste Special from the Edit menu to paste this data as a new series, with categories in the first column, without replacing existing categories. You get an ugly column series clustered with the others.

Right click on the new series, choose chart type from the drop down menu, and pick the XY Scatter type with markers and no lines. Right click on the chart, select Chart Options from the pop up menu, click on the Axes tab, and uncheck both secondary axes. The XY series' markers will now go just where we want them. Double click on the markers, click on the Patterns tab, and select None for Markers and Lines, to hide this series.

Now use your favorite data point labeling add-in to put the labels onto the points. Use Rob Bovey's Chart Labeler, at http://appspro.com, or John Walkenbach's Chart Tools, at http://j-walk.com. Both are free and very useful. After installing the add-in, follow instructions to add the labels from A2:A17 to the Scatter series. Format the labels to fit in the columns (I had to use 8-pt Arial Narrow).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

 

[itr674]

Jon Peltier - thanks a bunch. I am going out of town and will it this week...