• 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.

Graph If - Adding to Chart If Conditions met

D

danrosey

New Member
This one has been haunting me for some time.


I am setting up a dashboard (which includes prices and dates) so that when I receive new raw data, my graphs refresh automatically. Thus, I've set up time graphs - think a line chart with the horizontal axis being "Months." Currently (since I only have data through July), the graphs will show data for January - July, then tapper off to zeros in July-December.


Data feed is two-fold: Raw data page, then sumifs on the dashboard page to calculate monthly totals. The graph data pulls from the dashboard page's sumifs. Because I want this to refresh automatically when I obtain new data, my cells for the future months default to "0" with a iferror statement. Because of this iferror formula, the graphing "Show Zero's as blank" command does not work.


I'd like to be able to create a graph that will only show the months to date, but when new data is available for an additional month, the graph will automatically add this item.


Is this possible without the use of VBA?
 
Should be possible, just need to setup some dynamic ranges. Assuming your formulas and data look something like this:

[pre]
Code: 
Months	Totals
Jan	$8,540.00
Feb	$3,916.00
Mar	$2,510.00
Apr	$6,626.00
May	$8,545.00
Jun	$8,635.00
Jul	$4,008.00
Aug	$4,541.00
Sep	$0.00
Oct	$0.00
Nov	$0.00
Dec	$0.00
[/pre]
In cells A1:B13. First, let's define how many months we want to look at. I'll assume that we base that off of today's date. To define a Named Range go to Insert - Name - Define (2003) or Name Manager under the Formula ribbon. Let's call this range "MyMonths" defined by this formula:

=OFFSET($A$2,0,0,MONTH(TODAY()),1)


What this formula does is define a range that has as many rows as the current month (aka, for August this will define 8 rows). Now, we need to define the range for our data. Simialry, let's call this range "MyData" defined as:

=OFFSET(MyMonths,0,1)


This formula says to look at our first range, but then look 1 column over.


Now, to modify the chart to use the new data. Select the series. You should see a formula that looks something like this:

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$13,Sheet1!$B$2:$B$13,1)


We want to change it to this:

=SERIES(Sheet1!$B$1,Sheet1!MyMonths,Sheet1!MyData,1)


Voila! Your chart will now adjust what data it looks at based on the current month.

For more info, check out here:

http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html
 
