Most frequent value between two dates
In this article I will show you how to extract the most frequent value (text or number) between two dates in a column.
Array formula in D18:
To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
Explaining formula in cell D18
Step 1 - Calculate rank order if sorted
The COUNTIF function counts values based on a condition or criteria, the < less than sign makes the COUNTIF calculate a rank number if the list were sorted from A to Z.
COUNTIF($C$3:$C$12,"<"&$C$3:$C$12)
becomes
COUNTIF({"BB"; "CC"; "AA"; "CC"; "AA"; "EE"; "DD"; "EE"; "BB"; "AA"},"<"&{"BB"; "CC"; "AA"; "CC"; "AA"; "EE"; "DD"; "EE"; "BB"; "AA"})
becomes
COUNTIF({"BB"; "CC"; "AA"; "CC"; "AA"; "EE"; "DD"; "EE"; "BB"; "AA"}, {"<BB";"<CC";"<AA";"<CC";"<AA";"<EE";"<DD";"<EE";"<BB";"<AA"})
and returns
{3;5;0;5;0;8;7;8;3;0}.
Step 2 - Check which values are in range
The IF function returns the rank number number based on a logical expression. It returns boolean value TRUE if the value is in the date range. If boolean value is FALSE the IF function returns "" (nothing).
IF(($B$3:$B$12<=$C$16)*($B$3:$B$12>=$C$15),COUNTIF($C$3:$C$12,"<"&$C$3:$C$12),"")
becomes
IF({1;0;0;0;1;1;0;1;0;0},COUNTIF($C$3:$C$12,"<"&$C$3:$C$12),"")
becomes
IF({1;0;0;0;1;1;0;1;0;0}, {3; 5; 0; 5; 0; 8; 7; 8; 3; 0},"")
and returns
{3; ""; ""; ""; 0; 8; ""; 8; ""; ""}.
Step 3 - Calculate the most frequent number
The MODE.SNGL function returns the most frequent number in a cell range or array.
MODE.SNGL(IF(($B$3:$B$12<=$C$16)*($B$3:$B$12>=$C$15),COUNTIF($C$3:$C$12,"<"&$C$3:$C$12),""))
becomes
MODE.SNGL({3; ""; ""; ""; 0; 8; ""; 8; ""; ""})
and returns 8.
Step 4 - Find position of most frequent number in array
The MATCH function finds the relative position of a value in an array or cell range.
MATCH(MODE.SNGL(IF(($B$3:$B$12<=$C$16)*($B$3:$B$12>=$C$15), COUNTIF($C$3:$C$12, "<"&$C$3:$C$12), "")), COUNTIF($C$3:$C$12, "<"&$C$3:$C$12),0)
becomes
MATCH(8, COUNTIF($C$3:$C$12, "<"&$C$3:$C$12),0)
becomes
MATCH(8, {3; 5; 0; 5; 0; 8; 7; 8; 3; 0},0)
and returns 6.
Step 5 - Return value
The INDEX function returns a value based on row number (and column number if needed)
INDEX($C$3:$C$12, MATCH(MODE.SNGL(IF(($B$3:$B$12<=$C$16)*($B$3:$B$12>=$C$15), COUNTIF($C$3:$C$12, "<"&$C$3:$C$12), "")), COUNTIF($C$3:$C$12, "<"&$C$3:$C$12),0))
becomes
INDEX($C$3:$C$12, 6)
and returns "EE" in cell D18.
Get Excel *.xlsx file
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
3 Responses to “Most frequent value between two dates”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
I have just replaced data and add some more records. This formula doesn't work. Is this because of my 2007 version?
David,
I don´t think so, did you enter the formula as an array formula?
How would I do if I also want to show a number of how many times the most common value has been used? Like "EE - 2 times"