Custom Formatting a Chart Data Label

I want to create a chart which will display a zero value on the data label 
where there is in fact a zero value, and show N/A on the data label where it 
didn't apply. The formula I am using in the spreadsheet uses a nested formula 
to make this determination:
=IF(AND(B22+C22>0,C22>0),B22/C22,NA())
Presently, it works but displays the #N/A on the chart where I would prefer 
it simply displayed as N/A. Can I do this with a custom format on the data 
label??

Thanks so much! I don't know how you all do it but your amazing!


-- 
If you can read this, thank a Teacher...
If your reading it in English, thank a Veteran!
0
8/7/2008 1:29:01 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
424 Views

Similar Articles

[PageSpeed] 48

If your formula is being used for both the y-values AND the label, then you 
will have to live with #N/A
If you are using a chart label utility (see below) then you could use this 
for the label formula
=IF(AND(B22+C22>0,C22>0),B22/C22,"N/A")

Also it seems to me (after a big lunch and two glasses of wine) that you 
could begin the formula
with =IF(B22*C22>0,
unless you are avoiding the case when both B22 and C22 are negative.

Label utilities
Rob Bovey's Chart Labeller, http://appspro.com
 John Walkenbach's Chart Tools, http://j-walk.com
Tushar's Hover Chart Label utility:
  http://tushar-mehta.com/excel/software/chart_hover_label/index.html

best wishes
-- 
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Telobamipada" <Telobamipada@discussions.microsoft.com> wrote in message 
news:8910DC69-30B0-440C-B3C4-304E4D1B077B@microsoft.com...
>I want to create a chart which will display a zero value on the data label
> where there is in fact a zero value, and show N/A on the data label where 
> it
> didn't apply. The formula I am using in the spreadsheet uses a nested 
> formula
> to make this determination:
> =IF(AND(B22+C22>0,C22>0),B22/C22,NA())
> Presently, it works but displays the #N/A on the chart where I would 
> prefer
> it simply displayed as N/A. Can I do this with a custom format on the data
> label??
>
> Thanks so much! I don't know how you all do it but your amazing!
>
>
> -- 
> If you can read this, thank a Teacher...
> If your reading it in English, thank a Veteran! 


0
bliengme5824 (3040)
8/7/2008 5:19:36 PM
Reply:

Similar Artilces:

How can I set the format of a control in code
For example I am try to set the format of a comboBox that can take many values but I want to be able to set the format for each value. If Me.cmbTrackerFields.Value = "Denied" Then Me.Form.Filter = "[Invoice Tracker].[" & Me.cmbTrackerFields.Value & "]='" & Me.cmbFieldValues.Value & "'" Me.Form.FilterOn = True I want to be able to set the format for Me.cmbFieldValues.Value to "Yes/No". I tried this Me.cmbFieldValues.Format = "Yes/No" but that didn't work. Any ideas? Thanks. >Me....

chart with %
How I create automatically chart similar to PIE but "columns with % " without create a new table with % numbers ? thanks Marina ...

How do I sort data in a spreadsheet
I have a spreadsheet that has a number assigned to each name and it was made in aphabetical order. I want to change it to numerical order. How do I do this? You can't in Publisher. You can try pasting the data into Excel, sorting it and then paste it back into Publisher. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "stmary" <stmary@discussions.microsoft.com> wrote in message news:92E24369-2E8E-4B02-899D-95A4E389D6D2@microsoft.com... >I have a spreadsheet that has a number assigned to each name and i...

How pull data always from same location?
I want worksheet AA to pull data from a set location of worksheet BB. Worksheet BB is edited by a user who may add, delete and move the rows. If I make 'AA'!A10 set to ='BB'!A10 that works fine until my user moves the data on BB to row 50. At that point my 'AA'!A10 shows a value of ='BB'!A50. How do I set things up such that even after my user moves data to row 50 my 'AA'!A10 continues to pull from ='BB'!A10? Thanks =INDIRECT("'BB'!A10") HTH, Bernie MS Excel MVP "xrbbaker" <xrbbaker@discussions.microsof...

Multiple Conditional Formatting
I have an annual report spreadsheet comprised of 15 rows (1 - 15) and 6 columns (A - F). Column headings are "A = Name", "B = Date", "C = Wage", "D = Raise", "E = % Recent", "F = % Total In columns "A", "B" and "D" I manually add names or numbers as needed. Columns "C" "E" and "F" are formulas based on values in "B" and "D". The names in "A" remain constant, never requiring change. Rows are filled in starting with dates in "B", ...

Barcode Labeling Individual Items
If I buy a separate barcode/labeler/printer device, can I customize POS or RMS to print a barcode label for each individual item sold. I have a repair shop, and I want to barcode each item that comes in (even though there may be 5 'items' per transaction)? How about using Serial Numbers? If you are using a Zebra, Cognitive or other label printer with a text based programing language, you can add a barcode for the serial number. If you use a windows driver based printer and label, I don't believe that you can add a barcode for anything other than the LookupCode. The al...

Removal of broken Hyperlinks from data copiedfrom a web page in to an Excel Spreadsheet
I have copied over 600 + lines into excel from a web page data base. I have cells which appear as hyperlinks (blue underlined text). When I put the insertion point over the cell, I get the little "hand" and a hyperlink box opens but is blank. I do not want the text in the cells to be hyperlinks nor do I want to open any hyperlinks. I went to the edit menu to select links to remove all but links is grayed out. I can remove each one, one by one, by using the arrow key to move into the cell, doing a right click and select, remove hyperlink. This works, but will not accept multiple...

How to automate custom footer on all tabs
When I receive files from others, they typically do not have a footer w/ basic information like date and file name. I want this on all my files and further want to customize the font size and usually want the path name of where I store file in the custom footer. When I receive a mutli tab files, I am having to manually 'setup- custom footer- add info- change font...' to each tab. I want to apply the settings to all tabs- or even better- just automate the whole process- to not even have to go into setup, custom footer dialog. Just apply some saved custom footer I have to all tabs...

Loading data from Excel to Oracle
Hi All, I would like to load an Excel spreadsheet directly into a single table in Oracle. Both the spreadsheet and the table have the same ordering of columns and have compatable data. I am aware that a way to achieve this would be to convert the .xls file into a .csv file and then use sql loader. However, I am interested in doing this in a quicker/easier way directly from Excel, eg. by adding in some functionality and clicking on a new button for loading into the database. We are all able to read from a database in Excel by importing external data. Surely there is a way to change that...

How can I "undo" an entry on a form/subform while still entering data ? 11-18-07
Hi I posted this question a couple of days ago and got 1 response but couldn't read it. Hopefully someone will have and answer. I have a form/subform for entering invoice data. What can I do if I realize that I have already entered the invoice ? Example: I enter the main form data and enter 5 lines of detail on the subform and then I realize that I have already entered this invoice before ? I would love to have a button that I could click that "undoes" the entire entry. How can this be accomplished ? Thanks Mark <mthornblad@gmail.com> wrote in message news:81271...

Custom Error Bars in Excel 2007
When choosing the option to add a custom error bar in Exel 2007 and trying to set the range to a worksheet that does not contain the chart I receive an error in Excel that closes down Excel. The steps I perform are: 1. Click on a bar in my chart 2. Click Layout 3. Click the drop down for error bars 4. Click more error bar options... 5. Choose custom and click specify value. 6. Click to set the range where this is then displayed ={1}. As soon as I change to the worksheet to set the range Excel errors out and shuts down. I have recreated this on more than one machine and the result is t...

How do I add items to a custom pick list?
I added a custom field to CRM and specified a datatype of "PickList." This gives me a drop down box with a single item of value "default". How do I add other items to the list? NEVERMIND I found it. Under "properties" in custom design you hardcode the list. "Dave F" <dave@nospam.com> wrote in message news:uRBOlwAcEHA.2544@TK2MSFTNGP10.phx.gbl... > I added a custom field to CRM and specified a datatype of "PickList." > > This gives me a drop down box with a single item of value "default". > > How do I add...

CRM Customization 12-17-03
I have just started evaluating Microsoft CRM for our business, but one critical need is probably going to require some customization to interact with our existing home grown CRM database that handles ecommerce and tracks customer software downloads. We are .NET developers ourselves, so that shouldn't be a problem. However before I dig too deep, I'd like to know if the following is possible in MS CRM 1.2: 1) Can data be synchronized with with another SQL database? For example could we integrate the sales data in our eccommerce database with MSCRM? It would be nice if we could sched...

