Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with time function
Hi!
I have a spreadsheet filled with data that includes date and time. For a particular value in column a i need to find the max and min time. My sheet looks a little like this Column a columnb 1234 2/01/2013 1234 3/01/2013 1234 3/01/2013 1234 4/01/2013 1234 5/01/2013 1234 6/01/2013 5678 1/01/2012 5678 2/01/2012 5678 3/01/2012 5678 4/01/2012 So I need to find the max and min dates for the values 1234 and 5678 etc,. Please help! Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with time function
On Wed, 20 Nov 2013 21:49:02 -0800 (PST), Nitya Satheesh wrote:
Hi! I have a spreadsheet filled with data that includes date and time. For a particular value in column a i need to find the max and min time. My sheet looks a little like this Column a columnb 1234 2/01/2013 1234 3/01/2013 1234 3/01/2013 1234 4/01/2013 1234 5/01/2013 1234 6/01/2013 5678 1/01/2012 5678 2/01/2012 5678 3/01/2012 5678 4/01/2012 So I need to find the max and min dates for the values 1234 and 5678 etc,. Please help! Thanks! There are several ways to do this, depending on what your real data looks like. You can use a Pivot Table Insert Pivot Table Drag Col A to the Rows Area Drag Col B to the Values area twice Click the drop down arrow on the Col B items to get to the Value Settings Menu Set one to Min and the Number Format to Date Set the other to Max and the Number Format to Date Values Row Labels Max of Col B Min of Col B 1234 6/1/13 2/1/13 5678 4/1/12 1/1/12 If your values are really in sorted order as above, with the both columns sorted as you show, then you can do something like F2: 1234 F3: 5678 For Max G2: =LOOKUP(2,1/(Col_A=F2),Col_B) or you might try: =VLOOKUP(F2,Col_A:Col_B,2) For Min H2: =VLOOKUP(F2,Col_A:Col_B,2,FALSE) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with time function
"Nitya Satheesh" wrote:
My sheet looks a little like this Column a columnb 1234 2/01/2013 1234 3/01/2013 1234 3/01/2013 1234 4/01/2013 1234 5/01/2013 1234 6/01/2013 5678 1/01/2012 5678 2/01/2012 5678 3/01/2012 5678 4/01/2012 So I need to find the max and min dates for the values 1234 and 5678 etc,. Array-enter the following formulas (press ctrl+shift+Enter instead of just Enter): =MIN(IF($A$1:$A$10=1234,$B$1:$B$10)) =MAX(IF($A$1:$A$10=1234,$B$1:$B$10)) To confirm that you array-entered the formula, verify that the formula is surrounded by curly braces (e.g, {=MIN(IF(...))} in the Formula Bar when you select the cell. Note that we cannot typye the curly braces ourselves. That is just Excel's way of denoting an array-entered formula. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with time function
Thanks a Lot!!! That worked great!!!
I have one more query with respect to the same problem.What if I wanted to add one more column of data. Column a column b column c 1234 1 2/01/2013 1234 1 3/01/2013 1234 1 4/01/2013 1234 2 4/01/2013 1234 2 6/01/2013 1234 3 6/01/2013 5678 3 8/01/2012 5678 8 2/01/2012 5678 8 3/01/2012 5678 2 4/01/2012 5678 2 5/01/2012 So now what I want to do is get the max and min date for each of the values in column b, and then the total number of days for each of the values in column a. for e.g from the above data I know that '1' will have a max and min date of 4/01/2013 and 2/01/2013 so the number of days is 2, '2' will have a number of days of 2 and 3 will have a number of days 2 so 1234 will have a total days of 6. Please help. Thanks in advance. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with time function
On Fri, 22 Nov 2013 22:10:34 -0800 (PST), Nitya Satheesh wrote:
Thanks a Lot!!! That worked great!!! Who are you responding to, and, if to me, which method are you using? Please also show an example as to how you would like your results to be presented. If you are using my Pivot Table method, I would probably just set up a second Pivot table. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with time function
On Fri, 22 Nov 2013 22:10:34 -0800 (PST), Nitya Satheesh wrote:
Thanks a Lot!!! That worked great!!! I have one more query with respect to the same problem.What if I wanted to add one more column of data. In addition to my previous response, if you are using my formulas, it is simple to adapt them to your requirements. For the number of days for 1234, with F2: 1234 G2: =COUNTIF(Column_a,F2) And for the Max and Min of the values in column_b, merely adapt the formula I previously posted for the Max and Min of the values in column A: e.g. F6: 1 G6 (MIN): =VLOOKUP(F6,column_b:column_c,2,FALSE) H6 (MAX): =LOOKUP(2,1/(column_b=F6),column_c) You should be able to figure out how to use 2, 3 and 5678. If you want different results, you will need to, as I mentioned before, be specific as to how you want the results presented. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with time function
On Sunday, November 24, 2013 6:25:50 PM UTC+5:30, Ron Rosenfeld wrote:
On Fri, 22 Nov 2013 22:10:34 -0800 (PST), Nitya Satheesh wrote: Thanks a Lot!!! That worked great!!! I have one more query with respect to the same problem.What if I wanted to add one more column of data. In addition to my previous response, if you are using my formulas, it is simple to adapt them to your requirements. For the number of days for 1234, with F2: 1234 G2: =COUNTIF(Column_a,F2) And for the Max and Min of the values in column_b, merely adapt the formula I previously posted for the Max and Min of the values in column A: e.g. F6: 1 G6 (MIN): =VLOOKUP(F6,column_b:column_c,2,FALSE) H6 (MAX): =LOOKUP(2,1/(column_b=F6),column_c) You should be able to figure out how to use 2, 3 and 5678. If you want different results, you will need to, as I mentioned before, be specific as to how you want the results presented. Thank You! Sorry I was not more specific. I used The Pivot table method and just set up another Pivot table. Thanks again. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with time function
On Thursday, November 21, 2013 8:56:31 PM UTC+5:30, joeu2004 wrote:
"Nitya Satheesh" wrote: My sheet looks a little like this Column a columnb 1234 2/01/2013 1234 3/01/2013 1234 3/01/2013 1234 4/01/2013 1234 5/01/2013 1234 6/01/2013 5678 1/01/2012 5678 2/01/2012 5678 3/01/2012 5678 4/01/2012 So I need to find the max and min dates for the values 1234 and 5678 etc,. Array-enter the following formulas (press ctrl+shift+Enter instead of just Enter): =MIN(IF($A$1:$A$10=1234,$B$1:$B$10)) =MAX(IF($A$1:$A$10=1234,$B$1:$B$10)) To confirm that you array-entered the formula, verify that the formula is surrounded by curly braces (e.g, {=MIN(IF(...))} in the Formula Bar when you select the cell. Note that we cannot typye the curly braces ourselves. That is just Excel's way of denoting an array-entered formula. Thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there an XLS function to convert std time to Military time? | Excel Worksheet Functions | |||
Macro / function text time to 24hr excel time passed midnight fortotaling hr's | Excel Worksheet Functions | |||
verify use of TIME Function, Find Quantity Level compare to time-d | Excel Discussion (Misc queries) | |||
Function Call Specified at Run Time rather than Compile Time? | Excel Programming | |||
Function to convert Time String to Time | Excel Worksheet Functions |