Oct 13 2022 10:01 AM
Oct 13 2022 10:01 AM
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:
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.
Oct 13 2022 11:12 PM
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 Date||Count of Pts||Change in Count||Number of Treatment Days Affected||Effective until Wksht Row||Worksheet Row||New Count of Pts|
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:
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:
(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.