How do i convert dates to text but keep the date format
hi, have a series of dates but everytime i try converting them to text a number comes up. How can I convert my dates to text but keep the date format. Please Help In a helper column................ =TEXT(A1,"mmmm-dd-yyyy") You choose the format within the double quotes Copy and paste special(in place)>values>ok>esc Delete column A Or copy and paste special(onto column A) Gord Dibben MS Excel MVP On Tue, 13 Apr 2010 13:21:01 -0700, SallyP <SallyP@discussions.microsoft.com> wrote: >hi, have a series of dates but everytime i try conv...

point of intersection of two lines on line chart
Hello, is there a way to locate the exact point of intersection of two lines on line chart?? Dave - Andy Pope has a helpful web page: http://andypope.info/charts/intersection.htm - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Dave wrote: > Hello, is there a way to locate the exact point of intersection of two lines on line chart?? ...

Analysing data through pivot
Hi, I am trying to convert the payroll data I receive from my clients into a csv file that I import in to my payroll program. Ideally I would like it to be all automatic (marco). The problem lies in the variable nature of the information I receive. Here is the basic layout: NAME | ID | Basic pay | Commission | Overtime Bob 2 500 300 50 James 3 400 20 Sarah 4 500 And so forth THe layout I am trying to achieve is the following: Bob 2 Bas...

