Mode.Mult giving me problem

[asad]

Hello Guys,

I have used the function Mode.Mult with success in the past, but for some reason it is not working for me now. I have the following range

[TABLE="width: 48"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]9[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #c5d9f1, align: right"]55[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]92[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]-32[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]-51[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: yellow, align: right"]14[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]48[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]37[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: yellow, align: right"]14[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]-29[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]-8[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #c5d9f1, align: right"]55[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]-59[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]133[/TD]
[/TR]
</TBODY>[/TABLE]

When I use mode.mult and enter as an array, it gives me only 55 as the answer. It is same when I use mode, or muode.mult without array. The answer I want is 14, 55.

Thanks for all the help.

Asad

 

[AhoyNC]

Try this -
In the example below I selected cells C2 & C3 (you need to select enough rows to cover the modes) then put in the formula and confirmed with

Excel Workbook
	A	B	C
1	9		Modes
2	55		55
3	92		14
4	-32
5	-51
6	14
7	48
8	37
9	14
10	-29
11	2
12	-8
13	55
14	-59
15	133
CTRL-SHIFT-ENTER.

 

[asad]

Thanks for that. But, I thought that the answers will be in one cell and shown as 14,55.

 

[asad]

Is there another way for achieving that sort of result (14,55) in same cell?

 

[Michael M]

Only by joining them in another cell I believe

Excel 2010
	A	B	C
1	9		Modes
2	55		55
3	92		14
4	-32		55,14
5	-51
6	14
7	48
8	37
9	14
10	-29
11	2
12	-8
13	55
14	-59
15	133
Charlie

Cell Formulas
Range	Formula
C4	=C2&","&C3
C2:C3	{=MODE.MULT(A1:A15)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[asad]

Thanks Michael.

But the real issue is that the data is huge and the number of answers will be different in each instance (1000s of them). So I cannot have a general formula for set number of cells to be included in mode.mult function.

Anyway, if there is no work around to my problem, I guess I will have to live with it.

Thanks a lot.