Jan 2007 Challenge of the Month Discussion

[roger2]

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]

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]

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]

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]

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]

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]

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.xls
	A	B	C	D	E	F
1	Number			Skip	Stem	Leaves
2	8,848			1	78	01226789
3	8,611			1	79	3345
4	8,586			1	80	23589
5	8,516			3	81	2668
6	8,485			1	84	8
7	8,188			1	85	18
8	8,167			2	86	1
9	8,163			0	88	4
10	8,125			0
11	8,091
12	8,080
Van's Solution

 

[liyong]

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]

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