# Calculating Mode in Excel

#### smalik

##### Board Regular
Can someone help me solve this problem please?

I am trying to calculate Mode based on two criteria. If there is no mode, i.e., there is only single occurrence of the criteria my results are correct. However, when there are two mode values, the formula results in the first mode. Any idea what am I doing wrong?

This picture shows the correct results

This is where I have a problem. Excel is picking the first mode value
Column C is the calculated value which is incorrect for my exercise. Column D is what I like to get.
What modifications do I need to my formula to get the desired results?

Any help is greatly appreciated.

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### Fluff

##### MrExcel MVP, Moderator
I'm afraid I don't understand what you are trying to do, as it makes non sense to me.
That said, to get your desired result you can just use
=B12

#### smalik

##### Board Regular
My apology for the confusion

I am trying to calculate a "mode if" value based on criteria in Column "A"

There are over 500,000 transactions from a client database
Column A = Client
Column B = Month their policy is due

A client can have multiple transactions, i.e., multiple policies. For example, Auto, Home, Wind, Flood, Life, etc.

Since not all policies are due on the same month, I am trying to calculate which month has the most policies dues. Once I calculate the mode, I pick the earliest month (based up the mode value) and send them a letter. I am doing this in two steps.
1. Calculate the Mode (month) - using the "Modeif" array formula
2. Pick the earliest month (based on model value) - using the "Minifs" formula from the "modeif" column
1. if three polices are renewed in Mar and only one in Apr, "modeif" formula works perfectly
2. if two polices are renewed in Jun and two in Aug, The "modeif" formula is where I am having trouble.
For example, when two polices are due in Jun and two in Aug. However, if Aug polices are listed before Jun policies, Excel is picking Aug as the mode. (Unfortunately, sorting the data is not an option)

Hope this helps.

#### Fluff

##### MrExcel MVP, Moderator
That does not explain your desired output, which is exactly the same as colB.

#### smalik

##### Board Regular

Okay, let's try this.

If there are multiple calculated model values then I need the month instead of the mode value.

Does that help?

#### Eric W

##### MrExcel MVP
Ignoring your sample results, which as Fluff noted doesn't seem to match your explanation, I tried to create something based on the explanation alone. Consider:

Book1
ABC
1Criteria-1Criteria-2Mode Value
2Test111
3Test121
4Test131
5Test141
6Test151
7Test21510
8Test21010
90
10test312
11test332
12test322
13test322
14test332
15test41010
16test41510
170
18test516
19test526
20test586
21test566
22test586
23test566
24test576
25test5116
26test644
27test644
28test634
29test722
30test742
Sheet23
Cell Formulas
RangeFormula
C2:C30C2=MIN(MODE.MULT(IF(\$A\$2:\$A\$30=A2,\$B\$2:\$B\$30*{1,1})))
Press CTRL+SHIFT+ENTER to enter array formulas.

#### smalik

##### Board Regular

Eric,

This is perfect... I don't have to do a two step process. Can you please help me understand the significance of {1,1} in the formula?

#### Eric W

##### MrExcel MVP
MODE and MODE.MULT will find the mode of a set, but if there is no mode, i.e. all the values occur just 1 time each, MODE will generate an error. So when you multiply a value by an array {1,1} you get the value 2 times, so MODE will find something. Since each value is duplicated, the actual MODE is not changed.

#### smalik

##### Board Regular
Thank you for explaining. Much appreciated.

Replies
2
Views
110
Replies
3
Views
52
Replies
5
Views
42
Replies
7
Views
117
Replies
1
Views
247

### Forum statistics

1,127,633
Messages
5,626,004
Members
416,151
Latest member
Openminded intellectual

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back