Excel Medical Template Help Needed

[Doctor Frank]

Hello, I wonder if you might be able to guide me in the right
direction. I am a physician and need a bit of help. One of the
medications I prescribe is limited to prescribing it to only 100
patients at a time. This number is based on the script activity. For
example if I write for 30 days worth of the medication on January
first,,, that counts as 'one patient' for the next 30 days. If I
write for another patient on January 1st for 15 days worth of the
medication,, then he counts as a hit for the next fifteen days. So,
from the 1st to the 15th,, I will have '2' patients,, then after the
15th, I drop to one patient as the one patients script has expired.
Want to have a simple interface where the Doctor taps his name,,
enters a medical identifier for the patient and taps how long the
script is active for. Then the number of active patients appears in a
box. This is so the doctor does not have any more than 100 patients
active at any one time. One should also be able to type in a date in
the future,, say January 19th in this example,, and get the box to
show that on that date you will only have '1' active patient. Any
guidance would be appreciated,, I am doing this to help a patient
group that is in need,, I have no financial interest in selling this,,
just want to do it to help out. Thanks,,



F. Kunkel, MD

(e-mail address removed)

 

[Nick Hodge]

Frank

I've set up a worksheet (one for each doctor) with a heading in A1 of
'Active Scripts'. In A2 a heading of 'Future Date'

Starting in row 4 I have the following headers

A4 Patient
B4 StartDate
C4 No Days
D4 EndDate
E4 Active?

You then put a patient ID in the next row (Cell A5) a start date for the
course in B5 the number of days in C5. In D5 the formula

=IF(B5="","",B5+C5)

then in E5 the formula

=IF(OR(A5="",B5=""),"",IF($B$2<>"",IF(D5<$B$2,"No","Yes"),IF(D5<TODAY(),"No","Yes")))

Then in B1 enter the formula

=COUNTIF(E:E,"Yes")

You will then have the count of open scripts at today's date in B1 UNLESS
you add a date in B2 and then this will be the date that controls how many
open scripts there are

See if that works for you. Obviously you can copy the formulas in column D
and E as far as you like. They will show nothing until a patient ID is
entered, etc.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.excelusergroup.org
web: www.nickhodge.co.uk

 

[Joel]

You can do it with a single sumproduct as follows (don't) need columns D and E.

=SUMPRODUCT(--AND(($B2:$B100<=TODAY()),($B2:$B100<=(TODAY()+$C2:$C100))))

 

[Peo Sjoblom]

Can you explain how your formula works?


--


Regards,


Peo Sjoblom

 

[Joel]

The formula should be
=SUMPRODUCT(--($B2:$B100<=TODAY()),--(TODAY()<=($B2:$B100+$C2:$C100)))

You can see how the formula works by using themenu Tools - Formula Auditing
- Evaluate Formula. It will do a better job then my description
1) $B2:$B100<=TODAY() creates an array of 99 results of which cells where
the start date of the prescription is before todays date

{False,True,True,......}

Putting the two minus signs converts the true and False to 1's and 0's

2) $B2:$B100<=($B2:$B100+$C2:$C100) creates a second array of 99 items where
the end date is after todays date

{False,True,True,......}

Putting the two minus signs converts the true and False to 1's and 0's


3) Sumproducts will multiply each member of the 1st array with each member
of the 2nd arraqy and add the results

sumproducts({1,0,1,1,.......} + {1,0,1,1,.......})

 

[FloMM2]

Doctor Frank,
Or you could try this:
Cell A1 -"Patient's Name"
Cell B1 - "Number of Days"
Cell C1 - "Start date"
Cell D1 - "End Date"
Cell E1 - "Active"
Cell F1 - "Total"

Column A format - Text
Column B format - number
Column C format - Date
Column D format - Date
Column E format - General

Formulas:
In Cell D2 "=IF($C2="","",SUM(C2+B2))"
what this does is: If there is nothing in cell C2, there is nothing in cell
D2,
if there is a date in cell C2, then add the start date to the number of days
to find the End date.
In Cell E2 "=IF($D2="","", IF((NOW()>$D2,0,1))
what this does: If there is nothing in cell D2, there is nothing in cell E2,
if there is a date in D2, compare it to day, if it is before today 1, if
today is after D2, 0
These two cells are copied, all the way down their respective columns.
Formula in F2 "=SUM(E:E)"
what this does: adds all the 1's and 0's in column E.
So, as long as the value in cell F2 is less than 100, you can write another
prescription.

I could send you the excel file at "fak9717-at-hotmail-dot-com", if you want.
hth