Max value formula doesn't work

[Guest]

Hi

I have a colum of 514 rows with hours information (15:00, 0:30, etc). I
want to get the maximun number of hours within this range. I have used the
formula =max(a1:a514) but the outcome is 0:00. I have done this calculation
before with hours and it has worked. The ranges have been smaller, though.

Could anyone help me to figure out how to do it or why it is not working?

Thanks,
Orquidea

 

[Guest]

Your values in column A are text strings, not real time values. That is why
MAX is returning 0

 

[David Biddulph]

You've presumably got text, rather than real times, in those cells?

Try putting 0:00 in a spare cell, select & copy, then select your column of
"times" and Edit/ Paste Special/ Add.

 

[Guest]

Hi David:

I did what you suggested but it didn't work. The weird thing is that if I
run the formula from A1 to A50 It works but after that it doesn't. I
confirmed that the format is hh:mm in all of the data in that columm. I have
copied to a new sheet clear of any format and it doesn't work either.

Any idea what the problem is?

Thanks in advance

Orquidea

 

[Guest]

Select the cells from A1 thru A514 and format them to General. Values that
appeared as 15:00 shold now display as .625

It will be easier to see why MAX is failing

 

[Guest]

In cells adjacent to your "dates" enter this

=ISNUMBER(A1) where A1 is the address of a cell with a time.

I'm guessing you get some values with FALSE.

Here are some ideas on how to do a global convert.

http://www.contextures.com/xlDataEntry03.html

I've done it with Text to Columns, and I think there is an option to change
the column from GENERAL to a date. You may want to do that and then reformat
to HH:MM (or whatever you need).

 

[Guest]

How about this:

Put an additional column in the table we are searching, column E. In E1
enter:
=A1 & C1 and copy down

In reality, make this new column the first column because VLOOKUP will be
scanning it.

In the other worksheet, put the rack desired in A1, the panel desired in B1
and the VLOOKUP formula in C1:

=VLOOKUP(A1 & B1,.......)

 

[Pete_UK]

I think you've posted this to the wrong thread.

Pete

 

[Guest]

I think you responded to the wrong thread. It happens to us all.

Barb

 

[Pete_UK]

If they are text values in your column, another way is to change your
formula to this array* formula:

=MAX(VALUE(a1:a514))

*As this is an array formula, then once you have typed it in (or
subsequently amend it) you must use CTRL-SHIFT-ENTER (CSE) to commit
it, rather than the normal ENTER. If you do this correctly, then Excel
will wrap the formula in curly braces { } when viewed in the formula
bar - you must not type these yourself.

Though this will give you the correct answer if the values really are
text, it would be better to convert them to numbers as others have
advised.

Hope this helps.

Pete