Arrays, Dates & Blank Cells

[David Lipetz]

Folks,

I'm struggling to resolve this Execl formula.

I've got a workbook with 2 sheets: Summary and Source Data. The workbook is
used a template so that data exported from another system can be copied and
pasted into the Source Data sheet and the Summary sheet will
programmatically parse the data to provide the results that I am looking
for.

The Source Data sheet has a number of named ranges. My problem relates to
the range named DATE (D210000). This range contains dates formatted as
"=DATE(2008,1,1)" for Jan 1, 2008 as an example. In virtually every case,
there will be blank cells in the range since the data is less than 10K
rows).

The Summary sheet uses formulas to summarize the data contained in the
Source Data sheet by month, category, and so forth. In cell $B$16, a numeral
(1-12) is entered to specifiy the month for which the records in Source Data
are to be summarized. I then use two formulas that examine the dates in the
DATE range and provide the MIN and MAX date so the date range can be
displayed.

Here are the array formulas I am using to get these MIN and MAX dates:
{=MIN(IF(MONTH(Date)=$B16,Date,"N/A"))}
{=MAX(IF(MONTH(Date)=$B16,Date,"N/A"))}

Here is my problem (assuming that the data in Source Data contains
transactions from 01/01/08 through 03/06/08):

When 1 is entered in $B$16:
MIN returns 0-Jan-00 (INCORRECT - should be 1-Jan-08)
MAX returns 31-Jan-08 (correct)

When 2 is entered in $B$16:
MIN returns 1-Feb-08 (correct)
MAX returns 29-Feb-08 (correct)

When 3 is entered in $B$16:
MIN returns 1-Mar-08 (correct)
MAX returns 6-Mar-08 (correct)

When 4 is entered in $B$16:
MIN returns 0-Jan-00 (INCORRECT - should be N/A)
MAX returns 0-Jan-00 (INCORRECT - should be N/A)

I think that the blank cells in the DATE range are screwing me up but I am
at a loss on how to check for them and also how to report N/A when the month
in $B$16 represents no transactions in the Source Data sheet.

Appreciate any advice.

Thanks,
David

 

[T. Valko]

An empty cell will evaluate to month number 1. To account for that:

When 4 is entered in $B$16:
MIN returns 0-Jan-00 (INCORRECT - should be N/A)
MAX returns 0-Jan-00 (INCORRECT - should be N/A)

If no dates meet the criteria then the result of those formulas will be 0
and if you have the cell formatted as DATE then you'll get 0-Jan-00. So, you
have to test the result of the formula to see if it is 0:

=IF(MIN(IF((date<>"")*(MONTH(date)=B16),date))=0,"N/A",MIN(IF((date<>"")*(MONTH(date)=B16),date)))

=IF(MAX(IF((date<>"")*(MONTH(date)=B16),date))=0,"N/A",MAX(IF((date<>"")*(MONTH(date)=B16),date)))

All formulas are array entered.

 

[David Lipetz]

Biff,

Outstanding! Thank you.

Please explain the significance of the * in your formula:
=IF(MIN(IF((date<>"")*(MONTH(date)=B16),date))=0,"N/A",MIN(IF((date<>"")*(MONTH(date)=B16),date))).

Does it mean AND? If so, how many *'s can be strung together?

Aside from my question above, I now understand the problem I was having and
how your solution corrects it. Many thanks!

David

 

[T. Valko]

Please explain the significance of the * in your formula:

Does it mean AND?
Yes

how many *'s can be strung together?

As many as you want as long as the formula doesn't exceed the length limit
(Excel version dependent).