Dynamic Charting

**BHirsch619** · 03-20-2016, 12:52 PM

Hi everyone! I'm new to posting on this forum. I can usual find a post that helps me solve my problem. But not this time.

I'm making a lot of climate wind speed charts; a monthly average for each year on record for each weather station in my state.

I'm wanting to take the data from the B column in the following picture.

ExcelTable.JPG

And adjust the X-axis automatically such that it only displays the available data (i.e. 2010 - 2015).

ExcelChart.JPG

I'm going to repeat this process for each month and for an annual average. Because I have to do this for 30+stations I would like to automate as much of this process as I can. Any help would be much appreciated. I'm not sure if i have explained my problem fully so let me know if you need more information. Thanks!

**humdingaling** · 03-20-2016, 10:05 PM

thinking some offset formula with named range would do the trick
however with 30+ graphs...this may get tedious to manage

alternatively some macro to hide all the rows with NA would seem easier but is VBA viable for you?

any chance of sample data file so i dont have to mock everything up to test it

**BHirsch619** · 03-22-2016, 11:36 AM

It is actually 13 charts for 30+ stations. I eliminated quite a bit of data from file in order to get under the 1mb size limit.
But there is a chart for each month of the year and an annual chart.

Also I'm not sure if I attached the file or not. I attached it in the manage attachment page. But I couldn't get it to attach in the usual paperclip attachment option.
Hopefully it works!

**humdingaling** · 03-22-2016, 09:54 PM

So i got started and i couldn't stop at one thing....so i actually ended up changing many things to your sample file

first thing i fixed was your average formulas to ignore N/A

in b32

Formula:

Please Login or Register  to view this content.

in n9

Formula:

Please Login or Register  to view this content.

Secondly i added a data validation cell (drop down menu) to make your whole chart dynamic
select a month or annual and your chart will update accordingly

added 3 named ranges

Mon

Formula:

Please Login or Register  to view this content.

this determines what column to look under for chart data and CSE formula below

Chartdata

Formula:

Please Login or Register  to view this content.

this determines what to include into the chart

Chartlabels

Formula:

Please Login or Register  to view this content.

this updates the X axis chart labels accordingly

To make these named ranges run
i added two CSE formulas to determine the first and last value (based if the cell is a number)

First value is done by

Formula:

Please Login or Register  to view this content.

Last value is achieved by

Formula:

Please Login or Register  to view this content.

this would of been enough to make the chart "Dynamic"

however i found the charts - labels and titles and your long term average were not dynamic
so i did some wholesale changes to your labels sheet too

the whole sheet is run off the DV cell
'wind data'!Q2

i think you already understand how most of it works because you had most of it there....i just refined it slightly
have a look and if you require explanation let me know

**BHirsch619** · 04-04-2016, 05:02 PM

Thanks for the help! It took me awhile to figure out everything you have going on. I added a chart for the monthly averages. I wasn't sure how to add it to the drop down menu. So I just made a new chart for it.

Anyways, I have a future problem (if I can say that). As this is climate data, that data set is quite literally growing by the second. So when we update these charts, we need to adjust the averaging equation in the wind data sheet. For some stations it gets upwards of 190,000 rows of data. We are currently just copy and pasting the data into excel. So the number of rows of data in the RawData sheet will increase each time we update the charts. Is there away that the averaging range of data that the averaging equation will change automatically?

I took a stab at this by counting the number of cells that have data, but I was unable to get that number into the averaging equation. I have attached another sample spreadsheet.

Thanks again for your help!

**humdingaling** · 04-04-2016, 09:24 PM

Re: Drop down menu
its called data validation
heres a link with some information to get you started on it
http://www.contextures.com/xlDataVal01.html

i see what you tried there with the "MAX" named range...you were just missing the next step
ps you should avoid using named ranges that are also formula names so i changed that named range to MaxDataLines

ive attached the file again with the changes
essentially i added a named ranges (avgData,MonData,YearData) to get your averageifs formula working so it updates based on your MaxDataLines
picture and comments on file

have a look, if you understood what was done previously you should be able to understand what was done this time round

Dynamic Charting

LinkBack

Thread Tools

Rate This Thread

Display