• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Charts - Bogus Date Interpolation and Not Updating

S

Seeker After

Member
Hello, all! I am a total newbie to forum posting, I usually read them and learn so much. Thanks for the years of Training. I am trying to create a Dynamic Chart with dates and formulas in the data set (see attached file) and it has me totally stumped. The chart needs to include data from the Date/Time (B, the X-axis), DATA1 (C), MEAN (D), Upper (E), and Lower (F) columns.The data in columns H-L is the data from the same columns recombined in a table. The challenge has been to get a dynamic chart that does not interpolate the gaps in-between dates into the chart and will still auto-update.
Up to this point, I can only do one or the other with this data set. The chart based on a table range will not (but should as I understand it) allow the chart to auto-update but does exclude the extra dates on the X-axis. The chart based on named ranges with offset formulas does just the opposite, allowing the auto-updating but will not exclude the unwanted dates. I can't seem to see another option. I have created simpler datasets and achieved success, not with my data.
Thanks in advance.
 

Attachments

  • DYNAMIC CHART TESTS.xlsx
    44.1 KB · Views: 1
Last edited:
Hi ,

The problem with the table is that the added data is not within the table , but below its boundaries.

When you wish to manually add data to a table , the correct way is to place the cursor in the bottom right cell of the table , and press the TAB key ; this will insert a new row within the table confines , and you can add fresh data in this row.

Adding data in this manner will ensure that all references are automatically updated , including chart series.

Secondly , the formula for the named range DATEG1 should be :

=OFFSET('R SQR'!$B$3,0,0,COUNTA('R SQR'!$B:$B) - 1)

The -1 was missing.

Narayan
 
Last edited:
Thanks so much Narayan! I don't suppose I had the range is set correctly on those charts that I included in my example. Previously I had been attempting to include the entire range, 100 or so cells, and exclude the blanks from the chart allowing the formulas to bring the data into the tables. Do I understand correctly that this is not possible? I don't want to input the data manually my intention is to automate the process it sounds as if I've missed several pieces. Does that clarify my question or make it worse? Also thanks for the tip on the formula. What does the -1 do?
 
Hi ,

The rationale behind using tables instead of ranges is that a table expands to include new data ; if you have columns with formulas , the formulas will automatically be entered in the rows that are added to accommodate new data.

Thus , when you use tables , never have blank rows in your table solely to accommodate new data.

Once you introduce blank rows in your table , the only way out will be to create named ranges to define your data ranges so that the blank rows are excluded.

Regarding your doubt about the -1 , this is because the definition of the named range DATEG1 is :

=OFFSET('R SQR'!$B$3,0,0,COUNTA('R SQR'!$B:$B) - 1)

where the result of the COUNTA function will determine the number of rows that the named range will refer to.

Since the range passed to the COUNTA function is an entire column , if we consider the range B1:B1048576 , the number of cells which are non-blank are 23 , from B2 through B24. Since the start reference in the OFFSET function is B3 , the formula will refer to the range B3:B25 , which is one more than what we want. This additional cell is because the header label Date/Time is also being included in the count of non-blank cells to arrive at a count of 23. Since your data starts from B3 and should not include the header label , we should use 22 for the number of cells with data , instead of 23. Hence the subtraction of 1 from the result returned by the COUNTA function.

Of course , since dates and times are numeric values , if we use the COUNT function instead of COUNTA , we would get the correct count of 22 , and would not need to subtract 1 from the count returned by the COUNTA function , which counts both numeric and alphabetic data.

Narayan
 
This is great Narayan, thanks! My problem was the COUNTA vs COUNT in the named ranges the whole time. The charts are working!! It seems that because the cells were filled by "" from the formulas were being counted also. This resulted in both charting errors I originally described. After replacing the COUNTA with COUNT balance and order have been restored. Thanks again.

Interesting to note that the tables now work too but I haven't puzzled that one out yet. I'm not immediately seeing the relationship.
 
You must log in or register to reply here.
Back
Top