Solution for charting IF statements that return blank values

Hi everyone,

I know a big issue for people is that when you use an IF formula to
return a blank cell and graph it, it returns a zero value, for example
if(a1>0,a1,"") returns a blank cell that charts as a zero.

There is a solution to this problem that does not involve the more
common returning of an NA() value and conditionally formatting it out.

The solution is the use of dynamic ranges to set the chart values.  It
is relatively easy and good practice as you can easily update and
reference data on other sheets.  What you do is define a named range
for your data which dynamically extends with your information.

Step 1:
>From the top file menu's select Insert > Name > Define.

Step 2:
Give it a name that you will remember, that cannot start with a number.

Step 3:

Define your range using the following formula:

=$b$17:INDEX($b$17:$m$17,1,MAX(IF($b$17:$m$17<>"",COLUMN($b$17:$m$17)))-COLUMN($b$17)+1)

But replace $b$17 with the FIRST cell in the row, and replace $m$17
with the LAST cell in the row.  (Make sure you use absolute references
($) otherwise it will not work!).

NB: If your data is in columns, not rows, you can reverse the last two
formulas in the INDEX formula and use the ROW function instead of
COLUMN.  (ie, the INDEX formula uses row and then column -
INDEX(array,row_num,column_num)).  Please post if you would like a ROW
formula posted.

Step 4:

In your chart, right click, select Source Data, select the series you
wish to set dynamically, and in the Values box enter ='[Sheet
name]'![Named range from Step 2].

Good luck!

0
peterwe (1)
12/5/2006 4:11:00 AM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
382 Views

Similar Articles

[PageSpeed] 1

This is the preferred approach, when the "blanks" occur at either end of the 
range. When they occur in the middle, you have fewer options.

Another approach is to have two data ranges, one for viewing tabular output, 
and one laid out for efficient charting. The former links to the original 
data with "", the latter with NA(). The benefits of this include flexibility 
in layout and format of the tabular output, without worrying about getting 
it to plot the way you want; not requiring CF to hide errors in the table; 
not hiding the errors in the chart data, so you can more readily debug data 
issues; and more. The two data regions can be linked to each other, or 
preferably to a third main data sheet.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


<peterwe@gmail.com> wrote in message 
news:1165291860.617725.283550@j44g2000cwa.googlegroups.com...
> Hi everyone,
>
> I know a big issue for people is that when you use an IF formula to
> return a blank cell and graph it, it returns a zero value, for example
> if(a1>0,a1,"") returns a blank cell that charts as a zero.
>
> There is a solution to this problem that does not involve the more
> common returning of an NA() value and conditionally formatting it out.
>
> The solution is the use of dynamic ranges to set the chart values.  It
> is relatively easy and good practice as you can easily update and
> reference data on other sheets.  What you do is define a named range
> for your data which dynamically extends with your information.
>
> Step 1:
>>From the top file menu's select Insert > Name > Define.
>
> Step 2:
> Give it a name that you will remember, that cannot start with a number.
>
> Step 3:
>
> Define your range using the following formula:
>
> =$b$17:INDEX($b$17:$m$17,1,MAX(IF($b$17:$m$17<>"",COLUMN($b$17:$m$17)))-COLUMN($b$17)+1)
>
> But replace $b$17 with the FIRST cell in the row, and replace $m$17
> with the LAST cell in the row.  (Make sure you use absolute references
> ($) otherwise it will not work!).
>
> NB: If your data is in columns, not rows, you can reverse the last two
> formulas in the INDEX formula and use the ROW function instead of
> COLUMN.  (ie, the INDEX formula uses row and then column -
> INDEX(array,row_num,column_num)).  Please post if you would like a ROW
> formula posted.
>
> Step 4:
>
> In your chart, right click, select Source Data, select the series you
> wish to set dynamically, and in the Values box enter ='[Sheet
> name]'![Named range from Step 2].
>
> Good luck!
> 


0
jonxlmvpNO (4558)
12/5/2006 1:20:44 PM
Reply:

Similar Artilces:

