X-axis Labels not displaying correctly

[EKLK]

I have a large data file (43K + lines) which produces line graphs of the data by specified ranges. I have just begun using defined name ranges and the data is presenting perfectly, but the x-axis labels are not. I have 3 different graphs based on the defined ranges for the current specified ReportPeriod month (a validation field based on the filtered source data), ReportRange (based on the selected ReportPeriod AND a specified starting month for the range-again a validation field based on the filtered data) and another for the entire data history.

I am having trouble with the labels on all three graphs in my current file. The original file that I set up (different client/data set) used 1 hour increments for my data (it is an electric meter file reporting usage every hour) and worked perfectly, labels and all. The new file that I am working on works well for the graphed data but not the labels. I don't know why it should make any difference, but the new file has the meter reporting every quarter hour (therefore 4 times the data).

For the range report (specified start month to specified report month - a variable range) as well as the total history report, the label for the first month of the period shows up on the graph. For the total history, the first month of the next year also appears - but none of the other months in the range display.

For the current report period graph, the xaxis is supposed to display the day of the month. For all odd-numbered day months the graph displays perfectly. For all even numbered day months - the only thing that shows up is the "1" corresponding to the first day of the period.

The data in this large file (using defined name ranges) is the same data that I previously used in a "normal" spreadsheet to present the same graphs and the labels always presented correctly using fixed xaxis range definitions. Why won't they work with defined name ranges?

 

[Hui]

Eklk

Firstly, Welcome to the Chandoo.org Forums

First select a set of data and manually check if the series for the X Values and Series Values are correct

Can you please supply a sample file with may 20 or so rows of data?

If not can you supply the named Formula you are using for the series X Values and Values

 

[EKLK]

Thank you for being willing to take a look. I tried to upload the file - but it was too big even after I deleted 80% of the data. Then I tried removing the summary statistics page and that still left it too large. I deleted about 90% of the graphs - still too large. Then I deleted extra meters (there were 7 altogether) and ALL of their charts, graphs and defined ranges. But still the file is too large to upload (2,448 KB) according to the error message I am receiving. I am guessing that it is a file limitation on this site, because I have emailed files this large before without difficulty. There just isn't any way apparently to provide you with a sample of this file. My guess is that the remaining defined ranges are what is making the file so large and to eliminate those would defeat the purpose of the question.

 

[Hui]

Try saving it as an Excel Binary file type

 

[EKLK]

My file (too big to upload no matter what I do to carve it down) has a set of 3 line graphs for each meter of data. The first presents the current month (from the "report period" selector on the dashboard)
The Current Month chart which should display days (1, 2, 3, etc) has the x-axis selected as:
=DNMeterDashboard.xlsx!Days
the defined name "Days" is: =OFFSET(Data!$M$9,0,0,COUNTA(Meter2Cur),1)
As mentioned previously, the days present correctly when the month has an odd number of days - but show only a 1 for any month with an even number of days.

The second graph reports a range selected by the user (from "start period" to "report period") and should display labels for all the months from the start month to the report month (inclusive), but the x-axis label is only displaying for the first month of the range. The chart data selection for the axis is: =DNMeterDashboard.xlsx!DynamicRangeXaxis
and the defined name "DynamicRangeXaxis" for this selection is: =OFFSET(Data!$L$7:$L$100000,MATCH(StartRange,Month,0),,COUNTA(Data!$B:$B)-1,)

The last graph is a total history chart which displays all the available months of data, but only the first month (Jan '15) and the first of the next year (Jan '16) labels are displaying on the chart. The x-axis label selection is: =DNMeterDashboard.xlsx!DynamicXaxis and the defined name is: =OFFSET(Data!$L:$L,8,,COUNTA(Data!$B:$B)-1)

When I click into the defined name edit box, each of the defined names highlights the desired selection of labels from the data sheet.

Any ideas?

 

[EKLK]

Binary filed version upload attempt -looks like it worked.

 

[Hui]

I think your Named Formula are too complex and confusing
So I simplified them to 6:

See attached

The Named Formula are listed below each chart

 

[EKLK]

Your file is better at labeling the graphs, but there is something wrong with the formulas. I took your revised wb, dropped in the rest of the 15 months of data that I had for that one meter and changed the report month field to incorporate 6 months in the range graph - but it only picked up 3 and the first few lines of the 4th month. The current month graph disappeared entirely and the total history graph stopped at the start of the 4th month as well, so somehow your dynamic definitions are not grabbing all the data that they should. Not sure how to proceed now.

 

[EKLK]

I just checked your name definitions and they were set to limit at line 8645, so everything stopped there. When I changed them to limit at 100000 (probable line for 2 yrs of data on this file), then the total history lost the labels just like my original file did - and the range graph disappeared entirely. I don't think that the complexity of my name definitions is the problem - your simpler ones are doing the same thing mine did. Since I selected June - I see that your current month graph is also doing the same thing mine did. I don't think that the complexity of the name ranges is the issue

 

[Hui]

In simplifying I skipped the expandability of the data
I've fixed that by adding a Named Formula Data
then reference the other Names to that instead of a fixed range

You will also need to copy Columns B & L down

 

[EKLK]

I downloaded your new version and the same label issues exist. I have compared the graph line that your version creates vs. the one that mine creates for the same meter info and they match, and I have already tested mine for including all the data that I expect to be reflected and it is correct - so it looks like yours is doing the data pull correctly - but the same labeling issues exist in your version as on mine when the full 15 months of data is in the file.

 

[Hui]

I didn't copy the helper Columns Down!

Can you re-post the file with extra data, as the First Column is Text not dates!

 

[EKLK]

On the total history - yours only shows the very first month label - mine also presents the first month of the 2nd year (not a big improvement - but something LOL). I was going to try sending you the file with the full 15 months of data for that one meter so you could see how it is operating with all the data - but even in binary it is just too big for the upload limits on this site. Is it possible that this label issue is just a limitation of Excel's capacity? I will laugh (as I cry!) if my first week working with defined name ranges and dynamic charts that I hit Excel's limits!

 

[EKLK]

I will try to send you a separate file without all the defined names, etc. just the dates & meter info for the 12 months past the original file and you can paste them in. Give me a minute to get it ready

 

[Hui]

Can you upload now!
I have reset the file size temporarily

 

[EKLK]

Here it is

 

[EKLK]

FYI-Your one month graph is set to the wrong column of data for the labels- it needs to be one more column to the right (9 not 8) to pick up the day of the month rather than the name of the month.

 

[Hui]

The issue with the Labels was the use of a " " space
I changed that to a ""
fixed

Its all Hunky Dory now



File is in the next post

Enjoy

 

[Hui]

Here is the file:

 

[Hui]

This version has the Gap between Tick Marks tweeked

As a ninja I get to cheat with File Sizes!

 

[EKLK]

So the problem was in the data!! It looked right - but as usual, Excel doesn't "see" the same thing that we do. Amazing - thank you for the catch!

 

[EKLK]

It is just completely weird that that one tweek fixed all the labeling problems. I still don't understand why that space between the "" would cause Excel so many problems but I will certainly make sure to leave no space between the quotes when I want to produce a blank cell in the future

 

[Hui]

A space makes the sell a String
A "" makes the cell a Null

Excel
Looks at the data and sees strings so treats the Labels as strings

When it sees the nulls it treats the labels as numbers as Nulls don't get used