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