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]

"rmeister" <[email protected]> wrote in
message news:[email protected]...
>
> 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))


--

Regards,

Peo Sjoblom





"rmeister" <[email protected]> wrote in
message news:[email protected]...
>
> 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
> ------------------------------------------------------------------------
> rmeister's Profile:
http://www.excelforum.com/member.php...o&userid=30163
> View this thread: http://www.excelforum.com/showthread...hreadid=498681
>

[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




--

Regards,

Peo Sjoblom

"rmeister" <[email protected]> wrote in
message news:[email protected]...
>
> 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?
>
>
> --
> rmeister
> ------------------------------------------------------------------------
> rmeister's Profile:
http://www.excelforum.com/member.php...o&userid=30163
> View this thread: http://www.excelforum.com/showthread...hreadid=498681
>