Deleting "hidden" charts on a worksheet
Hi everyone, I've been playing around w/ a worksheet that contains data and charts, inserting and removing multiple rows at a time. Unfortunately, I did not change the properties of the charts and they were all set to "Move and Size w/ Cells". When I deleted a series of rows, these charts disappeared and left a line across the spreadsheet where they used to be. I've tried everything to get rid of them (because they are now calculating incorrectly and giving me error messages), including deleting the rows where they appear, trying to select them on the sheet, but nothing w...

Prepending values in a column with "\"
I would like to prepend all text values in a column with "\". What is the easiest way to do this if there are thousands of rows? Assuming column A has the text values. In B1 enter ="\" & A1 Double-click on fill handle of B1 to copy down. Copy then paste special(in place)>values>OK>Esc. Delete original column A if desired. Or you could use a macro to do in place with no formulas. Sub Add_Text_Left() Dim cell As Range Dim moretext As String Dim thisrng As Range On Error GoTo endit Set thisrng = Range(ActiveCell.Address & "," &...

Pivot chart and Total row
Hi All I have a Pivot table and attached Pivot chart (all good so far). However, there are three columns (drop column details here) which are the two columns of data and a grand total column (showing the average of my two data columns) When I turn it into a pivot chart it does not display the grand total column as well (is there a way to do this). I have tried to go into the chart options but these are locked. I can copy and paste the results but this is a bit clumsy. Any suggestions - any help greatly appreciated Kind regards Rexmann PS using excel 2003 ...

Surpressing plot of blank cells
I'm creating a line chart plot using Excel 2007. The data range for plot lines contains some blank cells. These blank cells get plotted as zero points. How do I change this behavior to plot only points that have values and to leave the blank cells unplotted? I appreciate your help, -John Hi, If they are truly blank then check the plot empty cells setting via. Select Data > Hidden and Empty cells. If the Cells in fact contain formula then use NA() instead of "". This will suppress the data marker but will not break the line. Alternatively use Autofilter to hide un...

Changing cell values through form
Hi... I'm not that good with access so I decided to look for help here. I have a table filled with drivers. Starting number, Driver name, and Number of laps are the columns. My Idea is to make a form that will list all the drivers, and add a value of +1 into the "Number of laps" cell to that driver when double clicked. That would be a sort of Lap counter form... Every suggestion is welcome. -- ::: www.ktvj-online.com - Motocros Portal ::: ::: www.forum.ktvj-online.com - Forum ::: ...

Re : Excel Range of Values Amidst Characteristic Transitions
Re : Excel Range of Values Amidst Characteristic Transitions Enter an Excel worksheet ; now that the tabulation is prepared for (preferential) presentation, Column A is shown as follows :- 01 8.0 02 8.0 03 <A Blank Row> 04 8.0 05 <A Blank Row> 06 <A Blank Row> 07 9.0 08 <A Blank Row> 09 9.0 10 9.0 11 <A Blank Row> 12 6.5 13 6.5 14 <A Blank Row> 15 <A Blank Row> 16 <A Blank Row> 17 8.0 18 <A Blank Row> 19 8.0 20 <A Blank Row> 21 8.0 22 <A Blank Row> 23 6.5 24 <A Blank Row> 25 ...

2nd try, parameter values
I'm having problems posting... sorry if this comes up twice. I have parameter values set to make things easier for the users... The prompt pulls Last Name or Emp #. If the Emp# is not available, is there a way to search by last name, including suffixes, but only entering the last name? or even just the first letter of the last name? On Thu, 19 Jul 2007 09:31:24 -0700, melinda.pluma@agedwards.com wrote: >I'm having problems posting... sorry if this comes up twice. > >I have parameter values set to make things easier for the users... > >The prompt pulls Last Name or...

How do I delete chart superimposed on Excel document?
Created Excel document. Print preview did not show gridlines. In attempting to get gridlines, somehow got into charts. A chart was created and is superimposed on original document. How do I get rid of the chart? How do I get gridlines on to original document? Oz Right-click on the chart and Edit>Clear>All To print gridlines.................File>Page Setup>Sheet Check "gridlines" and uncheck "draft quality". Gord Dibben MS Excel MVP On Fri, 4 Aug 2006 14:27:01 -0700, Oz Osborn <Oz Osborn@discussions.microsoft.com> wrote: >Created Excel docume...

