# Jan 2007 Challenge of the Month Discussion



## MrExcel (Jan 2, 2007)

The January 2007 challenge of the month is to find a better way to create stem and leaf charts in Excel. See the original problem at http://www.mrexcel.com/challenge.shtml


----------



## Oaktree (Jan 2, 2007)

Hi, Bill!  

I see you're looking for the "best non-VBA solution", but does "non-VBA" also exclude using the morefunc.dll add-in everyone should have?

Seems that something like

H2: {=SUBSTITUTE(MCONCAT(SMALL(IF(INT($A$2:$E$6/10)=G2,MOD($A$2:$E$6,10)),ROW(INDIRECT("1:"&SUMPRODUCT(--(INT($A$2:$E$6/10)=G2)))))),"FALSE","")} copied down would work...


----------



## MrExcel (Jan 2, 2007)

I would say using MoreFunc.dll is valid, although if someone does it without MoreFunc.dll, then this would be more elegant...


----------



## northwolves (Jan 3, 2007)

H2=REPT(0,COUNTIF(A$8:A$32,G2*10)) &SUBSTITUTE( SUMPRODUCT(ROW($1:$9)*(10^COUNTIF(A$8:A$32,G2*10+ROW($1:$9))-1)*10^(COUNTIF(A$8:A$32,">"& G2*10+ROW($1:$9))-COUNTIF(A$8:A$32,">"& G2*10+10))/9),0,"")

Drop it down to H6


----------



## northwolves (Jan 3, 2007)

Another:
H2=REPT(0,COUNTIF(A$8:A$32,G2*10)) & SUMPRODUCT(ROW($1:$9)*(10^COUNTIF(A$8:A$32,G2*10+ROW($1:$9))-1)*10^(COUNTIF(A$8:A$32,">"&G2*10+ROW($1:$9))+MATCH(G2*10+9,A$8:A$32)-26)/9)

Drop it down to H6


----------



## Trances (Jan 4, 2007)

Where can i find a list of past Challenges and submissions


----------



## xld (Jan 4, 2007)

Here is a asolution that works on the original grid. It does put the results into separate cells, so it has to be copied across however many cells that there are in the grid (a use for Excel 2007?)

=IF(ISERROR(INT(SMALL(IF($A$2:$E$6-MOD($A$2:$E$6,10)=$G2*10,MOD($A$2:$E$6,10)*10^5+ROW($A$2:$E$6)*10^2+COLUMN($A$2:$E$6),""),COLUMN(A$1))/10^5)),"",
INT(SMALL(IF($A$2:$E$6-MOD($A$2:$E$6,10)=$G2*10,MOD($A$2:$E$6,10)*10^5+ROW($A$2:$E$6)*10^2+COLUMN($A$2:$E$6),""),COLUMN(A$1))/10^5))

of course it is an array formula.


----------



## xld (Jan 4, 2007)

It can be reduced to 

=INT(SMALL(IF($A$2:$E$6-MOD($A$2:$E$6,10)=$G2*10,MOD($A$2:$E$6,10)*10^5+ROW($A$2:$E$6)*10^2+COLUMN($A$2:$E$6),""),COLUMN(A$1))/10^5)

and use conditional formatting to hide the errors.


----------



## markyc (Jan 5, 2007)

> Hi, Bill!
> 
> I see you're looking for the "best non-VBA solution", but does "non-VBA" also exclude using the morefunc.dll add-in everyone should have?
> 
> ...



Furthermore using morefunc.dll then this can be rewritten as:

H2: {=MCONCAT(REPT(ROW($1:$10)-1,COUNTIF(A$8:A$32,G2*10+ROW($1:$10)-1)))}

I'm trying to think of a way using pivot tables but no luck thus far.
Regards,
Mark


----------



## northwolves (Jan 5, 2007)

H2=REPT(0,COUNTIF(A$8:A$32,G2*10))& SUMPRODUCT(ROW($1:$9)*INT(10^COUNTIF(A$8:A$32,G2*10+ROW($1:$9))/9)*10^(COUNTIF(A$8:A$32,">"&G2*10+ROW($1:$9))+MATCH(G2*10+9,A$8:A$32)-25))


----------



## Bman-Belgium (Jan 9, 2007)

*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 (Jan 9, 2007)

*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
> ...



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 (Jan 11, 2007)

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 (Jan 11, 2007)

> 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
> ...



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 (Jan 11, 2007)

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 (Jan 11, 2007)

*Pivot Chart*

Here's the results of using a pivot chart


----------



## DougJ (Jan 11, 2007)

Results of the circular reference formula:

Initialised (G1 = 0)





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


----------



## Excelli (Jan 12, 2007)

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 (Jan 12, 2007)

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 (Jan 15, 2007)

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.


----------