Luke all he needs to do is, add this to his formula. If(a1=0,#N/A

#n/a allows you to have your range you want but it wont show up on the graph until data is found!


Or if(iserror(a1),#N/A
 
Montrey, I'm not sure that solves the problem. Danrosey was asking how to ensure the graph only shows the months to date. Using your method, the problem still exists as the chart will show empty data for months beyond the current date. Luke's technique will automatically adjust the size of the chart such that it does not show empty data beyond the latest data point.
 
Awesome - I'll need to tweak with my data since it's running as a row but I can easily change.


Jordan, you are correct. #N/A is basically what I am working with now.


Thanks, Luke!
 
Sweet, glad it's working! Assuming you figured it out, but for others, the adjusted formulas dealing with data in a row would be:

=OFFSET($A$2,0,0,1,MONTH(TODAY()))


and


=OFFSET(MyMonths,1,0)
 
Luke - works great with my column data, but to get a better understanding of the formula I am trying to set it up with rows as well. Believe I've got my named ranges set up correctly, but I can't get the graph to go through.


Here's what I'm working with: Data is set up running across columns, month labels on top, with units on the row beneath.


Names (months) - =OFFSET(Numbers,-1,0)

Numbers (data) - =OFFSET(Sheet2!$D$21,0,0,COUNTA(Sheet2!$D$21),COUNTA(Sheet2!$21:$21)-1)


When I check the offset references in the names manager, it lines up correctly and appears to be working correctly. But when I go to substitute the ranges into the graph series, it errors.


Any thoughts?
 
Just giving this a friendly bump to see if anyone has any thoughts on why the formula above isn't adjusting for graphing.
 
Hi danrosey!

Good idea on the bump, I had not seen your response. =/


The formulas you listed appear to be correct. I'm assuming the -1 at end of Numbers formula is due to some sort of label in row 21 that you aren't wanting to count. Are the names of the Named Formulas exactly has you have written? I know that starting in 2007, a lot more words became reserved and cause trouble if you use them (like starting with Chart, 3 letter acronyms, etc).


Plugging in the formulas you listed with names, things are working okay on my end. I created a regular chart first, and the series had this formula:

=SERIES(Sheet2!$C$21,Sheet2!$D$20:$H$20,Sheet2!$D$21:$H$21,1)


Then, modify formula to this:

=SERIES(Sheet2!$C$21,Sheet2!Names,Sheet2!Numbers,1)

Hit Enter, and Chart updated correctly. Can you confirm what part is not working?
 
Danrosey


Can you post a sample file, Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Luke - exactly. I've tried changing the labels to various titles as well with no luck.


The part that isn't working is when I hit enter after those adjustments to the graph. It gives me the following: "A formula in this worksheet contains one or more invalid references. Verify that your formulas contain a valid path, workbook, range name, and cell reference."


Now that I've started to tweak this a bit, I'm running into some issues with my existing (working) dynamic graph. I notice now that the graph series reads:


=SERIES(Sheet2!$B$1,'Spend.xlsx'!Labels,'Spend.xlsx'!Values,1)


...where "Spend" is the name of the file. But I never explicitly typed those formulas? Is it required that the Range Name include a sheet or file reference? I may have incorrectly assumed that I could just title it "Value" not "Sheet2!Value."


*EDIT*

After some tweaks, I realize that my Series formula must include the sheet reference (Sheet2!) in order for this to work. Any explanation as to why would be welcome!


Thanks again for the help!
 
Hi danrosey!

As you found out, need to include the sheet reference. This is because it's possible to have the same name for a range but used on different sheets. Thus, when we reference the name range, we have to at least put the sheet name so that XL knows which one we're referring to. On top of this, "technically" XL also wants to know the name of the workbook that the named range is referring to, but this part is usually optional.

So, when wanting to reference the name, XL really keeps track of this:

[My Book.xls]MySheet!MyRange


However, when using the chart, you only need to give it the last 2 parts for it to figure out what you're doing. Finally, to make things "really" confusing, XL then simplifies things by removing the sheet name if it's not needed to clarify which sheet to look at.


Re-reading what I wrote, I know it's probably confusing, but hopefully having gone through the process it's somewhat clear. IT just boils down to XL trying to know exactly what you are refering to.
 
Luke - makes sense! Let's expand on this a bit.


My data is set up as calculations (unlike our example where the data used is essentially static unless manually changed)that pull from a data source when available. So this dynamic chart works great, however since I've got formulas in my data cells (essentially waiting for data to be available to calculate), my charts will always show all 12 months.


I've tried using iferror to show data as "blank" but Excel is reading my empty cells as zero.


The goal is for Excel to read the months without data (essentially any month in the future) as a blank and thus not plot in the same way the dynamic graph would not plot a month unless there is data available.
 
Do the formulas evaluate to "" or some other word, such as N/A? If so, let's replace the COUNTA function with just a COUNT, which only looks at numbers

=OFFSET(Sheet2!$D$21,0,0,COUNT(Sheet2!$D$21),COUNT(Sheet2!$21:$21)-1)


The alternative is do some some sort of COUNTIF based on another condition.
 
Rehashing this one as I've got some more complicated scenarios kicking around.


Let's say I've got my data set up in columns, and my dynamic range set up with the following formulas:


Spend: =OFFSET('INTERACTIVE I'!$AD$3,1,0,COUNT('INTERACTIVE I'!$AD$3:$AD$42))

Months: =OFFSET(Spend,0,-1)


In the example below, the dynamic range defaults to Jan 2012, through to Jan 2013 - which makes sense.

[pre]
Code: 
Spend
Jan 2012	9866.18
Feb 2012	0
Mar 2012	0
Apr 2012	0
May 2012	0
Jun 2012	0
Jul 2012	8708.04
Aug 2012	0
Sep 2012	0
Oct 2012	0
Nov 2012	0
Dec 2012	0
Jan 2013	0
[/pre]
My goal is to keep the zero between Jan-12 and Jul-12 values, but discard Aug through Jan-13 values. However, if I attempt this with a countif formula...


Spend: =OFFSET('INTERACTIVE I'!$AD$3,1,0,COUNTIF('INTERACTIVE I'!$AD$3:$AD$42,">0"))

Months: =OFFSET(Spend,0,-1)


The dynamic range shows Jan-12 to Feb-12 - which also makes sense.


So how would I write the formula so that the dynamic range would only grab Jan-12 to Jul-12? I've tried a few helper columns but keep creating circular references.
 
I'm assuming the criteria is that you are looking for the last non-zero number?

=OFFSET('INTERACTIVE I'!$AD$3,1,0,MAX(('INTERACTIVE I'!$AD$3:$AD$42>0)*ROW(INDIRECT("1:"&ROWS('INTERACTIVE I'!$AD$3:$AD$42)))),1)


4th argument is now an array function. Creates a true/false array looking for values greater than 0 and multiplies that with a row counter. The MAX function will then return the largest row counter, giving you the value I think you need. Not sure if you really want the 1 in the row_offset argument, but I left it in there.
 
and best of all a Custom mode that lets you tweak the system's blur and judder reduction elements to suit your tastes using two separate sliding scales.It’s fair to say that the LE40B651 has already delivered more features than most mid-range TVs. How can you judge this from a website? This should be your minimum requirement. hits and other impacts off from the phone.,<h2 style="white-space:nowrap;display:inline;font-size:14px;">wholesale nfl jerseys</h2>
    You should take advantage of the availability

of this vast information and then research in order to make the best selection

ever. But is the summer the best time for air conditioning installation or should you do it some other time? During the early spring and fall months these places often offer discounts on units.

Related articles:

 
You must log in or register to reply here.
Back
Top