Fiat Coupe Forum Fiat Coupe Forum
- Founded by Kayjey & James Northam - Funded by the Club for the benefit of all owners -
mixed RSS feedall RSS feeds
(this will scroll you to the RSS box)
Fiat Coupe Club UK
join the club
Fiat Coupe Forum
 
» Announced
    Old Forum BACK!
    Forum RSS feed!
    Images - finally!


» Related sites
    www.fiatcoupeclub.org
    www.fiatcoupe.net


» External data
    chatroom
    owners
 
Who's Online
7 registered (hangar1138, Gunzi, barnacle, magooagain, 2 invisible), 69 Guests and 5 Spiders online.
Key: Admin, Global Mod, Mod
Forum Stats
2,588 Registered Members
65 Forums
108,770 Topics
1,294,572 Posts

Most users ever online: 546 @ 30/08/2007 19:16
Top Posters
stan 32079
barnacle 31719
Theresa 22692
bockers 20993
pinin_prestatyn 19864
PeteP 18558
JimO 17908
Nigel 16843
Jim_Clennell 16212
Vickster 15915
RSS Feeds
Club Events
Club Information
Track Events
Rolling Road/RWYB
Social Events
Non-UK Events
Coupé Related Chat
Coupé Spotting
Coupé News/Press
Buying/Selling Advice
Insuring a Coupé
Basic FAQ's
How to Guides
Forum Issues
Technical Problems
General Maintenance
Styling
Tuning
Handling
ICE and Alarm
Coupés for Sale
Coupés Wanted
Parts for Sale
Parts Wanted
Group Buys
Business Forum
Other Vehicles for Sale/Wanted
Other Items for Sale/Wanted
Haggling/Offers
Ebay links
Other Cars
Other Websites
General Chat
Topic Options
#1611345 - 10/11/2017 11:08 Call for help - Excel chart whizzes!
Lucifer Offline
Forum is my job

Registered: 15/09/2007
Posts: 4115
Loc: 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!
_________________________
///M4 Competition Package

Top
#1611353 - 10/11/2017 12:47 Re: Call for help - Excel chart whizzes! [Re: Lucifer]
DaveG Offline
Club member 311
Forum is my life

Registered: 16/12/2005
Posts: 6750
Loc: Lightwater, Surrey, UK
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, ?
_________________________
2000 Pearl White Plus & 1996 Portofino 20vt
1985 & 2016 2017 Fiat 124 Spider + XF Sportbrake

Top
#1611355 - 10/11/2017 13:05 Re: Call for help - Excel chart whizzes! [Re: Lucifer]
Hyperlink Offline
Forum is my life

Registered: 28/06/2006
Posts: 5221
Loc: 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.


Edited by Hyperlink (10/11/2017 13:13)

Top
#1611363 - 10/11/2017 14:14 Re: Call for help - Excel chart whizzes! [Re: Lucifer]
bockers Offline
Hon Club Member 007
Forum Fossil

Registered: 16/12/2005
Posts: 20993
Loc: Chertsey in the Thames
Did someone mention Pivot Tables! shocked redcard banghead suicide

Top
#1611368 - 10/11/2017 16:14 Re: Call for help - Excel chart whizzes! [Re: Lucifer]
DaveG Offline
Club member 311
Forum is my life

Registered: 16/12/2005
Posts: 6750
Loc: Lightwater, Surrey, UK
No, pivot charts rolleyes tongue
_________________________
2000 Pearl White Plus & 1996 Portofino 20vt
1985 & 2016 2017 Fiat 124 Spider + XF Sportbrake

Top
#1611369 - 10/11/2017 16:20 Re: Call for help - Excel chart whizzes! [Re: Lucifer]
Hyperlink Offline
Forum is my life

Registered: 28/06/2006
Posts: 5221
Loc: Watford, Herts.
it will work so whats the problem?

Top
#1611376 - 10/11/2017 17:54 Re: Call for help - Excel chart whizzes! [Re: Lucifer]
DaveG Offline
Club member 311
Forum is my life

Registered: 16/12/2005
Posts: 6750
Loc: Lightwater, Surrey, UK
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.


Edited by DaveG (10/11/2017 22:15)
Edit Reason: corrected COUNTIF
_________________________
2000 Pearl White Plus & 1996 Portofino 20vt
1985 & 2016 2017 Fiat 124 Spider + XF Sportbrake

Top
#1611390 - 10/11/2017 21:27 Re: Call for help - Excel chart whizzes! [Re: Lucifer]
Hyperlink Offline
Forum is my life

Registered: 28/06/2006
Posts: 5221
Loc: 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.



Edited by Hyperlink (10/11/2017 21:37)

Top
#1611417 - 11/11/2017 14:00 Re: Call for help - Excel chart whizzes! [Re: Lucifer]
Lucifer Offline
Forum is my job

Registered: 15/09/2007
Posts: 4115
Loc: South Cheshire
Cheers folks, most helpful! smile

(though you'd think this would be an option that would come as standard!)
_________________________
///M4 Competition Package

Top