#Value, MODE, LEFT and time of day help, please.

aiki100

Board Regular
Joined
Aug 16, 2016
Messages
102
Hello all -

I have been given some great help recently in trying to return the MODE of a series of TIMES (as in times of day). This has been very helpful, and I thank you all. Now I have hit a snag in implementing it.

In col C2 I have the following formula:

=IF(Main!DG3="X",Main!G3,"")

This returns the following data and format (if the "IF," above is satisfied).

13:00:00 EST or 13:00:00 EDT

Here is the helpful formula I was given that I have put in E2

=MODE(LEFT(C6:C9,8)*1)

The formula above excises the "EST," turns it into a number, and does indeed return the MODE. Excellent!

Now, my snag is that I have hundreds of rows of this data, and some rows will return nothing, since =IF(Main!DG3="X",Main!G3,"") is not always true.

It seems like the MODE formula is not unhappy with the blank rows per se, but unhappy with the fact that those blank rows are not truly blank, but have a formula in it. I could very very easily be wrong, but in any event, I get a #VALUE error.

What I need please, is a way to strip off the "EST," above (already done, I believe), turn that into a number that MODE can work with (already done, I believe), and now I need to have MODE ignore the blank rows to return the MODE of times of day where data actually exists.


Thank you for any thoughts or pointers!
 

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)
Try a conditional array formula like:


Excel 2010
A
11
23
34
4 
55
69
7 
8 
94
106
112
122
134
144
Sheet7
Cell Formulas
RangeFormula
A4=""
A7=""
A8=""
A13=MODE(A1:A12)
A14{=MODE(IF(A1:A12<>"",A1:A12))}
Press CTRL+SHIFT+ENTER to enter array formulas.


even though the "" seems inert here (maybe not with other functions)
 
Last edited:
Upvote 0
Hi Sheetspread -

Thank you so much for taking a look at this. Your approach seems reasonable, yet I still get a #VALUE error. It's funny, because this formula:

[FONT=&quot]=MODE(LEFT(C7:C9,8)*1)

works just fine. But make it:

[/FONT]

[FONT=&quot]=MODE(LEFT(C6:C9,8)*1)

and I get the #VALUE error. And that's because C6, though "empty," has a formula in it that is returning "". (Or maybe it IS simply because it is "" and MODE can't deal with that?)

Anyway, as long as there are no "blank," cells in the Cx:Cx range, this formula:

=MODE(LEFT(C7:C9,8)*1)

works great.


Any thoughts, please?



[/FONT]
 
Upvote 0
This worked when I tried:


Excel 2010
ABC
113:00:00 EST or 13:00:00 EDT0.541667
203:00:00 EST or 03:00:00 EDT0.125
311:00:00 EST or 11:00:00 EDT0.458333
409:00:00 EST or 09:00:00 EDT0.375
5 #VALUE !
6 #VALUE !
711:00:00 EST or 11:00:00 EDT0.458333
8
90.458333333
100.458333333
Sheet8
Cell Formulas
RangeFormula
C1=0+LEFT(A1,8)
A5=""
A6=""
A10=MODE(C1,C2,C3,C4,C7)
A9{=MODE(IF(A1:A7<>"",0+LEFT(A1:A7,8)))}
Press CTRL+SHIFT+ENTER to enter array formulas.


Note that left() on a blank returns a #VALUE ! error when you +0
 
Last edited:
Upvote 0
Yes, indeed, that works! Thank you very, very much for the help!

Question please: what is the 0+ all about? Only if you have a moment to tell me - I try to learn a bit more every time I have my mind expanded here.

Thank you again - hope you have a great week!
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

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
Back
Top