Fiat Coupe Forum
- Founded by Kayjey & James Northam
- Funded by the Club for the benefit of all owners
Fiat Coupe Club UK
join the club
Fiat Coupe Forum
 
» Announced
    Posting images


» Related sites
    Main club site
    fiatcoupe.net


» External data
    owners listed
 
Who's Online Now
1 registered members (Edinburgh), 245 guests, and 2 spiders.
Key: Admin, Global Mod, Mod
Forum Statistics
Forums69
Topics113,543
Posts1,340,417
Members1,785
Most Online731
Jan 14th, 2020
Top Posters(All Time)
barnacle 33,520
stan 32,122
Theresa 23,294
PeteP 21,480
bockers 21,070
JimO 17,917
Nigel 17,366
Edinburgh 16,659
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
Previous Thread
Next Thread
Print Thread
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
L
Lucifer Offline OP
Forum is my job
Lucifer  Offline OP
Forum is my job
L

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,383
Lightwater, Surrey
DaveG Offline
Club Treasurer Member 311
DaveG  Offline
Club Treasurer Member 311
Je suis un Coupé

Joined: Dec 2005
Posts: 8,383
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.
H
Hyperlink Offline
Forum is my life
Hyperlink  Offline
Forum is my life
H

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] #1611363
10/11/2017 13:14
10/11/2017 13:14
Joined: Dec 2005
Posts: 21,070
Chertsey in the Thames
bockers Offline
Hon Club Member 007
bockers  Offline
Hon Club Member 007
Forum Fossil

Joined: Dec 2005
Posts: 21,070
Chertsey in the Thames
Did someone mention Pivot Tables! shocked redcard banghead suicide

Re: Call for help - Excel chart whizzes! [Re: Lucifer] #1611368
10/11/2017 15:14
10/11/2017 15:14
Joined: Dec 2005
Posts: 8,383
Lightwater, Surrey
DaveG Offline
Club Treasurer Member 311
DaveG  Offline
Club Treasurer Member 311
Je suis un Coupé

Joined: Dec 2005
Posts: 8,383
Lightwater, Surrey
No, pivot charts rolleyes tongue


1996 Portofino 20vt & 2000 Pearl White Plus
1985½ & 2016 2017 Fiat 124 Spider + XF Sportbrake
Re: Call for help - Excel chart whizzes! [Re: Lucifer] #1611369
10/11/2017 15:20
10/11/2017 15:20
Joined: Jun 2006
Posts: 5,244
Watford, Herts.
H
Hyperlink Offline
Forum is my life
Hyperlink  Offline
Forum is my life
H

Joined: Jun 2006
Posts: 5,244
Watford, Herts.
it will work so whats the problem?

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,383
Lightwater, Surrey
DaveG Offline
Club Treasurer Member 311
DaveG  Offline
Club Treasurer Member 311
Je suis un Coupé

Joined: Dec 2005
Posts: 8,383
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.
H
Hyperlink Offline
Forum is my life
Hyperlink  Offline
Forum is my life
H

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
L
Lucifer Offline OP
Forum is my job
Lucifer  Offline OP
Forum is my job
L

Joined: Sep 2007
Posts: 4,115
South Cheshire
Cheers folks, most helpful! smile

(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

Powered by UBB.threads™ PHP Forum Software 7.7.1
(Release build 20190129)
PHP: 7.3.33 Page Time: 0.018s Queries: 15 (0.006s) Memory: 0.7838 MB (Peak: 0.8767 MB) Data Comp: Off Server Time: 2024-03-28 09:09:23 UTC