Modify Aggregate Function

[Tikey]

Hi

I am using the following formula to find the earliest date in a column of cells for a particular year, in this example, 1994.

=AGGREGATE(15,6,Records!$F:$F/(YEAR(Records!$F:$F)=1994),1)

I now wish to modify this such that it will only consider cells where a corresponding cell has a value of >0.

A B
1/1/1994 0
2/1/1994 2
3/1/1994 1
6/1/1993 5

So in the above example, if I were to be looking for the earliest date in 1994, the answer would be 2/1/1994 as cell B1 only has a value of 0

Can anyone offer a solution

Thanks

 

[Peter_SSs]

Try something like this

=AGGREGATE(15,6,Records!$F$2:$F$100/((YEAR(Records!$F$2:$F$100)=1994)*(Records!$G$2:$G$100>0)),1)

Adjust the $100 as needed but I strongly recommend not using full column references with formulas like this. With full column references this formula takes about 500 times as long to calculate as this one with restricted rows.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=MIN(IF(ISNUMBER(1/($F$2:$F$100*$G$2:$G$100)),IF(YEAR($F$2:$F$100)=1994,IF($G$2:$G$100>0,$F$2:$F$100))))

Just enter:

=MINIFS(F2:F100,F2:F100,">="&DATE(1994,1,1),F2:F100,"<="&DATE(1994,12,31),G2:G100,">0")

Just enter:

=AGGREGATE(15,6,Records!$F:$F/((YEAR(Records!$F:$F)=1994)*(Records!$G:$G>0)),1)

Note that the latter does not test whether F and G are numbers.

 

[Tikey]

Thanks very much to both of you, your solutions worked great.

 

[Peter_SSs]

Thanks very much to both of you, your solutions worked great.

You're welcome.