Calculating Mode in Excel

smalik

Board Regular
Joined
Oct 26, 2006
Messages
120
Office Version
  1. 365
Platform
  1. Windows
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
1602777609795.png


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.
1602777679013.png
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 26, 2006
Messages
120
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
That does not explain your desired output, which is exactly the same as colB.
 

smalik

Board Regular
Joined
Oct 26, 2006
Messages
120
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Aug 18, 2015
Messages
10,770
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
Joined
Oct 26, 2006
Messages
120
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Eric,

Thanks for your help.

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
Joined
Aug 18, 2015
Messages
10,770
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
Joined
Oct 26, 2006
Messages
120
Office Version
  1. 365
Platform
  1. Windows
Thank you for explaining. Much appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,863
Messages
5,627,332
Members
416,241
Latest member
moggy84

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top