## meng132 (Jan 18, 2007)

stemdata solution.xlsABCDEFGHI1779588482DataStemLeaf385197835     48719798      58090801499   68611817      778588247     882418300246  97981849      10836085114    11801086136    1278328701     138303884      Sheet2


----------



## meng132 (Jan 18, 2007)

stemdata solution.xlsACADAEAFAGAHAIAJAKALAMANAOAPAQARASAT12StemLeaf7879808182838485868788 Leaf3185851       1    384287871         1  5 5380809  9           Sheet2


----------



## riteshparakh (Jan 25, 2007)

stemdata.xlsABCDEF7NumberStemLeafPosMultiplier839390.1000000000039941410.100000000004146881044440.0100000000050004571146460.0010000000060233356667891248480.00010000000711348480.000010000001450500.100000000001550500.010000000001650500.001000000001754540.000100000001855550.000010000001957570.000001000002060600.100000000002162620.010000000002263630.001000000002363630.000100000002463630.000010000002565650.000001000002666660.000000100002766660.000000010002866660.000000001002967670.000000000103068680.000000000013169690.000000000003271710.10000000000Proposed Solution[/img]

Try this


----------



## F1A1G1inDC (Jan 25, 2007)

*My Take on This*

IF(LEFT(A2,1)=LEFT(G2,1),RIGHT(A2,1),"")&IF(LEFT(B2,1)=LEFT(G2,1),RIGHT(B2,1),"")&IF(LEFT(C2,1)=LEFT(G2,1),RIGHT(C2,1),"")&IF(LEFT(D2,1)=LEFT(G2,1),RIGHT(D2,1),"")&IF(LEFT(E2,1)=LEFT(G2,1),RIGHT(E2,1),"")


----------



## cgordon (Jan 26, 2007)

This works for descending order with the data in column A

D2: {=SUM((A$2:A$26-C2*10)*IF(LEFT(A$2:A$26,1)=""&C2,""&10^(ROW(A$2:A$26)-MATCH(MAX(IF(LEFT(A$2:A$26,1)=""&C2,A$2:A$26,"")),A$1:A$26,FALSE)),"0"))}


----------



## cmvpjones (Jan 27, 2007)

Leaves
B2=A2-Rounddown(A2,-1)

Number of Leaves
C2=COUNTIF($A$2:$A$26,"<40")-SUM(C$1:C1)


----------



## jg1980 (Jan 27, 2007)

Hi,

Step 1: Set BIN
Step 2: {+Rept("*",FREQUENCY(A2:E6,G2:G6)}
Step 3: Enjoy your Stem & Leaf Plot






Regards,


----------



## hady (Feb 2, 2007)

am i too late joint this challenge?   
this is simplify Jonathan garcia which no need Bin helper cell and only show the chart

=REPT("$",FREQUENCY(A2:A26,VALUE(C2:C6&9)))


this one  showing the numbers

=SMALL(IF(VALUE(LEFT($A$2:$A$26))=C2,VALUE(RIGHT($A$2:$A$26))),COLUMN($1:$25))

check this on file

```
http://rapidshare.com/files/14623180/jan2007cl_3KB.rar.html
```


----------



## hady (Feb 3, 2007)

*Elegant Formula for stem and leaf*

edited sorry for double post

Hady


----------



## hady (Feb 3, 2007)

*Elegant Formula for stem and leaf*

I have an elegant way to create stem and leaf chart without any helper cells and no VBA , no UDF or no excel add in just common/native formula in excel.  

Assume the data on cell A2:A26, 

put formula on C2 

```
=MIN(IF(INT($A$2:$A$26/10)>C1,INT($A$2:$A$26/10)))
```
execute as CSE

put formula on D2

```
=TEXT(SUM(IF(ISERROR(LARGE(IF(VALUE(LEFT($A$2:$A$26))=C2,VALUE(RIGHT($A$2:$A$26))),COLUMN($1:$25))),0,LARGE(IF(VALUE(LEFT($A$2:$A$26))=C2,VALUE(RIGHT($A$2:$A$26))),COLUMN($1:$25)))*10^(COLUMN($1:$25)-1)),REPT(0,INDEX(FREQUENCY($A$2:$A$26,VALUE($C$2:$C$6&9)),ROW()-1)))
```
execute as CSE

select C2:D2
drag down as required
you got stem and leaf diagram in elegant way

check this out on my excel sheet on below link
http://rapidshare.com/files/14791662/MrExcel_January_07_challenge.rar.html

regards,
Hady
XL-Mania

[Edited to make link clickable~admin]


----------



## roger2 (Feb 5, 2007)

bit late, but how about this one? It uses a data table, for 'elegance'.

- make 5x5 array of leaves for 1 stem:
              (=IF((A2-MOD(A2,10))/10=$G$2,MOD(A2,10),""))
(assuming original 5x5 starts in a2, stem is in G2)
- concatenate these 25 items: concatenate(h2, h3, etc)....assuming new array of leaves starts in h2)

