Using MODE with Time and Days of the Week?

aiki100

Board Regular
Joined
Aug 16, 2016
Messages
83
Hello -

#1) I am trying to derive the MODE of a sequence of times of day. In other words, I'd like to see what times of day are the most frequently occurring times, from a list of observed times. I have been given the following format to convert:

12:00:00 EDT

I imagine it does need to be converted Into text or some other kind of number format so as to derive the MODE of this sequence? I have tried several approaches to no avail.

For instance, If I had the following:

12:00:00 EDT
12:00:00 EDT
22:00:00 EDT
22:00:00 EDT
22:00:00 EDT
22:00:00 EDT

The MODE would be: 22:00:00 EDT - the most frequently occurring value.

My data does have blank lines in rows, so perhaps that is an issue as well? I have tried approaches where they are all together, and MODE didn't work there either, so I think that's not the "main," MODE issue, though I will need a way to deal with blank cells, too.

#2) I also wish to derive the MODE of the day of the week, as well. I have converted the actual date into day of week with a custom format of, "ddd," and would like to derive the MODE of this (separate) sequence too. Like so:

4/26/18 Thu
4/27/18 Fri
5/4/18 Fri

The MODE for this sequence of days of the week (would be) Fri, as it is the most frequently occurring day in this very small sample.

Is there anyone with any ideas on how to proceed on either of these please?

Thanks much to you all —
 
Last edited:

Oscar Cronquist

Board Regular
Joined
Apr 20, 2018
Messages
200
1.

The MODE function works only with numbers.
https://support.office.com/en-us/article/mode-function-e45192ce-9122-4980-82ed-4bdc34973120

I am guessing your time values are text values in Excel?
If that is the case you need to convert the values into numbers. Perhaps this array formula will work:
=MODE(LEFT(A1:A6,8)*1)
Cell range A1:A6 contains the time values.

2.

Try the following array formula:

=MODE(WEEKDAY(E1:E7,1))
Cell range E1:E6 contains the date values.
 
Last edited:

aiki100

Board Regular
Joined
Aug 16, 2016
Messages
83
Hi Oscar -

Thank you very much for replying!

So: your MODE formula for time above does indeed work, thank you! However, it only works (and this makes complete sense once I read your documentation) if I manually strip off the "EDT," so as to make the cell a pure number only. So question one: Do you know of another formula that would strip off the EDT so I can make use of your (MODE/Time) formula? I can certainly do it in two steps since I have the data I'm supposed to use in a separate worksheet anyway, and can make the edit there.

Separate issue, I will have blanks in rows between these times, and so get a #VALUE error. Any way to ignore the blanks and still return the MODE?


Number two, the weekday MODE formula did not work, and I think that is because, although I have formatted the cell to return day of week, it is still a date format that I have formatted to SHOW day of week?

Also, if you'd be kind enough to tell me what the 8 is for and the *1 in this formula:

=MODE(LEFT(A1:A6,8)*1)

and the ,1 in this one

=MODE(WEEKDAY(E1:E7,1))

I will try to follow along with your logic so as to learn this
whole thing better. Of if you have a link (so you don't have to work too much!) that would be great as well.


I VERY much appreciate your help, and thank you again for taking a look at this.


 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,984
While it is true that MODE only works with numbers, you can covert a list of text values to numbers by using MATCH. For example:

ABCDEF
14/26/2018 ThuThu
212:00:00 EDT22:00:00 EDT4/27/2018 Fri
312:00:00 EDT5/4/2018 Fri
422:00:00 EDT5/3/2018 Thu
54/30/2018 Mon
622:00:00 EDT
722:00:00 EDT
8

<tbody>
</tbody>
Sheet10

Array Formulas
CellFormula
F1{=INDEX({"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},MODE(IF(E1:E7<>"",WEEKDAY(E1:E7))))}
C2{=INDEX(A2:A7,MODE(IF(A2:A7<>"",MATCH(A2:A7,A2:A7,0))))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



And given that your dates in E are actual dates just formatted to show the DOW, you can use the F1 formula to get the most common DOW. Both formulas are array formulas, and both of them will ignore empty rows in your ranges.
 

Oscar Cronquist

Board Regular
Joined
Apr 20, 2018
Messages
200
if I manually strip off the "EDT," so as to make the cell a pure number only. So question one: Do you know of another formula that would strip off the EDT so I can make use of your (MODE/Time) formula?

Also, if you'd be kind enough to tell me what the 8 is for and the *1 in this formula:
LEFT(A1:A6,8)*1

The LEFT function returns 8 characters counting from the beginning of the text string. LEFT("12:00:00 EDT",8) returns 12:00:00.

*1 converts the number stored as text into a number.

and the ,1 in this one


=MODE(WEEKDAY(E1:E7,1))
WEEKDAY(E1:E7,1) converts the dates to numbers. 1 is the return_type. 1 converts Sundays to 1, Saturdays is 6.
https://support.office.com/en-us/article/weekday-function-60e44483-2ed1-439f-8bd0-e404c190949a
 
Last edited:

aiki100

Board Regular
Joined
Aug 16, 2016
Messages
83
Very helpful! Thank you for taking the time to explain that. And of course, by explaining it, you clued me in on how to edit the "EDT," out of the original data so as to be used with your formula.

Thanks again!
 

aiki100

Board Regular
Joined
Aug 16, 2016
Messages
83
Hi Eric -

Thanks so much for your thoughts on this. It is greatly appreciated!

Question:

Say I have the following data, that are just dates (nothing identifying DOW in this cell, just date only) in col H:

1/5/10
1/6/10
1/11/10
1/14/10

I think your formula (in say J2) should therefore be:

=INDEX({"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},MODE(IF(H2:H70<>"",WEEKDAY(H2:H70))))

(entered with ctrl - shift- enter---- for array)

Yet when I use this, I get #N/A, except for some cells that have no data in them, but DO have a formula. These "blank," cells are all returning Tue, but all else, WITH a date are returning N/A.

Any thoughts, please? Thank you again for the help.




 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,984
I'm a little confused.

First, if you use the formula you gave, on the 4 dates that you gave, you will get # N/A. This is a characteristic of the MODE function. You have a Tuesday, Wednesday, Monday, and Thursday. There is no "most common" date, there is 1 each of them. You need at least 2 of one of them to get a MODE, otherwise you get the # N/A. We can put IFERROR around the formula if you want a bit nicer message.

Second, are you using the formula more than once? I can't quite picture your scenario. The formula is designed to look at a range, not an individual cell. Individual cell values (which you can see with the Evaluate Formula tool) can be constants or the result of a formula. Non-numeric values will cause a # VALUE error, and non-date values will throw off the result.
 

aiki100

Board Regular
Joined
Aug 16, 2016
Messages
83
Eric -

No way YOU are confused...I AM confused, lol.

You are of course correct. I had made a very small random sample to test, and didn't haven't enough real data to trigger an actual, accurate return for the MODE calculation. I actually have many thousands of data points, and once I dropped some of that in, it worked just fine.

Thank you again for you help, I very much appreciate it - Have a great weekend!
 

Forum statistics

Threads
1,082,155
Messages
5,363,472
Members
400,741
Latest member
MachoDrove

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top