Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Max value formula doesn't work
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Max value formula doesn't work
Your values in column A are text strings, not real time values. That is why
MAX is returning 0 -- Gary''s Student - gsnu200751 "orquidea" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Max value formula doesn't work
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. -- David Biddulph "orquidea" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Max value formula doesn't work
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 "David Biddulph" wrote: 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. -- David Biddulph "orquidea" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Max value formula doesn't work
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 -- Gary''s Student - gsnu200751 "orquidea" wrote: 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 "David Biddulph" wrote: 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. -- David Biddulph "orquidea" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Max value formula doesn't work
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). -- HTH, Barb Reinhardt "orquidea" wrote: 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 "David Biddulph" wrote: 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. -- David Biddulph "orquidea" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Max value formula doesn't work
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,.......) -- Gary''s Student - gsnu200751 "Barb Reinhardt" wrote: 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). -- HTH, Barb Reinhardt "orquidea" wrote: 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 "David Biddulph" wrote: 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. -- David Biddulph "orquidea" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Max value formula doesn't work
I think you've posted this to the wrong thread.
Pete On Oct 25, 2:50 pm, Gary''s Student wrote: 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,.......) -- Gary''s Student - gsnu200751 "Barb Reinhardt" wrote: 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). -- HTH, Barb Reinhardt "orquidea" wrote: 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 "David Biddulph" wrote: 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. -- David Biddulph "orquidea" wrote in message ... 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- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Max value formula doesn't work
I think you responded to the wrong thread. It happens to us all. :)
Barb "Gary''s Student" wrote: 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,.......) -- Gary''s Student - gsnu200751 "Barb Reinhardt" wrote: 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). -- HTH, Barb Reinhardt "orquidea" wrote: 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 "David Biddulph" wrote: 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. -- David Biddulph "orquidea" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Max value formula doesn't work
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 On Oct 25, 2:08 pm, orquidea wrote: 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 "David Biddulph" wrote: 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. -- David Biddulph "orquidea" wrote in message ... 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- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula will not work | Excel Discussion (Misc queries) | |||
Why does this formula not work? | Excel Worksheet Functions | |||
Cant quite get this formula to work: | Excel Worksheet Functions | |||
A search for $ in a formula use to work now it does not work | Excel Discussion (Misc queries) | |||
How does this formula work | Excel Worksheet Functions |