I need to ignore blank cells in an excel 2007 MIN array but I keep getting 0's

Question

Here is the array I have:

{=MIN(IF('APRIL 2011'!E3:E999<>"",'APRIL 2011'!E3:E999  
 *('APRIL 2011'!E3:E999*('APRIL 2011'!D3:D999='APRIL TOTALS'!A2))))}  

It still returns zeroes with empty cells!

Answer 1

You can use the ISBLANK() function to test for blank cells. IF cell A1 is blank, then =A1="" evaluates to true, and so does =A1=0

I don't know of an expression to return a blank value to a cell, so I use "" instead, like this:

=IF(ISBLANK([expression]), "", [expression])

Answer 2

Nest your conditions. You are trying to mashup IF criteria with (VALUE)*(TRUE/FALSE) and the when the TRUE/FALSE is FALSE, you are multiplying by zero. In this way, you are discarding blank cells but artificially seeding your result set with zeroes for every row where column D <> 'APRIL TOTALS'!A2.

As an array formula,

=MIN(IF('APRIL 2011'!E3:E999<>"", IF('APRIL 2011'!D3:D999='APRIL TOTALS'!A2, 'APRIL 2011'!E3:E999)))

Array formulas need to be finalized with Ctrl+Shift+Enter? (but you already knew that!).

Answer 3

The issue with your formula is that you are returning the minimum of an array of Boolean products. Because these are Boolean products, products with a FALSE multiplier (the ones you intend to exclude) are counted as 0.

I see a way around this as long as Column E contains no zero values. I've made two big changes to your original formula:

1. To remove the Boolean multiplier problem, I've used an IF statement to choose multipliers instead of 1 and 0 for TRUE and FALSE. TRUE is still 1, but FALSE is now a large number -- make it larger than any value in Column E. This way, the blank entries will only be the minimum value if they are the only value.

2. To take care of the case where the large number will be returned, I've made it such that the Minimum value is calculated only if there are non-blank values in Col E for Col D values = A2. If there are no such values, "" is returned.

    {=IF(COUNT(IF('APRIL 2011'!D3:D999='APRIL TOTALS'!A2,  
      IF('APRIL2011'!E3:E999<>"",'APRIL2011'!D3:D999)))=0,""  
      MIN(IF('APRIL2011'!E3:E999<>"",'APRIL2011'!E3:E999,10000000000)  
      *IF('APRIL 2011'!D3:D999='APRIL TOTALS'!A2,1,1000000000000)))}