how do I get the if function to return a blank cell, not 0?

I am trying to create a chart from a series that contains data for each 
month.  The series is calculated on other worksheets and copied to the 
worksheet containing the chart.  I would like to have the cells for the 
months that have not been updated yet (now is January, there are 0' in all 
cells for Feb-Dec) to be blank (to create gaps in the chart) not 0's.  
Can this be done?
0
JoeCars (1)
1/18/2008 4:33:02 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
578 Views

Similar Articles

[PageSpeed] 32

Unfortunately, what you want, and what many of us have requested but doesn't 
exist, is a worksheet function like BLANK() or NULL(). The best we can do is 
use NA() in a chart's data source, which isn't interpreted as a zero by 
marker-type charts (XY, Line, and I think the line & marker style radar 
plots). So...

=IF(condition,value,NA())

You get an ugly #N/A error in the sheet, but it can be hidden with 
conditional formatting. See Debra's explanation:

http://contextures.com/xlCondFormat03.html#Errors

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


"JoeCars" <JoeCars@discussions.microsoft.com> wrote in message 
news:F246F2BD-DE8B-4050-9525-2320890097B7@microsoft.com...
>I am trying to create a chart from a series that contains data for each
> month.  The series is calculated on other worksheets and copied to the
> worksheet containing the chart.  I would like to have the cells for the
> months that have not been updated yet (now is January, there are 0' in all
> cells for Feb-Dec) to be blank (to create gaps in the chart) not 0's.
> Can this be done? 


0
jonxlmvpNO (4558)
1/18/2008 4:46:26 PM
What you need for the chart is not 0 or a blank but N/A
Let say you have =IF(A2>10,A2,"") Replace this by =IF(A2>0,A2,NA())
You will see #N/A in cells when the A value is <10. The chart engine will 
ignore these values
Don't like the appearance of you table? Use Conditional Format to hide the 
#N/A
best wishes
-- 
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"JoeCars" <JoeCars@discussions.microsoft.com> wrote in message 
news:F246F2BD-DE8B-4050-9525-2320890097B7@microsoft.com...
>I am trying to create a chart from a series that contains data for each
> month.  The series is calculated on other worksheets and copied to the
> worksheet containing the chart.  I would like to have the cells for the
> months that have not been updated yet (now is January, there are 0' in all
> cells for Feb-Dec) to be blank (to create gaps in the chart) not 0's.
> Can this be done? 


0
bliengme5824 (3040)
1/18/2008 4:51:07 PM
Reply:

Similar Artilces:

Excel Cell formulas
How would I make a cell show its results in another cell, without haveing to enter, any kind of formula into that cell that I wanted the results displayed? Open Excel Select Sheet1 Alt+F11 Copy: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Sheet1.Range("B1").Value = Sheet1.Range("A1").Value End Sub Past on the white sheet. Alt+F11 Write in A1 Enter -- ---------------------------- Mauro Gamberini "adame9176" <adame9176@discussions.microsoft.com> ha scritto nel messaggio news:039DB43A-46D3-4AED-8F88-EE072B894377@microsoft.com... >...

