Update projections on a list

Occasional Contributor

I am a novice in Excel so I need some help:). I have a running sheet that I use to track and project our patient census and then it plots it out into a linear graph. I have Date and # of patients. Here is a snap shot:

 

11/21/202221
11/22/202221
11/23/202219
11/28/202222
11/29/202222
11/30/202220
12/1/202220
12/2/202220
12/5/202220
12/6/202220
12/7/202219

 

If I were to change the number of patients on a certain day, how do I get the subsequent days to reflect that change? A patient could start on 12/1 and have 30 treatments. Is there a formula I can use that would make this easier?

Thanks in advance for any help. 

2 Replies

@D_Varghese 

I'm going to assume that there is one treatment per day, expected to be administered on consecutive (as listed in column A) days.  If those assumptions are not true, see my last paragraph.

 

Yes, if you restrict the solution to formulas, and adding columns, I think this does what you are interested in, but it requires copying + pasting after calculation:

Treatment DateCount of PtsChange in CountNumber of Treatment Days AffectedEffective until Wksht RowWorksheet RowNew Count of Pts
11/21/202221-19991001220
11/22/202221  3320
11/23/202219-11216417
11/28/20222211217521
11/29/2022221410622
11/30/202220  7720
12/1/20222023038822
12/2/202220  9922
12/5/202220  101021

Columns C (Change in Count) and D (Number of Treatment Days Affected) are for input, at least temporarily.  Columns E (Effective until Wksht Row) and F (Worksheet Row) are calculated; they can be (and should be) hidden.  Formulas for the last three columns are:

 

for column E (Effective until Wksht Row), row 2; copy it down thru the last data row:
=D2 + ROW()
for column F (Worksheet Row), all rows:
=ROW()
for column G (New Count of Pts), row 2; copy it down thru the last data row:
=B2 + SUMIF(E$2:E2, ">" & F2, C$2:C2)

 

 

The SUMIF function in column G formula is doing the bulk of the work.  It applies the numeric change in number of patients (column C), but only to the number of rows indicated in column D.

 

The example above shows multiple scenarios, although I expect they would be entered and processed separately in real life:

  • On Nov. 21, one patient has relocated to Bora Bora, and so will not be treated again.  (The 999 days is probably larger than necessary; any number that extends past the end of your worksheet data will suffice.)
  • On Nov. 23, one patient will leave for holiday, and thus postpone a treatment previously scheduled for that date.  The value of 12 days reflects that this patient has only 12 treatments remaining as of that date.  The corresponding entry on Nov. 28 indicates when they will resume treatments.
  • On Nov. 29, one patient that requires only four treatments will start his/her treatments.
  • On Dec. 1, two patients will begin their 30-treatment regimen.

As those entries are made for each row, recalculation is (or can be) automatically made in column G (New Count of Pts).  After reviewing the data for reasonableness, you would:

  1. copy column G values (starting either from the top, or from the first input row through the last data row);
  2. paste the values (not the formulas, which is the default) into column B (starting at the same row you started the copying from); and
  3. clear the input in columns C and D (delete the content, not the worksheet cells).

(If the treatment center is unavailable on a scheduled day, say due to local flooding, and if all patients can accept the simple offset in scheduling, you need only delete the column A cell for that date, using the "Shift cells up" option.  But I could be wrong on that; it's late, and my thinking capabilities passed out about two hours ago.)

 

Note that there is nothing here that guarantees that the input to columns C and D are integers, as they should be.  Column C values can be negative, but column D values should not be negative.

 

Another solution would involve macros (VBA) and saving the spreadsheet as *.xlsm rather than *.xlsx.  Additional columns would not be required; the VBA code could react to user changes in column B values, and prompt for the number of treatment days that would be affected, then "instantly" change values in the appropriate succeeding rows.  Code could be included to validate user input.

 

But an even better solution may require patient-specific information.  And putting that information into a spreadsheet raises HIPAA concerns, as regards information security.  So the right tool for the job may be a database (such as SQL Server) and a program to report from it, not necessarily Excel.

wow thanks for the response!! Lots to look at but will let you know how this helps. Appreciate it a lot!