Mode

[Cup V Joe XL]

So I am using MODE.MULT which is working great! So this is what it does if there is no data in the data array: (If the data set contains no duplicate data points, MODE.MULT returns the #N/A error value.) that is what it’s supposed to do, but what I am looking for is to do that when there are more than one duplicate data point. Ex if the data array shows 1, 1, 2 that would then = 1 and if the data array shows 1,1,2,2 that would then = 1, but I want it to = N/A or it can be blank , so maybe using a logical formula would help but I can’t figure it out... Please Help.

[NBVC]

I am not sure of the MODE.MULT function but if you mean the MODE function or if it similar to the MODE function, you can use a helper formula first...

assuming data is in A1:A6, then in B1 enter:

=IF(COUNTIF($A$1:$A1,$A1)=1,COUNTIF($A$1:$A$6,$A1),"")

copied down.

then use:

=IF(COUNTIF($B$1:$B$6,MAX($B$1:$B$6))>1,"",MODE($A$1:$A$6))

for the final result

[Cup V Joe XL]

It doesn’t seem to work, I even attempted in a new workbook, but the formula still calculates the first duplicate point in the data array first. I do think we are close to figuring it out. The data array shows 1,1,2,2 that would then = 1, but I want it to = N/A or it can be blank If there are more than one duplicate data point in the data array.

[ChemistB]

Mode.Mult works as an array. You'll need to have multiple cells (vertical cells) selected and then enter with CNTRL SHFT ENTER.
So if your data in A1:A10 and your mode is tied with 3 each of 1 and 2...
If you select C1:C4 and =MODE.MULT($A$1:$A$10) and enter with CNTRL SHFT ENTER, you'll see 1,2,#NA, #NA

BUT, that's not what you want (I think)......
So if you don't want an value if there is more than one # tied for mode, try

=IF(COUNT(MODE.MULT($A$1:$A$10))>1,"",MODE($A$1:$A$10))
Does that work for you?

[Cup V Joe XL]

THAT WORKED!!!! I have been breaking my head for a while trying to figure it out. Thanks you both!!!