Help with time function

  • Thread starter Thread starter Nitya Satheesh
  • Start date Start date
N

Nitya Satheesh

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!
 
Nitya Satheesh said:
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,.
Click to expand...

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.
 
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.
 
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.
Click to expand...

Thank You! Sorry I was not more specific. I used The Pivot table method and just set up another Pivot table.

Thanks again.
 
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.
Click to expand...

Thank you!
 
Back
Top