VBA to insert .xlborder if cell value not equal to previous cell
I've got a worksheet and I'm wondering whether it is possible to insert a line when a value in Column A, B, C & D does not equal the values in the row above or below it. I've currently got a formula in Column A that reads.... =IF(AND(B3=B2,C3=C2,D3=D2,E3=E2),"","IL") and a conditional format that if the cell value is equal to "IL" then put a border. Wondering if there is a better way to do this via VBA or is that the better way? In your conditional formatting formula, instead of =A1="IL" (which I assume is what you've...

Using commas in "Value List" list boxes
I am trying to add a column that contains a comma to a 'value list' list box. I have a patient name (Last, first MI) column and a patient ID column (among others). I have my Row Source Type set to Value List and the number of columns correct, column widths etc. all correct, but when I try and do this: lstbox.AddItem "TreatmentId;LastName, FirstName MI;PatientId" The help documentation shows that the delimeter should be the ; not comma's, however instead of getting the name all in one column, it is being placed in two separate columns (because of the comma... it is...

Chart: range info from seriescollection?
I would like to fill a combobox (Rowsource) with data that are used in for instance the XValues or Values of a Seriescollection of a graph. "ActiveChart.SeriesCollection(1).Values" does not give the right type (I was hoping for a string type of info like "Sheet1.Range(a1:a10)"). How to get a Range to be used as Rowsource for my combobox??? Thanks for your suggestions, JB The Values and XValues will return an array of values. To get the range address like this, you will need to parse the series formula. Or you could use John Walkenbach's class module for extractin...

Is it possible to save an excel chart as .emf?
I would like to save an excel chart as .emf instead of .jpeg or .gif using the script below. Is this possible? Sub SaveChartAsGIF () Fname = ThisWorkbook.Path & "\" & ActiveChart.Name & ".gif" ActiveChart.Export FileName:=Fname, FilterName:="GIF" End Sub ...

Step change charts
Help, I am trying to create a bar charts by year but only want to show increases and decreases through the middle years as step changes i.e. 1999 100 2000 +10 2001 -30 2002 +40 2003 120 How can I do this? What I should see is the first vertical bar being 100 then from the 100 level an increase of 10 to get to 110 then a decrease bar of -30 (from the 110 level) to get to 80 etc etc. For a tutorial, see the Excel | Charts | Waterfall Chart page of my web site. -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Cus...

Excel Bar Chart with many elements Example
I am working with a survey database where one field represents one question in the Survey. Some questions allow the user to enter multiple answers. This data isn't normalized but I have t work with what I've got. Anyway I tried to create a chart in Acces with the wizard but got the error: "Cannot have more than elements in the chart". Anyway I attempted to recreate the char in Excel but my chart looks crammed and canno print entirely within one page. Does anyone know how or have a example of how to create a bar chart with many elements in a clea and consice manner T...

stop opening a blank workbook at start up
how do I stop excel from opening a new workbook everytime I start up the application. Ad a shortcut to the Excel.Exe file and append the properties of the shortcut with /e Shotcut will look like this: "C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE" /e "km" <km@discussions.microsoft.com> wrote in message news:8E495132-B698-40B7-B964-D2982513D93A@microsoft.com... > how do I stop excel from opening a new workbook everytime I start up the > application. ...

Can no longer cut/paste ranges in chart "edit data" fields?
In Excel 2007 I can no longer cut/paste cell ranges into the x and y datasets using the edit data tool. Is there a way to turn this back on? Also the chart range resets itself to the current length of the data, however, in many instances I want to set an exact future range. Is there a way to have excel not reset the range to what it "thinks" I want. > In Excel 2007 I can no longer cut/paste cell ranges into the x and y > datasets > using the edit data tool. Is there a way to turn this back on? I have no problem doing what you can't. Could you provide more det...

