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

Conditional Formatting For a Chart's Data Range?

L

LukeCWM

New Member
I have a calculator set up in Excel, and every day I manually fill three fields, which are then used in formulas to auto-fill nine other fields for the same date, allowing me to compare the necessary data.


I put in a chart to see the most essential data clearly, and it is nice.


What I would like to do is have the chart automatically expand to incorporate the new data as it is entered. I understand this is easy to do if a cell was blank and then is filled, but all of my cells have a Divide by Zero error until my daily data is entered, and the automatic chart data source isn't smart enough to discount fields with those values.


Is there a way to use a conditional formula to tell the chart to incorporate data only if the result is a number it can use instead of a Divide by Zero error? Or is there a way to use a conditional format to say "Check if J43 has a value: if yes, expand chart to row 43, if no, do not expand cart"?
 
You could create a dynamic chart range. Insert - Name - Define, give it a name of MyData, with formula like:

=OFFSET($J$2,0,0,COUNT(J:J),1)


(I'm assuming the chart data starts in J2.) Then, in your chart, for the y-axis, you'll define the data like:

='My Workbook.xls'!MyData


Further reading:

http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html

http://peltiertech.com/WordPress/dynamic-charts/
 
You could also wrap your formulas inside of an IF ISERROR statement and use the NA() function. The chart will ignore any cells with NA() in them.


For example:


Code: 
IF(ISERROR(A2,NA(),A2))


Regards,


-ep
 
Hello again. Thanks for the earlier responses, but I'm still not grasping this. Also, my formulas have gotten more complex.


Some of the fields are empty after the line I want, some of the fields have errors from an inability to divide by zero, and some of the fields just carry the numbers above them. So I don't think using an empty cell or a cell with an error will give me the results I want.


What would really be nice is a cell that contains a number, and the chart to be extended to the cell of the number contained in that earlier cell. Like A2 could contain the number 45, and my chart's data source could extend to be A3 to A45. And if I change A2 to 94, then the chart's data source would extend to be A3 to A94.


Currently, I'm using three charts, and I enter data daily. One of charts' data range is as follows:


=Data!$A$3:$A$78,Data!$U$3:$U$78,Data!$AK$3:$AK$78,Data!$AQ$3:$AQ$78,Data!$AW$3:$AW$78,Data!$BC$3:$BC$78,Data!$BI$3:$BI$78,Data!$BO$3:$BO$78,Data!$BU$3:$BU$78,Data!$CA$3:$CA$78,Data!$CG$3:$CG$78


I'd like to be able to automatically change all those 78's to be 79's, and then 80's, etc. For all three charts. To do this manually for all three charts daily is time consuming, especially since Excel won't let me scroll through or arrow through the length of the formula.
 
you could try including the offset function with your suggestion on cell A2:


ex: offset($a$3,0,0,a2)


When you change the value in A2, the offset function will change the length of the range. You'd need to do separate functions for each of the columns you're using.
 
Thank you, this looks fantastic. I'll see if I can find enough examples to learn it, and then work it into my document.
 
Where do I put the offset formula?


In the example from tushar-mehta.com, there is a formula for the x value and a formula for the y value. But I only see one place to enter data for my chart, which is a field called "Chart Data Range".
 
Hi ,


I am not really aware of the particular formula you are referring to , but I can make the following general comment :


A chart series is actually a formula , using the SERIES function ; its components are :


=SERIES([Series Name],[X Values],[Y Values],[Plot Order])


Bubble charts have a fifth parameter.


More details on this are available from the following link :


http://peltiertech.com/Excel/ChartsHowTo/ChartSeriesFormula.html


If you have to enter a formula for the X-values , enter this after the first comma in the =SERIES( ... ) formula which appears in the formula bar when you click on any data series in your chart. Similarly , the formula for the Y-values is entered after the second comma in the =SERIES( ... ) formula.


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