Creating a dynamic bar chart based on counting blank cells.

Hi all,

I am having some difficulty using dynamic ranges to create a few graphs and was hoping someone out there could help!

A smaller version of my data can be seen in the attached Excel sheet:

(Note treatment days are in ascending order within ‘end of treatment reason’ categories.)

I need to create two plots, as shown in the spreadsheet.

1) A bar chart displaying the patients who are not on treatment with reason for treatment discontinuation along the x axis, and treatment duration along the y axis.
I have managed to successfully make this graph using dynamic ranges. I used the following:

Insert > Name > Define:
end_trt_reas: =OFFSET(Listing!$F$5,1,0,COUNTA(Listing!$F:$F)-1)
end_trt_reas_valA: =OFFSET(end_trt_reas,0,1)

Chart Wizard (Series Tab):
Name: =Listing!$F$3
Values: =Listing!end_trt_reas_valA
Category (X) axis labels: =Listing!end_trt_reas



2) This is the chart that is causing trouble! A bar chart displaying only the patients who are on treatment with a patient count along the x axis, and treatment duration along the y axis.

In a similar way to above, I tried to define names that would work, however as this graph relies on taking the number of blank cells in column F, the chart always errored. Of course new entries will be added to the date, hence why a dynamic range is required. I also need the patient count along the x-axis.

If anyone has a relatively good knowledge of dynamic ranges, I'd be really grateful for their help!

Feel free to post if I haven't made myself clear enough.

Many thanks,

Katie

I'm not very inclined to open a large Word doc with just pictures. Why don't you post an Excel file with some sample dummy data at a fraction of the file size.

Hello,

I'm not quite sure what you mean with "number of active patients". The patient number? A running count of patients?

I've created these two dynamic ranges and plugged them into the chart

Please Login or Register  to view this content.

see attached. We can take it from there. Pipe up with what you need.

cheers,

to get a total count on patients, both off and on treatment, you can use

=counta($A:$A)

to get a count of only those patients on treatment, you can use

=COUNT(ActivePatients)

Please let me know what you want to see in the X axis of the chart.