Array formula challenge

  • Thread starter Thread starter Laurence Lombard
  • Start date Start date
L

Laurence Lombard

I have not managed to get an array formula to work on this problem. I wonder
if any of you could help.

Below is an example of a list of Terminally ill patients. One would like to
know how many patients are on the care list at Givendate ie those who have
been admitted before Givendate, but who have not been put on hold OR have
died by GivenDate. A patient could be on hold AND died - the formula must
not double count this case.

One needs to check for empty cells hence my test B1:B100>Date(1900,1,1)
My attempts have gone along the following (split for readability)

SUM(
(B1:B100>Date(1900,1,1) * (B1:B100 < GivenDate)
*OR
(C1:C100 > Date(1900,1,1)*(C1:C100 >= GivenDate)
,
(D1:D100>(Date(1900,1,1) *( D1:D100>=Givendate)
)


A,B,C,D
NAME,ADMIT,ON HOLD,DIED
Jack,01/03/2003,02/04/2003
Steven,15/03/2003,,19/04/2003
Mary,16/03/2003,18/03/2003,25/03/2003
Peter,01/04/2003
Susan,15/06/2003
 
I managed to work it out.


=SUM(IF(B$2:B$100>DATE(1900,1,1),(B$2:B$100<F2),0)
*IF(C$2:C$100>DATE(1900,1,1),(C$2:C$100>=F2),1)
*IF(D$2:D$100>DATE(1900,1,1),(D$2:D$100>=F2),1))

Line 1:Return 1 if Admissiondate is not empty and is before Givendate else
return 0
Line 2:Return 1 if Died is empty OR (Died is not empty and after Givendate)
Line 3:Return 1 if On Hold is empty OR (On Hold is not empty and after
Givendate)


This variation does the same thing but the formula follows the explanation
more correctly
=SUM(IF(ISNUMBER(B$2:B$100),(B$2:B$100<F13),0)
*IF(NOT(ISNUMBER(C$2:C$100)),1,(C$2:C$100>=F13))
*IF(NOT(ISNUMBER(D$2:D$100)),1,(D$2:D$100>=F13)))

Line 1: Return 1 if Admissiondate is a number[Date] and is before Givendate
Line 2: Return 1 if Died is Not a number [empty] OR after Givendate
Line 3: Return 1 if On Hold is not a number [empty] OR after Givendate

Thanks
Laurence
 
Back
Top