GP 9.0, Integration manager SP 3 Installation error
I am attempting to install SP3 against Integration manager from GP 9.0- I have made sure all windows updates are up to date. When installing I get the UAC request (I am on Windows Server 2008, standard). I know it gets to MsgBox.dll During the "copying files" stage and then I get "unexpected error; quitting" with an OK button. (it might get to a file beyond that- it's just the last one I could visually identify as when the error happens it clears what it was working on.) The install then jumps to updating registry keys. When it completes that it rolls back th...

Get rid of menubar..?
Hi all, How can I get rid of all menubars. I can remove them temporarily by 'Tools/Customize' and tick off all menubars but when I restart MyAccess.mdb one menubar is still there. What command should I use on Form_Open to eliminate all Menubars. I have tried with: Me.MenuBar = "" Kent J. Hi - Set the menubar property of the form to =1 (no quotes). You can do that in design view or by using VB code, i.e. Me.menubar = "=1" John Kent J wrote: >Hi all, > >How can I get rid of all menubars. >I can remove them temporarily by 'Tools/Customize&...

CRM Mobility 3.0 Wild Certificate and other
CRM Gurus, What Wild Certificate should be used for CRM 3.0 mobilie? What's the best way to test it? Does CRM open an SSL connection to sync with Outook with regards to CRM mobility? thnx, m Dear M., Not really sure what you mean with 'Wild Certificate'? When you read Appendix A of the mobile implementation guide, it explains how to create a certificate and how to enable your website to use SSL. If you need any help or clearification, let me know. -- Erik van Hoof CWR Mobility Check our weblog at: http://www.cwrmobility.com/weblog "Manish" <Manish@disc...

Adding a third part font to Windows CE6.0 R3
I'm would like to add a third party font to my current project or make it an options under Core Os/CEBASE/Fonts Add it to show up under Microsoft's fonts? Probably not as good an idea as you think. It is easy enough to add it to the Third Party folder and catalog items though. -- Bruce Eitman (eMVP) Senior Engineer Bruce.Eitman AT Eurotech DOT com My BLOG http://geekswithblogs.net/bruceeitman Eurotech Inc. www.Eurotech.com "TomRuss" <TomRuss@discussions.microsoft.com> wrote in message news:F23D4008-0D3D-4825-8F02-A53F29FE2FF1@microsoft.com.....

Outlook attachment function
This maybe a stupid question...Is there any way for me to set the attachment function to go to an assigned folder? So when ever I want to add an attachment it will always go to that same folder? You mean using the File, Insert menu? No.... but add the folder to the Places bar and you can quickly switch to it. http://www.poremsky.com/p/XPplaces_bar.htm -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook...

Make Excel view show active cell in top left with VBA?
I am using a vba macro to go to various ranges on a sheet. When the range is selected, I would like for Excel to align the top left cell of the range to be the top left cell in the view. So, whether I select cell A5 or cell A127, for instance, I want that cell to be at the top left corner of the current view. Thanks, Russ Application.Goto Reference:=ActiveCell, Scroll:=True Gord Dibben MS Excel MVP On Fri, 08 Jun 2007 21:10:19 -0700, Russ3Z <russ3z@yahoo.com> wrote: >I am using a vba macro to go to various ranges on a sheet. When the >range is selected, I would like for ...

Operator function across a range of cells
Is there any way that I can simply select a range of cells, and then divide the value of them all by two? I'm ideally after a way to do this without using VBA. Thanks JW Put 2 in an empty cell. Copy the cell. Select your range. Choose Edit/Paste Special, selecting the Values and Divide radio buttons. Click OK. Clear the cell in step 1. In article <MQYgb.1459$NN3.12152973@news-text.cableinet.net>, "Dublevay" <duble@blueNO--SPAMyonder.co.uk> wrote: > Is there any way that I can simply select a range of cells, and then divide > the value of them all by t...

Removing spaces from middle of cell
In my spreadsheet there is a column that contains number, 3 on the left, space, 3 on the right. I want to remove that space. Rather than doing this in three steps, taking left, then taking right, then joining those together, is there an easier way to accomplish the same thing. It currently looks like this 216 123 I want it to be this 216123 I'm sure there is an easier way, but I only know how in three steps. Thanks! Hi, One way is to use the formula below in a helper column and then copy the helper column and paste special|Values back over the original data. you ca...

Custom Buttons/Functions in RMS
Hi Folks, Quick question for y'all with more experience with RMS than I! I'm currently to some extent integrating RMS with our existing eCommerce website. I've got the website updating stock levels on RMS when it sells something, but I'm unsure asto how to go about things the other way around. Essentially when the Till tenders I would like it to update the stock table on my website and knock the stock levels down accordingly. Now I could quite happily sit and write the code to do this myself in VBscript or whatever, but I can't quite find whereabouts in RMS I would ...

how do i get value from.............
how do i get value from xml file to checkbox1.checked ( 1 or 0) . i stored value to xml: rw.WriteConfigInfo("UserModes", "InvisibleOnServer", CStr(chkInvisible.CheckState), "PirateChat.xml") get value from xml and store in checkbox1.checked . but i couldn't get value from xml: 'load user modes Dim szInvisible As Collection = clsReader.GetConfigInfo("PirateChat", "InvisibleOnServer", " ", "PirateChat.xml") Dim getInvisible As String For Each getInvisible In szInvisible ...

Formula for
I'd like to write a formula in column B to find the difference between a value in the current row, say, A500, and the prior cell in column A that has a value.. could be A499, or it might be A200, or A1. Example: First column is literals, the second column s/b a formula, =A3 - A[prior row with data] 2 5 3 6 1 4 -2 9 5 This may not be very efficient if you 1000's of rows of data. Assume your data is in the range A1:An Enter this formula in B2: =IF(OR(COUNT(A$1:A2)<2,A2=""),"",A2-LOOKUP(1E100,A$1:A1)) Copy down as need...

How do I get the names of the sender in my sent box without click.
In Outlook Today, I added the column "to" in my sent boxes. The names of the senders however are not placed there, unless I click the message. Is it possible to fill in the names of the senders automatically?. ...

Error exporting to excel CRM 3.0
when i try to export any data to excel worksheet on a computer with Server 2003 and Office XP, excel opens but just after that i get erorr that specified file cannot be opened, on any other it works fine, could it be because of Office XP? does Server 2003 requires Office 2003 to work properly with such a matter? ...

How to put a SQL query in a single Excel cell?
I'm creating a spreadsheet that lists different queries. When we list the odd SQL query, we want to put the entire query in a single Excel cell that can be copied and pasted in SQL Query Analyzer. Is there a way to copy and paste the SQL query into the cell? We're finding that when pasting, Excel breaks it up into several lines. Additionally, typing it out doesn't always properly format the query (like when there are comments within the query)... Is what I'm looking to do even possible? Kris - you'll find that Excel cells only hold 255 characters which seems li...

GP 7.5 to 8.0 Upgrade: Problem with Extended Pricing Upgrade
I just performed an upgrade for a customer running Great Plains Professional with Extended pricing (7.5 to 8.0). After the upgrade, when entering an SOP Document, I would recieve a pricing error indicating that no price could be obtained for the item. This affected all items and all customers. To attempt to fix the problem, I ran check links on the Multicurrency Setup file. This produced the following error: Several Price Sheets exist with no functional currency. These price sheets have been removed. Upon further review, I realized that all of my price sheet data (header and detail...

change the format of cell
I have uploaded an excell spreadsheet and seem that all cells are text formatted. I can`t change to fromat of cell to number. "Format cell" dosen`t working or change anything. On Tue, 27 Oct 2009 07:13:01 -0700, Exceller <Exceller@discussions.microsoft.com> wrote: >I have uploaded an excell spreadsheet and seem that all cells are text >formatted. >I can`t change to fromat of cell to number. >"Format cell" dosen`t working or change anything. Changing the format will not do that. You need to coerce these values to numbers by performing a math operation...

multiple if/then on one cell
here is basically what i want to do. if a3=b3 than x if not than check to see if a3=b4 than y if not than check to see if a3=b5 than z i just don't know how to make excel understand. if there is a different command to use that would be cool too .. i just don't know it. thanks in advance. Try this: =IF(A3=B3,"x",IF(A3=B4,"y",IF(A3=B5,"z"))) "cheshire191" wrote: > here is basically what i want to do. > > if a3=b3 than x > if not than check to see if a3=b4 than y > if not than check to see if a3=b5 than z > > i just ...

Named Cell Ranges #2
I have a rather large table that lists different categories and many tasks for each category. I then named a separate sheet for each category and listed the tasks. Each of the tasks are separated out into individual tables that list the steps to perform each task. In order to save typing time (and to make sure the tasks read the exact same way), I set the table headings to equal the corresponding tasks on the original table. I've also named each table in the attempt to "group" the cells together. If I have to move one of the tasks around on the sheet #1, I want the ste...

Excel File search function
Can anyone suggest how to search for a numeric value randomly across a specific network drive to come up with the file it resides in ? I have two laptops with office 2003 . One will search a numeric value and find on specified drive , other will not. Both identicle versions and search preferences set the same in excel's file search under file on tool bar. I compared side by side. They are however running on a company windows Xp Pro version image but excel has not been modified and should be stock. Function was working great and just stopped recently, these systems receive HP...

migrate xls app 2003 to 2007 -- get custom macro button out of des
I created an Excel 2003 app with tons of VBA and custom macro buttons that lie within the sheets. One user at my place has Excel 2007. I went to the trust center and enabled macros and trusting VBA object models... When I place the mouse over a custom macro button and then try to click on the button all it does is to select the button in design mode. How do I get out of the design mode and get to the mode where I can run the macro when I click on the button? Thanks, Rich Developer Tab>Design Mode can be toggled on/off. Assumes button is from Control Toolbox If yo...

Nested SUMIF function
Hi! Is it possible for me to use SUMIF function in nested form? Sumif(Sumif(Sumif))) Me If you need a multiple criteria Sumif then use an array formlua as described at http://www.cpearson.com/excel/array.htm HTH. Best wishes Harald "A P" <ap@textguru.ph> skrev i melding news:O3UpvUwHFHA.588@TK2MSFTNGP15.phx.gbl... > Hi! > > Is it possible for me to use SUMIF function in nested form? > > Sumif(Sumif(Sumif))) > > Me > > ...

Getting File Open Error !!!
Hi, Excuse me in advance for asking may be a dumb question as I am very new to Money. I have installed Microsoft Money 2003 in my home computer. I was working on a money file and I copied the mony file to work on my office machine. There I installed Money 2003 Trial version. When I try to open the file saved on my office machine at home it gives this error... "You may have made a typing error or typed the password for a different file." Any help would be greatly appreciated... SH ...

anyone resolved this: cannot login after upgrade to 3.0 on win2K3 server ( "contact your systems admin..")
I quote this because it sounds like what happened to us today: "Upgrade install of CRM 3.0 on Windows 2K3 box. I can login as the system administrator, but can't login as any other user. I have "enabled the other users, but I get an error each time I login. The only thing it says is: ERROR. An Error has occoured. Contact your systems administrator for more information..... then I have a Try Again or Close button." Win2K3 Server 10 users active and having license on 1.2 upgrade successfully terminated the administrator can login (with restricted access box checked) ...

Excel 2000 help please
I have Excel 2003 and have created a spreadsheet for someone using Excel 2000. In xl2003 within Tools/Protection there are tick box options for Select locked cells, Select Unlocked cells etc.etc. Was this something introduced in xl2003 or was it still available in xl2000? I have set protection and ticked unlocked cells only. The person I sent the spreadsheet to said he could access all cells even though he couldn't enter information in the proteced cells. I don't have access to xl2000 Within worksheet protection was "selecting unlocked cells only" avai...