- use data table to make table for other stems

(couldn't find the 'upload file' button....let me know if i won, then i'll send the .xls)

regards, roger


----------



## hjk (Feb 9, 2007)

Hi~
Bit late, too.
The following will be funny.

H2: =RIGHT(LEFT(RIGHT(TEXT(SUM(IF(VALUE(LEFT($A$2:$E$6,1))=G2,VALUE(RIGHT($A$2:$E$6,1)*0.00001^(COLUMN($A$2:$E$6)-1)*0.1^(ROW($A$2:$E$6)-1)))),"#.0000000000000000000000000"),25),25-COUNTIF($A$2:$E$6,">="&(G2+1)*10)),25-COUNTIF($A$2:$E$6,">="&(G2+1)*10)-COUNTIF($A$2:$E$6,"<"&(G2*10)))

Very long! Don't forget to be arrayed.


----------



## Domenic (Feb 17, 2007)

For what it's worth...

D2, copied down:

=TEXT(SUM(LARGE((INT(A$2:A$26/10)=C2)*RIGHT(A$2:A$26),ROW(INDIRECT("1:"&ROWS(A$2:A$26))))*10^ROW(INDIRECT("1:"&ROWS(A$2:A$26)))/10),REPT(0,COUNT(1/(INT(A$2:A$26/10)=C2))))

...where A2:A26 contains the data, and C2:C6 contains the stem.  Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER.


----------



## twinetwstr (Feb 20, 2007)

*Spreadsheet Solution*

I did a solution to this problem using a section of the spreadsheet for the workspace, would this be an OK solution, or were you looking for one formula to do all the work?  The formulas submitted certainly do work, but I like solutions that break the problem down into simpler tasks.


----------



## liyong (Feb 23, 2007)

H2{=RIGHT(REPT(0,25)&SUM(SMALL(IF(INT($A$2:$E$6/10)=G2,MOD($A$2:$E$6,10),0),ROW($1:$13))*10^(13-ROW($1:$13)))&SUM(SMALL(IF(INT($A$2:$E$6/10)=G2,MOD($A$2:$E$6,10),0),ROW($14:$25))*10^(12-ROW($1:$12))),COUNTIF($A$2:$E$6,">="&G2*10)-COUNTIF($A$2:$E$6,">="&(G2+1)*10))}


----------



## twinetwstr (Feb 24, 2007)

*SUper-easy Stem Chart*

To me, the value of Excel is the ability to make a sheet ANYONE can use to perform a task.  Eligant to me means making something a person can use without much training to perform a variety of tasks.

So my solution is a locked spreadsheet that hides the "works" and allows only a couple of areas for input.  I have a collumn for up to 100 numbers, a stem chart showing 10 stems (starting with the lowest available stem from the input data), and two other "adjustments".

The first adjustment allows the user to skip stem values that have no leaves - otherwise showing consecutive stem values.

The second adjustment allow the user to discard the rightmost "x" digits from the input numbers, allowing the Mountain Range data from Wikipedia to be input.

I would post the sheet, but I see no way to attach it.


----------



## twinetwstr (Feb 24, 2007)

*Here is the visible portion of my solution.*

Ok, I found the Html Converter to make a postable format, but could post only the visable output, not the "works". Hopefully there is enough here to give you the idea, eventhough I could not get the formatting to come out like I have it on the sheet.  If you use the 2 digit information, th range of the Stems is auto-adjusting, and you just set a cell (not visible but in $e$26) to 0.StemChart.xlsABCDEF1NumberSkipStemLeaves28,848  1780122678938,611  179334548,586  1802358958,516  381266868,485  184878,188  1851888,167  286198,163  0884108,125  0  118,091    128,080  Van's Solution


----------



## liyong (Feb 28, 2007)

> H2=REPT(0,COUNTIF(A$8:A$32,G2*10)) &SUBSTITUTE( SUMPRODUCT(ROW($1:$9)*(10^COUNTIF(A$8:A$32,G2*10+ROW($1:$9))-1)*10^(COUNTIF(A$8:A$32,">"& G2*10+ROW($1:$9))-COUNTIF(A$8:A$32,">"& G2*10+10))/9),0,"")
> 
> Drop it down to H6



很高兴在这儿又遇到你！在excelhome见过你的贴子，曾经

你的方法有个问题，就是假如有多于15个相同的数字时会出现问题 ，比如数据中有16个 36，


----------



## MrExcel (Mar 2, 2007)

Thanks to everyone who entered. A discussion and the winners are posted at http://www.mrexcel.com/pc16.shtml


----------