how to down load more than 1 year statements
HI I like to download more than one year of statment from my brokerage. But it only goes back to 1 year. I want to download statement from 1-1-2003 to 12-31-2003. but since I am downloading in March 2004 it only goes back to march 2003. I had money 2001 it didn't have the option to download more than one year of statement. I got mony 2004 but same thing it only goes back to March 2003. I have noticed that it can arrange reports from 1-1-2003 to 12-31-2003. But still missing the actual data from 1-1- 2003 to March 2003. Please advise how I can achieve that goal. Thanking in anticipa...

Changing bar graph colours according to value
Is there any way to have the colours of the bars in a bar graph change according to value? Hi, Have a look at Jon's example. http://peltiertech.com/Excel/Charts/ConditionalChart1.html Cheers Andy nikki8327 wrote: > Is there any way to have the colours of the bars in a bar graph change > according to value? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Need to format text in header, but value is generated using VBA
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C6360C.2372C770 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, I wanted to reference a cell value in my spreadsheet header and found = out how to do that using this function: Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim WS As Worksheet For Each WS In Worksheets ActiveSheet.PageSetup.RightHeader =3D _ Format(Worksheets("Time Period Info").Range("B3").Value) =20 Next WS End Sub However, this leaves my text for ...

how to make this work if sum=5+n2 then sum becomes the value of s.
I want to get or create a formula in excel or access that allows me to keep a running total of my supplies ie... I have 2 pens, remove one and receive 2. answer in the cell becomes 3 pens then if I zero out the received cell the inventory cell still remembers that I have 3 pens not 4 pens (because I received 2 more pens) and visa versa when I zero out the received cell it remembers I have 3 pens not 1 pen(because I removed 1 pen) I believe the method you suggest is flawed. You have no audit trail. If the number on the sheet doesn't match your actual inventory, how will you figure out...

can not see chart
I created a simple chart. For some odd reason the chart is all gray. I can only see the chart if i select the chart object. Please help! Thank you! Hi, Via the menus Tools > Options. On the View tab select Obejcts Show All. Cheers Andy Nick wrote: > I created a simple chart. For some odd reason the chart is all gray. I can > only see the chart if i select the chart object. Please help! Thank you! -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

on server reboot multiple emails get returned as undeliverable
I have an SBS 2003 server running exchange, all latest service packs etc. We have hit a problem a couple of times when on reboot of the server end users get emails returned as undeliverable as they have exceeded the delivery date. It does not affect all outgoing messages, and the destinations are valid. It is as though the SMTP gets stuck on some messages, and only notices on re-start. Any ideas? F Frank <noreply@127.0.0.1> wrote: > I have an SBS 2003 server running exchange, all latest service packs > etc. > We have hit a problem a couple of times ...

Print notes on purchase order blank report
How can I print the notes from purchase order header on the purchase order blank report? thank you in advance. You can modify the PO in Report Writer. Link the Records Notes Master to the Purchase Order HEader table and include the notes table in the report. Then the notes text field will be available to be displayed in the report. Regards Sivakumar V On Nov 17, 6:38 am, "Jack Tundra" <jacktun...@hotmail.com> wrote: > How can I print the notes from purchase order header on the purchase order > blank report? thank you in advance. Hi I am sorry but I cannot find th...

How to avoid when linked source data is changed, chart turns blan.
When I created new charts in Excel 2003, each previous chart became blank. Therefor I saved the file unter a new name and copied the previous chart to the new file. This created ofcourse links to the other files. Now I want to change the source file, but when I do that the chart becomes blank as well. Anybody who had this problem before and found out how to solve it ? Rgds, Chris ...

chart type keeps changing
My chart is "embedded" in my worksheet. The worksheet has months across the top in one row and net income per share in a row below it for each corresponding month. The x-axis is pulling data for the month and year eg. Jan-04...Feb-06. The y-axis is pulling data from a different row for the net income for each month. The chart has been maintained since Jan-04. It is a standard clustered column w/ a 3-D visual effect. I am trying to enter data for March '06 in data source, series one. I am updating the series 1 with the new column address that represents the new month eg...