Array formulas and blank/null cells...

[nanomess]

Hi,
Say if you have two equally sized arrays (A1:A20 and B1:B20), and you want to find the MAX of A-B using an array formula.... the formula would look as such:

{=MAX(A1:A20-B1:B20)}
CTRL+SHIFT+ENTER used to make it an array formula....

My problem is, how do you handle a situation where one of the arrays has some blank cells... so for example cell A10,A11,A12 are blank, whilst cells B10,B11,B12 have content... this would obviously skew the data.... so is there a way to avoid having a MAX result returned if there are blank/"NULL" cells or at least ignore those cells?

[RobertMika]

=MAX(IF(A1:A20<>"",IF(B1:B20<>"",A1:A20-B1:B20)))
Confirm CSE.

[Crisp Packet]

Or a non array version as you have Excel 2010.

Please Login or Register  to view this content.