Need Help With Array Formula

[rmeister]

I have a formula that looks like this:
{=MIN(IF(SHEET1!A2:A400=SHEET2!A4,SHEET1!B2:B400))}

What I would like to do is instead of saying A2:A400 and B2:B400 I
would like to do A:A and B:B. When I do this my results turn to zero
because the cells are blank beyond 400.

Is there a way I can write the formula to ignore the blank cells.

Any Help Would be appreciated!!!

 

[Stephen]

I have a formula that looks like this:
{=MIN(IF(SHEET1!A2:A400=SHEET2!A4,SHEET1!B2:B400))}

What I would like to do is instead of saying A2:A400 and B2:B400 I
would like to do A:A and B:B. When I do this my results turn to zero
because the cells are blank beyond 400.

Is there a way I can write the formula to ignore the blank cells.

Any Help Would be appreciated!!!

rmeister

I don't think it's anything to do with blank cells; try a blank cell in the
fixed range of the formula above - it works perfectly well. The problem is
(if I remember correctly) that you can't use whole column (or whole row)
references in an array formula. You could use A2:A65535.

 

[Peo Sjoblom]

1. Since this is an array formula you cannot use A:A so you need to specify
something (A2:A65535)

2. While it is true that blank cells are seen as zero, in your case that
would only happen if the criteria in A4 is blank or if you have a criteria
in A4 and that criteria is found in A2:A400 while any adjacent cell in
B2:B400 is blank, if that's the case

=MIN(IF((Sheet1!A2:A400=Sheet2!A4)*(Sheet1!B2:B400<>""),Sheet1!B2:B400))

 

[rmeister]

Thank all of you for your help.

Last Question,
If the criteria does not match the #DIV! appears as the result. How
can I write the formula to return a $0 if no criteria is found?

 

[Peo Sjoblom]

#DIV appears if you use average or for instance divide a value with a blank
value/0 not by using the MIN function, if you are using average
then you can use the same technique (range<>"") or if there are no values
at all you can use something like

=IF(COUNTBLANK(A1:A10)=10,"",your formula