Array Dates min and max (97/2000/XP)

[jturner]

I have a list of dates that I want to find the earliest and latest dates in the range based on 2 criteria in adjacent columns. Ultimately, I want to find how many days are between the earliest and latest dates posted.

I have attached a sample sheet that shows the 3 columns in question, eventcode, eventType, and date along with some sample dates. I see a lot of examples to count and sum based on multiple criteria, but nothing on min and max.

Am I even approachng this correctly? And can anyone give me a formula for finding the first date in column c that match criteria in columns a and b.

In otherwords, if eventcode = 1 and eventType = "A" what is the earliest and latest dates so I can find the differnce between the two.

[SammyB]

The formula that you say is incorrect
<pre>{=MIN(IF($A$2:$A$19=$E3 AND $B$2:$B$19=$F3,1,0))}</pre>

needs to be the array formula
[pre]{=MIN(IF(($A$2:$A$19=$E3) * ($B$2:$B$19=$F3),$C$2:$C$19,99999999999))}
HTH --Sam

[sdckapr]

try the following ARRAY (confirm with ctr-shift-enter)
=MIN(IF(($A$2:$A$19=$E3)*($B$2:$B$19=$F3),$C$2:$C$ 19))
and
=Max(IF(($A$2:$A$19=$E3)*($B$2:$B$19=$F3),$C$2:$C$ 19))

to keep consistent you can also use:
=count(IF(($A$2:$A$19=$E3)*($B$2:$B$19=$F3),$C$2:$ C$19))

For your count


Steve

[SammyB]

Leaving the Else clause empty was much better than my 99999999999999.... Thanks! --Sam (still thinking Fortran!) <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

[jturner]

It works perfectly. Thank you very much.

Some day I am going to get array's. I just don't think that way.

[SammyB]

> I just don't think that way
No one except a select few at Microsoft do!

[sdckapr]

Another tip (in case you are as lazy as I am)

Instead of explicitly putting the range in the cells (and requiring you to change it in multiple formulas):
Define a range name: eg DataTable =Sheet1!A1:C19 (or whatever)

Then use:
=MIN(IF((INDEX(DataTable,0,1)=$E3)*(INDEX(DataTabl e,0,2)=$F3),INDEX(DataTable,0,3)))
and
=Max(IF((INDEX(DataTable,0,1)=$E3)*(INDEX(DataTabl e,0,2)=$F3),INDEX(DataTable,0,3)))

and to keep consistent you can also use:
=count(IF((INDEX(DataTable,0,1)=$E3)*(INDEX(DataTa ble,0,2)=$F3),INDEX(DataTable,0,3)))

FYI:
Index(DataTable,0,x) is the xth column of Datatable in matrix/array form
(also index(DataTable,x,0) is the xth row of datatable in matrix/array form
and index(datatable,0,0) is the entire table in matrix/array form)

Now if you are REALLY lazy, you let excel determine the range of data:
Define a name DateCount =COUNT(INDIRECT("Sheet1!C:C"))
Then Define DataTable as =OFFSET(INDIRECT("Sheet1!A2"),0,0,DateCount,3)

Now when you add items to your list, DataTable will grow based on the number of dates in col C!
Steve

[jturner]

My real spreadsheet is getting to big and I through out most of my range names, it was getting too slow. I also through out the custom functions that worked, but again, it was starting to crawl.
I am using the following array formula, although I left it out of my initial request as I was trying to keep it as simple as possible.
=MIN(IF(OFFSET($B$2,0,0,COUNT($B:$[img]/forums/images/smilies/cool.gif[/img]-1,1)=$B2,OFFSET($S$2,0,0,COUNT($S:$S)-1)))

I guess I am wondering if there is any advantage or disadvantage to your method or mine. I am using a similar equation in around 25 columns so calculation speed has become an important factor.

[sdckapr]

I haven't done any testing, but I would THINK that:

Putting the range name in the formula would be BETTER than using the offset, especially if you are using it multiple times (as you were doing in your worksheet)

If you use OFFSET multiple times, each time excel must calculate it. And it must also calculate counting in the column multiple times. If you create a range name, it only has to be determined once and then it uses the name. This should be faster (how much faster - I have no clue, but it is a drain on the resources!)

I also think it makes for easier coding.

You could also name each column a different name and use the array index as the name to define them instead of using the index array in the formula. Again, I am not sure which is most efficient, I would recommend doing whatever you are most comfortable with. Remember you will have to keep it updated so you need to be comfortable with the coding!

Steve