Using MODE with Time and Days of the Week?

[aiki100]

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 —

 

[Oscar Cronquist]

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.

 

[aiki100]

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]

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:

	A	B	C	D	E	F
1					4/26/2018 Thu	Thu
2	12:00:00 EDT		22:00:00 EDT		4/27/2018 Fri
3	12:00:00 EDT				5/4/2018 Fri
4	22:00:00 EDT				5/3/2018 Thu
5					4/30/2018 Mon
6	22:00:00 EDT
7	22:00:00 EDT
8

<tbody>
</tbody>

Sheet10​

Array Formulas Cell Formula 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]

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

 

[aiki100]

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]

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.

 

[Eric W]

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]

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!

 

[Eric W]

You're welcome! Enjoy your weekend!