shakethingsup
Board Regular
- Joined
- May 21, 2017
- Messages
- 64
- Office Version
- 365
- Platform
- Windows
Hi - I'm wondering if there is a more efficient way to think about this to present scenarios and charts.
This table shows how many patients get enrolled each month in a clinical trial for drug study and the cumulative patients enrolled over time (column I and J). The more clinics (sites) you activate, the faster you can screen patients and subsequently enroll. Let's ignore the uneveness of site activations for now (0 --> 20 --> 5 --> 5 for a cumulative amount of 30 sites). This is an input in itself so I'm trying to think about the "ramp" or the change in this one.
In the month of February, you activate 20 sites, you can see 0.25 patients per site = 0.25*20 = 5 patients in the month. Not every patient qualifies, 60% fail, so 40% qualify, 40% of 5 = 2 patients. You accumulate the results monthly. Clearly the two major inputs are enrollment rate and screen failure. "what if were to change those rates?"
I just designed it this way where someone chooses from the drop down menu in C2 and D2 the scenario and it just changes cells E12 and F12 respectively which then flows into the calculations.
After this I graph it in a chart.
I could easily create many columns to accodomate the various scenarios so one does not have to flip back and forth. Wondering if there's a better more efficient way to show all scenarios at once so they can be graphed and visualized.
I'd be willing to learn power query/power BI if this is the way to go. Never used those yet!
Thank you in advance
This table shows how many patients get enrolled each month in a clinical trial for drug study and the cumulative patients enrolled over time (column I and J). The more clinics (sites) you activate, the faster you can screen patients and subsequently enroll. Let's ignore the uneveness of site activations for now (0 --> 20 --> 5 --> 5 for a cumulative amount of 30 sites). This is an input in itself so I'm trying to think about the "ramp" or the change in this one.
In the month of February, you activate 20 sites, you can see 0.25 patients per site = 0.25*20 = 5 patients in the month. Not every patient qualifies, 60% fail, so 40% qualify, 40% of 5 = 2 patients. You accumulate the results monthly. Clearly the two major inputs are enrollment rate and screen failure. "what if were to change those rates?"
I just designed it this way where someone chooses from the drop down menu in C2 and D2 the scenario and it just changes cells E12 and F12 respectively which then flows into the calculations.
After this I graph it in a chart.
I could easily create many columns to accodomate the various scenarios so one does not have to flip back and forth. Wondering if there's a better more efficient way to show all scenarios at once so they can be graphed and visualized.
I'd be willing to learn power query/power BI if this is the way to go. Never used those yet!
Thank you in advance
