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

[aiki100]

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!

 

[sheetspread]

Try a conditional array formula like:

Excel 2010
	A
1	1
2	3
3	4
4
5	5
6	9
7
8
9	4
10	6
11	2
12	2
13	4
14	4
Sheet7

Cell Formulas
Range	Formula
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)

 

[aiki100]

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]

 

[sheetspread]

This worked when I tried:

Excel 2010
	A	B	C
1	13:00:00 EST or 13:00:00 EDT		0.541667
2	03:00:00 EST or 03:00:00 EDT		0.125
3	11:00:00 EST or 11:00:00 EDT		0.458333
4	09:00:00 EST or 09:00:00 EDT		0.375
5			#VALUE !
6			#VALUE !
7	11:00:00 EST or 11:00:00 EDT		0.458333
8
9	0.458333333
10	0.458333333
Sheet8

Cell Formulas
Range	Formula
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

 

[sheetspread]

(whether it's a true blank or "")

 

[aiki100]

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!

 

[sheetspread]

+0, *1, or the value() function will convert text numbers to values.

 

[aiki100]

Thank you very much for that, and again thanks for your help!

Have a good one -