Mode.Mult in dynamic table, excluding zeros, and hiding errors

[larsensation]

Hi,

I have a table that updates as data is entered into another sheet. I want to find the mode of this data, but exclude the zeros, since there is a lot of zeros, because data has not been entered into the original sheet yet.

Here is the formula currently being used: {=IFNA(MODE.MULT(IF(A4:X18<>0,A4:X18)),"-")}

I am getting the multiple modes, but i am still getting error messages for the spaces that don't have a value. I would like to change the #N/A to - until the data is entered and it would change automatically.

Thanks for your help!

 

[Eric W]

Welcome to the board.

I assume you'e getting something like column Y here:

	Y	Z	AA
-	-
-	-
-	-
-	-
-	-
-	-
-	-

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]#N/A[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]Y1:Y10[/TH]
[TD="align: left"]{=IFNA(MODE.MULT(IF(A4:X18<>0,A4:X18)),"-")}[/TD]
[/TR]
[TR]
[TH]Z1:Z10[/TH]
[TD="align: left"]{=IFERROR(SMALL(MODE.MULT(IF(A4:X18<>0,A4:X18)),{1;2;3;4;5;6;7;8;9;10}),"-")}[/TD]
[/TR]
[TR]
[TH]AA1:AA10[/TH]
[TD="align: left"]{=IFERROR(SMALL(MODE.MULT(IF(A4:X18<>0,A4:X18)),ROW(INDIRECT("1:10"))),"-")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]




You select Y1:Y10, enter the formula in Y1 and confirm with Control+Shift+Enter, and you get the #N/A values after the real results. The #N/A values are appearing because the MODE.MULT is returning a 3-element array, and you selected 10 rows, so the final 7 rows have nothing to put in the cells. This is really determined after the formula (including the IFNA) has been processed. One way around that is to create an array that is the same size as the number of rows in your output selection. The Z1 formula looks for the 10 smallest values in the array returned by MODE.MULT. If there are less than 10 values, the extra will return #NUM , which IFERROR will convert to -. If you want a much longer list, you can use the version in AA1.

Hope this helps.

 

[larsensation]

Welcome to the board.

I assume you'e getting something like column Y here:

	Y	Z	AA
-	-
-	-
-	-
-	-
-	-
-	-
-	-

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]#N/A[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]Y1:Y10[/TH]
[TD="align: left"]{=IFNA(MODE.MULT(IF(A4:X18<>0,A4:X18)),"-")}[/TD]
[/TR]
[TR]
[TH]Z1:Z10[/TH]
[TD="align: left"]{=IFERROR(SMALL(MODE.MULT(IF(A4:X18<>0,A4:X18)),{1;2;3;4;5;6;7;8;9;10}),"-")}[/TD]
[/TR]
[TR]
[TH]AA1:AA10[/TH]
[TD="align: left"]{=IFERROR(SMALL(MODE.MULT(IF(A4:X18<>0,A4:X18)),ROW(INDIRECT("1:10"))),"-")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]




You select Y1:Y10, enter the formula in Y1 and confirm with Control+Shift+Enter, and you get the #N/A values after the real results. The #N/A values are appearing because the MODE.MULT is returning a 3-element array, and you selected 10 rows, so the final 7 rows have nothing to put in the cells. This is really determined after the formula (including the IFNA) has been processed. One way around that is to create an array that is the same size as the number of rows in your output selection. The Z1 formula looks for the 10 smallest values in the array returned by MODE.MULT. If there are less than 10 values, the extra will return #NUM , which IFERROR will convert to -. If you want a much longer list, you can use the version in AA1.

Hope this helps.

This worked beautifully! Thank you for your help. It is very much appreciated.

 

[larsensation]

Second Question, If I were to transpose this AND have the data set from another sheet, would this still work? I seem to get 5 dashes "-" when i enter this:

{=TRANSPOSE(IFERROR(SMALL(MODE.MULTI(IF(Sheet3!A4:X18<>0,Sheet3!A4:X18)),{1;2;3;4;5}),"-"))}

 

[Eric W]

If you select a horizontal range, say D2:H2, then this should work:

=TRANSPOSE(IFERROR(SMALL(MODE.MULT(IF(Sheet3!A4:X18<>0,Sheet3!A4:X18)),{1;2;3;4;5}),"-"))

It's exactly the same as yours, I just removed the I after MULTI.

You can also get the same effect without the transpose by making the array constant a horizontal array:

=IFERROR(SMALL(MODE.MULT(IF(Sheet3!A4:X18<>0,Sheet3!A4:X18)),{1,2,3,4,5}),"-")