Custom fields not showing up
Hi, Any changes to the current screen layout for both Accounts and Contacts is not showing up to any users, even after resetting IIS. Any ideas? Thanks, Soenke did you publish them using the deployment manager tool? -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "Zoonk" <soeNOSPAMnke_weiss@hotmail.com> wrote in message news:O6P5daHIFHA.2752@TK2MSFTNGP12.phx.gbl... > Hi, > > Any changes to the current screen layout for both Accounts and Contacts is > not showing up to any users, even after resetting IIS. > > Any ideas? > > Tha...

Dynamic bar chart, must ignore "n/a" data...
Okay, gang, here's what I am trying to do. Assume I cannot use macros or pivot tables (the end chart must be usable by folks with ZERO excel ability.) Formulas only. (I know this would be easy with pivot tables.) Assume I have a table that is calculating how many pieces of fruit I pick. I have a column (A) that is called FRUIT. The user of the spreadsheet picks the type of fruit from a validated drop down list in (A) and then enters the number of pieces picked in (B). so the data might look like this: APPLE 2 APPLE 3 PEAR 2 PEACH 2 APPLE 4 Where each row is a different day...

Installing corporate custom forms?
Hi All Is there a procedure for installing pre-built custom corporate .oft files on an Exchange 2000 system? Thanks, Jack B. ...

How to use XY Chart Labeler
As some of you have recommended here, I downloaded and installed the XY Chart labeler from AppsPro (http://www.appspro.com/). There does not seem to be a manual for it, so how do I get started using it? Thanks. -- ================================================= Do you like wine? Do you live in South Florida? Visit the MIAMI WINE TASTERS group at http://groups.yahoo.com/group/miamiWINE ================================================= To install the add-in, double-click on the XYChartLabeler.exe that you downloaded. Then, in Excel, choose Tools>Add-ins If XY Chart Labeler isn'...

importing customer summary data
Hello: This GP 10.0 client is not going to import customer historical data. But, they want the Customer Summary data to be updated and accurate. Is it safe to import this data through Table Import? If not, is it possible to do so through Integration Manager? Thanks! childofthe1980s It's possible todo through Table Import. There is no standard adapter in IM for customer summary data. If you ever run reconcile, you could have a problem because you won't have the transactions to support the summary data. -- Charles Allen, MVP "childofthe1980s" wrote: > Hell...

Problems with formating and formulas in Excel 2002
I am having problems with the sum formula in Excel 2002. I take a straight copy of data in an acess database (not an export) and paste it into an excel spreadsheet. I can not seem to format the cells nor can I use the Sum formula to calculate. If I manually re-type all the numeric value these problems seem to disappear. The problems seem to have started when Access XP was installed onto the PC but niether removing Access no re- installing the PC has worked to correct this problem. Has anyone else seen something similar? Hi It sounds to me like the cells are coming in as text, rathe...

How to call function of Dialog box from custom tree control class?
Hi to all, I am creating a dialog based apllication in MFC. In which I have created my own Tree class derived from CTreeCtrl which shows all the image files and folders(.bmp,.jpg etc). Now my problem is when user selects the file from tree ctrl that image should be displayed in the dialog box. I tried to call the function of dialog box's ShowImages(CString strPath) from tree controls OnSelectionChanged event handler function but it fails for assertion at the time of CClientDC dc(this); this function. Tree is in the same dialog where i want show the Images. P...

Automatic removal of data labels with 0 value in a chart
Hi, I have some charts that I want to remove only the data labels which value = 0. I know how to do this manually,I am only interested in finding out how to display data labels in a chart whose values are equal to 0. If the only way to do this is by using BVA code, would you please send me some code. Is there a toggle swith in the Excel Options just like the one that currently exists not to display 0 in a work sheet? I have about 100 columns in seven different charts that I need to edit manually every week and it is very time consuming. Thanks. You can use a simple custom num...

Excel charts #12
I am trying to format the individual labels on the category axis on an Excel chart. I want every other label in a different color and bold. But the entire category axis is formatted. Does anyone have a suggestion? The category axis is years - 1990 through 2005. I want the odd years to be bold and a different color than the even years. When Excel doesn't let me do what I want with a chart axis, I draw my own: http://peltiertech.com/Excel/Charts/ArbitraryAxis.html Use this technique to make two different axes, one for even and one for odd, and hide the default axis Excel draw...