Jan 2007 Challenge of the Month Discussion

[meng132]

stemdata solution.xls
	A	B	C	D	E	F	G	H	I
1	7795	8848
2	Data	Stem	Leaf
3	8519	78	3	5
4	8719	79	8
5	8090	80	1	4	9	9
6	8611	81	7
7	7858	82	4	7
8	8241	83	0	0	2	4	6
9	7981	84	9
10	8360	85	1	1	4
11	8010	86	1	3	6
12	7832	87	0	1
13	8303	88	4
Sheet2

 

[meng132]

stemdata solution.xls
	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT
1
2			Stem	Leaf	78	79	80	81	82	83	84	85	86	87	88		Leaf
3	1	85	85	1								1					3	8
4	2	87	87	1										1			5
5	3	80	80	9			9
Sheet2

 

[riteshparakh]

stemdata.xls
	A	B	C	D	E	F
7	Number	Stem	Leaf	PosMultiplier
8	39	3	9	0.10000000000	3	9
9	41	4	1	0.10000000000	4	14688
10	44	4	4	0.01000000000	5	000457
11	46	4	6	0.00100000000	6	023335666789
12	48	4	8	0.00010000000	7	1
13	48	4	8	0.00001000000
14	50	5	0	0.10000000000
15	50	5	0	0.01000000000
16	50	5	0	0.00100000000
17	54	5	4	0.00010000000
18	55	5	5	0.00001000000
19	57	5	7	0.00000100000
20	60	6	0	0.10000000000
21	62	6	2	0.01000000000
22	63	6	3	0.00100000000
23	63	6	3	0.00010000000
24	63	6	3	0.00001000000
25	65	6	5	0.00000100000
26	66	6	6	0.00000010000
27	66	6	6	0.00000001000
28	66	6	6	0.00000000100
29	67	6	7	0.00000000010
30	68	6	8	0.00000000001
31	69	6	9	0.00000000000
32	71	7	1	0.10000000000
Proposed Solution

[/img]

Try this

 

[F1A1G1inDC]

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]

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]

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

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

 

[jg1980]

Hi,

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

Regards,

 

[hady]

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]

Elegant Formula for stem and leaf

edited sorry for double post

Hady

 

[hady]

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]