2 registered members (386ka, Mags),
347
guests, and 3
spiders. |
Key:
Admin,
Global Mod,
Mod
|
|
Forums69
Topics113,543
Posts1,340,442
Members1,785
|
Most Online731 Jan 14th, 2020
|
|
|
Call for help - Excel chart whizzes!
#1611345
10/11/2017 10:08
10/11/2017 10:08
|
Joined: Sep 2007
Posts: 4,115 South Cheshire
Lucifer
OP
Forum is my job
|
OP
Forum is my job
Joined: Sep 2007
Posts: 4,115
South Cheshire
|
Hey folks, despite trying to look for solutions online I seem to be getting nowhere with a charts query in Excel. I wonder whether anyone on here could possibly help please.
I have a spreadsheet which pulls together data from a variety of hospitals. I have a chart to show the data. However, the number of hospitals in the data changes depending on various conditions met by other formulas.
So let's say for example my chart range currently covers cells A1:B21. At present I have 20 hospitals plus the header row, and the chart range covers it perfectly. However, if I adjust my settings (for example I decide to only show smaller hospitals), there might now only be ten hospitals plus the header row, so technically my data are only showing in rows A1:B11 (as my formulas are set to show blanks in the rows afterwards).
However, my chart range is still covering A1:B21, and there are a load of empty bars on my chart with nothing in them. Sure, I can re-adjust my range manually, but this is a pain.
Does anyone know whether there are any settings in the charts somewhere which will completely ignore empty rows, ie fill all the available space with the rows which have data, even if there are a load of blanks afterwards? This would make my life sooo much easier.
Thanks in advance - hope it's possible!
2016 Jaguar F-Type R AWD; 2017 BMW M4 Competition Package; 2015 Range Rover Evoque 2.2 Tech
|
|
|
Re: Call for help - Excel chart whizzes!
[Re: Lucifer]
#1611353
10/11/2017 11:47
10/11/2017 11:47
|
Joined: Dec 2005
Posts: 8,385 Lightwater, Surrey
DaveG
Club Treasurer Member 311
|
Club Treasurer Member 311
Je suis un Coupé
Joined: Dec 2005
Posts: 8,385
Lightwater, Surrey
|
It should be possible, creating a dynamic range name and getting the chart to use that name, it's not something I've done before, but I have found a few references.
When you show smaller hospitals to get data in cells A1:B11, are cells A12:B21 all blank? And in cells A1:B11 are there any blank cells? In other words, are all the empty bars at the end of the range (at the right hand end of the chart)?
Can you share a simple example table showing perhaps 10 rows or so, first with 10 hospitals, and then another example showing how the smaller list would look? What chart type are you using - XY, Line, Bar, ?
1996 Portofino 20vt & 2000 Pearl White Plus 1985½ & 2016 2017 Fiat 124 Spider + XF Sportbrake
|
|
|
Re: Call for help - Excel chart whizzes!
[Re: Lucifer]
#1611355
10/11/2017 12:05
10/11/2017 12:05
|
Joined: Jun 2006
Posts: 5,244 Watford, Herts.
Hyperlink
Forum is my life
|
Forum is my life
Joined: Jun 2006
Posts: 5,244
Watford, Herts.
|
I think would a pivot chart will work?
You will just need to refresh the chart each time to update there is a change.
Last edited by Hyperlink; 10/11/2017 12:13.
|
|
|
Re: Call for help - Excel chart whizzes!
[Re: Lucifer]
#1611376
10/11/2017 16:54
10/11/2017 16:54
|
Joined: Dec 2005
Posts: 8,385 Lightwater, Surrey
DaveG
Club Treasurer Member 311
|
Club Treasurer Member 311
Je suis un Coupé
Joined: Dec 2005
Posts: 8,385
Lightwater, Surrey
|
I'm sure pivot tables/charts will work but I think you need to explain to OP how to do it.
Using dynamic ranges is a bit fiddly but works. Assume we have a range of values in B2:B21 that we want to plot just as a simple bar chart without referencing any other values, eg: in A2:A21 - if we do want to reference those, we need to set up another dynamic range.
Click on the Formulas ribbon and select Define Name. Create a new name, eg "plot" (without the quotes) and in the Refers to: box type =OFFSET(Sheet1!$B$2,0,0,COUNT(Sheet1!$B$2:$B$21),1) and press OK. This creates the dynamic range that contains all the cells with values in and ignore any empty cells at the end.
Then right click on your chart and choose Select Data... and then in the Horizontal (Category) Axis Labels box on the right, click Edit. In the box, type =Test.xlsx!plot and press OK. Note that you have to put in the filename, not the worksheet name, so you may need single quotes around the filename.
Now as the number of rows with actual data in changes, the chart will change (you might need to press F9 to update if the number of rows reduces), but it won't work if there are any "empty" rows in the middle of other rows with data.
However this will only work if the empty cells contain text (eg: some empty text) or =NA() for example, if they contain a zero value it will treat it as a value to plot. To exclude zeroes, change the COUNT(Sheet1!$B$2:$B$21) to COUNTIF(Sheet1!$B$2:$B$21,">0") for example.
Last edited by DaveG; 10/11/2017 21:15. Reason: corrected COUNTIF
1996 Portofino 20vt & 2000 Pearl White Plus 1985½ & 2016 2017 Fiat 124 Spider + XF Sportbrake
|
|
|
Re: Call for help - Excel chart whizzes!
[Re: Lucifer]
#1611390
10/11/2017 20:27
10/11/2017 20:27
|
Joined: Jun 2006
Posts: 5,244 Watford, Herts.
Hyperlink
Forum is my life
|
Forum is my life
Joined: Jun 2006
Posts: 5,244
Watford, Herts.
|
Possibly.
Select the data set (for the max range required) click add pivot chart on the ribbon which on my version was next to the usual charts. Select a location for the pivot table/chart (either the same sheet or a new one). Once done drag the x category (hospitals?) to the the bottom left box and the values into the bottom right box of the pivot table/chart setup. Right click the value in the bottom right box and select sum rather than count. I think from memory that’s it. You can deselect any 0/blacking value from the table by unchecking them in the pivot tables via the drop down box. If you do this also update the x category in the right box to include new values in manual selections - it’s a check box on the drop down.
This will work with any data and not matter if there are gaps and/or blanks/non blanks etc - you can just exclude any data you want if needed.
Last edited by Hyperlink; 10/11/2017 20:37.
|
|
|
Re: Call for help - Excel chart whizzes!
[Re: Lucifer]
#1611417
11/11/2017 13:00
11/11/2017 13:00
|
Joined: Sep 2007
Posts: 4,115 South Cheshire
Lucifer
OP
Forum is my job
|
OP
Forum is my job
Joined: Sep 2007
Posts: 4,115
South Cheshire
|
Cheers folks, most helpful! (though you'd think this would be an option that would come as standard!)
2016 Jaguar F-Type R AWD; 2017 BMW M4 Competition Package; 2015 Range Rover Evoque 2.2 Tech
|
|
|
|