Jan 2007 Challenge of the Month Discussion

[Bman-Belgium]

Stem and Leaves

Why not use Pivot's?
In 4 steps, I've got what you want:
1) Define 'Stem' in next column as =INT(A12/10)
2) Define 'Leaf' in next column as =A12-B12*10
3) Make a Cross Pivot Table with Average of 'Leaf' as data, and define 'Stem' in a row, 'Leaf' in a column
4) Right next to this pivot table, you repeat the 'Stem' and just unite each corresponding Pivot row-data with formula =F4&G4&H4&I4&J4&K4&L4&M4&N4&O4&P4

(I wasn't able to insert an image or my worksheet, but if you want, I'll send it through)

Martin

 

[markyc]

Re: Stem and Leaves

Why not use Pivot's?
In 4 steps, I've got what you want:
1) Define 'Stem' in next column as =INT(A12/10)
2) Define 'Leaf' in next column as =A12-B12*10
3) Make a Cross Pivot Table with Average of 'Leaf' as data, and define 'Stem' in a row, 'Leaf' in a column
4) Right next to this pivot table, you repeat the 'Stem' and just unite each corresponding Pivot row-data with formula =F4&G4&H4&I4&J4&K4&L4&M4&N4&O4&P4

(I wasn't able to insert an image or my worksheet, but if you want, I'll send it through)

Martin

The only problem with above method is that you don't capture repeated values. You could perform a count but then you will have to use the REPT() function to repeat the Leaf 'count' number of times. This addition will make the formula/method more complicated and it makes using the countif method appear simpler.

Regards,
Mark

 

[DougJ]

If there was an application where circular references can be justified, this would be it!

In Tools-Options-Calculation, set iterations to 10.

Using the layout in the spreadsheet supplied enter:

F1: 10
G1: 0 (This cell will initialise the circular references)
H1: =IF(G1=0,0,IF(H1=11,11,H1+1))
C2: =SUM(IF((INT($D$2:$D$26/$F$1)=$F2)*(MOD($D$2:$D$26,$F$1)=$H$1-1),1,0))
and copy down to C6 as array functions
G2: =IF($G$1=0,"",IF(AND(C2>0,$H$1<11),G2&REPT($H$1-1,C2),G2))
and copy down to G6

Now enter 1 in cell G1, and hey presto!

Alternatively set iterations to 1, and press f9 10 to generate the chart.

 

[fractaloon]

Why not use Pivot's?
In 4 steps, I've got what you want:
1) Define 'Stem' in next column as =INT(A12/10)
2) Define 'Leaf' in next column as =A12-B12*10
3) Make a Cross Pivot Table with Average of 'Leaf' as data, and define 'Stem' in a row, 'Leaf' in a column
4) Right next to this pivot table, you repeat the 'Stem' and just unite each corresponding Pivot row-data with formula =F4&G4&H4&I4&J4&K4&L4&M4&N4&O4&P4

(I wasn't able to insert an image or my worksheet, but if you want, I'll send it through)

Martin

I was thinking a pivot chart could easily solve this problem. Use the above method, but instead of showing Average of "Leaf" in step 3, show a count of Leaves. Then create a pivot chart from this data with horizontal stacking bars.

I will try to upload a sample of it tonight when I get home.

Cheers,
Albert

 

[Bman-Belgium]

If I modify my Pivot to show 'Count Nums' of 'Leaf' instead of 'Average', I get the number of occasions (cq. repeated values).

After that, I use REPT function, based on the column-header of the Pivot (row 36), as follows for each stem-row:
=REPT($F$36,F37)&REPT($G$36,G37)&REPT($H$36,H37)
&REPT($I$36,I37)&REPT($J$36,J37)&REPT($K$36,K37)
&REPT($L$36,L37)&REPT($M$36,M37)
&REPT($N$36,N37)&REPT($O$36,O37)

This works for 'Count Nums' equal to blank, because that result will also be blank.

Regards,
Martin


P.S I haven't enough knowledge of Pivot Charts to see if that could fit in.

 

[fractaloon]

Pivot Chart

Here's the results of using a pivot chart

 

[DougJ]

Results of the circular reference formula:

Initialised (G1 = 0)

After 10 calculations (G1 = 1, min iterations=10)

 

[Excelli]

My thinking is that if you know each data point's stem,leaf, and the exact leaf position, you should be able to chart it or pivot it. If input range is A2:A26, These formulas will give the 3 parameters to data point A1:

Stem B2: =INT(A2/10)
Leaf C2: =A2-B2*10
Position D2: =COUNTIF($A$2:$A26,"<"&A2)-COUNTIF($A$2:$A$26,"<"&B2*10)+COUNTIF(A$2:A2,A2)

Fill it down to row 26 to get all data. Then use a pivot table or xy chart to generate the end results. A pivot table would use sum of leaf as data, stem as row, and position as column. A XY Plot chart employs stem as y-axis, position as x-axis; data points are formatted invisible, and leaves used as data labels.

 

[DougJ]

Excelli - neat

To assign the data labels to a range (other than the x or y range for the series) I had to use an addin (XY Chart Labeler from www.appspro.com).

Is there any other way to do it?

 

[Excelli]

DougJ, you probably already know that you can click on the individual label and reference it to the target cell. In this case, you have to do this step 25 times to get the chart all labeled correctly. The famous xy-chart-labeler add-in does make it much faster.