Handling blank data points

I have a chart which is has "" in a formula to clear contents when not 
applicable to show error.  This results in the chart treating the cell as 0 
and therefore ugly result in data point.

All other post responses to this type of question suggest using NA() and 
conditional formation to hide the error.. Unfortunately when this is used 
this screws up my SUM() and AVG() formulas..

Any other suggestions?

Thanks
Jo
0
jo2109 (2)
3/6/2009 1:16:09 AM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
783 Views

Similar Articles

[PageSpeed] 19

Hi Jo,

I don't know if this is the best way but what I have done under similar 
circumstances is use the #N/A for the chart series data column and then I use 
a helper column. In the helper column I insert the following formula 
(Assuming that column B is the chart series data and the following formula is 
in any other column.
=IF(ISERROR(B2),0,B2)

That way I have a column that includes the zeros for the math functions.

You can still place the sum and average formulas at the bottom (or wherever) 
of column B but they actually sum and average the data in the helper column.

Hide the helper column if desired. You can even place the helper column on 
another worksheet and hide the worksheet.

 
-- 
Regards,

OssieMac


"jo2109" wrote:

> I have a chart which is has "" in a formula to clear contents when not 
> applicable to show error.  This results in the chart treating the cell as 0 
> and therefore ugly result in data point.
> 
> All other post responses to this type of question suggest using NA() and 
> conditional formation to hide the error.. Unfortunately when this is used 
> this screws up my SUM() and AVG() formulas..
> 
> Any other suggestions?
> 
> Thanks
> Jo
0
OssieMac (238)
3/6/2009 11:42:11 AM
You probably want =IF(ISERROR(B2),"",B2) [or more specifically 
=IF(ISNA(B2),"",B2) ] rather than =IF(ISERROR(B2),0,B2), as the zeroes could 
screw up your averages.
--
David Biddulph

"OssieMac" <OssieMac@discussions.microsoft.com> wrote in message 
news:8BA6FCA9-10D1-4AFA-A5C8-AFDCFCD307ED@microsoft.com...
> Hi Jo,
>
> I don't know if this is the best way but what I have done under similar
> circumstances is use the #N/A for the chart series data column and then I 
> use
> a helper column. In the helper column I insert the following formula
> (Assuming that column B is the chart series data and the following formula 
> is
> in any other column.
> =IF(ISERROR(B2),0,B2)
>
> That way I have a column that includes the zeros for the math functions.
>
> You can still place the sum and average formulas at the bottom (or 
> wherever)
> of column B but they actually sum and average the data in the helper 
> column.
>
> Hide the helper column if desired. You can even place the helper column on
> another worksheet and hide the worksheet.
>
>
> -- 
> Regards,
>
> OssieMac
>
>
> "jo2109" wrote:
>
>> I have a chart which is has "" in a formula to clear contents when not
>> applicable to show error.  This results in the chart treating the cell as 
>> 0
>> and therefore ugly result in data point.
>>
>> All other post responses to this type of question suggest using NA() and
>> conditional formation to hide the error.. Unfortunately when this is used
>> this screws up my SUM() and AVG() formulas..
>>
>> Any other suggestions?
>>
>> Thanks
>> Jo 


0
David
3/6/2009 12:25:35 PM
Thanks for the tip but unfortunately the "" is not just in one cell but is 
part of a formula for many cells and may be the outcome for a number of data 
points.  Your suggestion simply leads me to believe that to alleviate the 
issue I need to totally replicate my numbers for to pick up the 'pretty' 
number for one part and the data series to plot as another..

I'm hoping not to replicate the whole section simply to have the graph not 
plot null data.

"David Biddulph" wrote:

> You probably want =IF(ISERROR(B2),"",B2) [or more specifically 
> =IF(ISNA(B2),"",B2) ] rather than =IF(ISERROR(B2),0,B2), as the zeroes could 
> screw up your averages.
> --
> David Biddulph
> 
> "OssieMac" <OssieMac@discussions.microsoft.com> wrote in message 
> news:8BA6FCA9-10D1-4AFA-A5C8-AFDCFCD307ED@microsoft.com...
> > Hi Jo,
> >
> > I don't know if this is the best way but what I have done under similar
> > circumstances is use the #N/A for the chart series data column and then I 
> > use
> > a helper column. In the helper column I insert the following formula
> > (Assuming that column B is the chart series data and the following formula 
> > is
> > in any other column.
> > =IF(ISERROR(B2),0,B2)
> >
> > That way I have a column that includes the zeros for the math functions.
> >
> > You can still place the sum and average formulas at the bottom (or 
> > wherever)
> > of column B but they actually sum and average the data in the helper 
> > column.
> >
> > Hide the helper column if desired. You can even place the helper column on
> > another worksheet and hide the worksheet.
> >
> >
> > -- 
> > Regards,
> >
> > OssieMac
> >
> >
> > "jo2109" wrote:
> >
> >> I have a chart which is has "" in a formula to clear contents when not
> >> applicable to show error.  This results in the chart treating the cell as 
> >> 0
> >> and therefore ugly result in data point.
> >>
> >> All other post responses to this type of question suggest using NA() and
> >> conditional formation to hide the error.. Unfortunately when this is used
> >> this screws up my SUM() and AVG() formulas..
> >>
> >> Any other suggestions?
> >>
> >> Thanks
> >> Jo 
> 
> 
> 
0
jo2109 (2)
3/7/2009 9:01:00 AM
Reply:

Similar Artilces:

Data transfer from Money 2000 Professional , italian version.
I have been using Money 2000 Professional,italian version, for many years. I recently bought Money Small Business 2005, but I can't open the previous Money 2000 files. I get a message that the old file has been made with an incompatible version. The operating system is Windows 2000 Professional, italian version. What can I do? -- Sandra In microsoft.public.money, Sandra wrote: >I have been using Money 2000 Professional,italian version, for many years. >I recently bought Money Small Business 2005, but I can't open the previous >Money 2000 files. >I get a message that th...

excel data base
hi there. i need some help please guys. i have exported some names & telephone numbers from a software packag (sms centre) & im trying 2import them in2 a package called e-txt. thes packages reads csv files. ive encountered a problem my exported files r saved in excel lik this... name country code area code phone number joe bloggs 44 07977 797898 but the e-txt database fields r as follows 1st name surname phone number joe bloggs 447977797898 how do i con...

How do I mark data points in an Excel chart as significant?
I have Excel 2003 and want to mark specific data points on a line chart as statistically significant with some kind of symbol (*). I cannot for the life of me figure out how to do this, or maybe it is not possible. Thanks for the help in advance. Chris - You can split the data into two series, and use a different symbol for significant points. This web page has instructions: http://peltiertech.com/Excel/Charts/ConditionalChart1.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Chris Russe...

Exchange 2K to 2K3 migration
I'd like to get some advice on the best approach to handle the STM file during an Inter-Org mailbox move. The Exchange migration wizard will handle the the mailbox but how do you handle the content in the STM file? Thanks. You don't need to worry about it. Mailbox data is stored in both the EDB and STM files. Typically, message content from the Internet will be in the STM file. I'd suspect that when you migrate the mailboxes, all data will be converted to native MAPI format (moved to EDB file) and will then be moved to the new mailbox. -- Ben Winzenz Exchange MVP Me...

Counting data in columns
ok ive struggled with this all morning and i cant do it i have 2 columns A and B A can contain any of the following RTB, In Progress, Live B contains a Date but can also be empty I want to count column B (dates) where Column A is equal to RTB but i have no idea how to do this Example of Spreadsheet A B RTB 01/01/2003 Live 01/02/2003 RTB 01/02/2003 RTB 01/12/2003 Live 02/12/2003 RTB RTB 01/01/2003 Live 01/10/2003 Total RTB count ...

Do Not Want to count blank Cells
I copy pasted an Access dynaset (e.g., results of a crosstab query) into an Excel spreadsheet. I tried to use the function =counta(a4:z4) to count the number of entries (nonblank) in row 4. But the result is giving me 26 because it is counting blank cells. Is there a way to count the entries without counting the blanks or a way to easily reformat or change the blank cells so the formula will not count the blanks. I do not want to have to manually delete every blank cell in a table 26 columns by 2000 rows. Thank you, Steve CountA does what you want. It seems that you must have s...

Handle to an ActiveX control
Hi... What if I use GetModuleHandle(L"abc.ocx") in the InitInstance() of the of the App class derived from 'COleControlModule'...of the ActiveX control "abc.ocx" ? Will I get the Handle or it will return NULL....? I have encountered controls, in some of them..handle is found and in others its NULL... Wat is the reason behind this behavior..? Can any one explain? "Abby++" <asthana.abhinav@gmail.com> wrote in message news:1174308443.062622.51500@b75g2000hsg.googlegroups.com... > What if I use GetModuleHandle(L"abc.ocx") in the InitI...

changing settings: comma into point
hi there! currently, my excel is set to have a comma as a delimiter. a is want to copy-paste point-delimited data into excel, i have t change the excel-settings i guess? does anybody know where i can fin this option? denni ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Hi Dennis, > hi there! currently, my excel is set to have a comma as a delimiter. as > is want to copy-paste point-delimited data into excel, i have to > change the excel-settings i guess? doe...

Automatic data collection in Manufacturing
How does this work? I see where you can designate a workstation as an ADC processor, but where do you setup to let your bar code scanner enter information. What exactly is the setup on this? Anyone? "Dnelson" wrote: > How does this work? I see where you can designate a workstation as an ADC > processor, but where do you setup to let your bar code scanner enter > information. What exactly is the setup on this? If you figure the answer, do post. I will be interested in knowing this too. Scott "Dnelson" wrote: > How does this work? I see where you ca...

Extra (blank) browser window
Whenever I click on a link in an Outlook e-mail two Internet Explorer windows pop up. The first one is completely blank. The second one is the page I selected. This doesn't happen if I click on a shortcut or favorite. It started happening a few weeks ago. As I've tried to reconstruct the chain of events in my mind (which may be faulty), I started getting error messages when clicking on links from Outlook. A dialog popped up saying it could not find a file, and IE would not come up. Thinking Outlook had forgotten where the web iexplore.exe was, I pointed it to that lo...

multiple data
I need a chart that has Collections Charges Units For 3 years Broken out by payor mix. I have tried to do the stacked chart thinking y axis would have dollars, x axis would have years 1-3 and then for each year I would have 3 bars (each stacked by payor) for collections, charges and units. I can get this to run but only for one year. I cannot get 3 bars per year. I am certain it is something to do with the way I have highlighting the data range and or the series. We have worked all weekend getting the data and now cannot determine how to run chart. Please help! ...

Merging excel data into word #2
Ok, I have a feeling this process is quite simple, but I haven't bee able to figure it out on my own, nor find anything in FAQ's. I have created an excel spreadsheet containing the following: students' last name, first name, ID number, advisor and counselo (obvioulsy Im in education) I would now like to create a phone call log for each student, but woul like to import each students' info into this word document. So as print the 100 or so pages, page one would have the first students info page two would contain the second students info, and so on. Any help is greatly appre...

user form that retrieves data from Microsoft Access query
I created a word document that displays a userform when the template is opened, when you click on the ok button on the userform the template should display data that comes from a microsoft access query. The thing is that this works perfectly for me with my non-administrators account. The problem is that whenever another user is trying to open this template no data is being displayed. I checked the permissions on the folder where the template is located and that looks fine. Anyone has any idea what I might be doing wrong here? Thanks in advance. Where is the database, ...

blank To: field in Sent items
We recently migrated from Notes to Exchange and some of my users are complaining that some messages that they forward don't have an address in the To: field in Sent Items. We are using Outlook 2003. Any ideas? Same problem here. "Sander" <noemail@noemail.com> wrote in message news:<#7SnUBm0EHA.1564@TK2MSFTNGP09.phx.gbl>... > We recently migrated from Notes to Exchange and some of my users are > complaining that some messages that they forward don't have an address in > the To: field in Sent Items. We are using Outlook 2003. Any ideas? So are we...

Data Validation drop down not showing and Formula Auditing bar greyed out
Good afternoon, I've created a spreadsheet that has multiple Validated cells. Each of these cells is validated using a list, with "In-cell Dropdown" Checked. However the drop down is not showing. The cell is still being validated as I cannot enter a value other than what is in the list. Also, on the formula auditing bar, the Trace Precedents, Remove Precedents, Trace Dependants, Remove Dependants, Remove all arrows and Trace Error buttons are all greyed out. I've checked that the sheets and workbook are not protected and as far as I can tell it has happened between file ...

invalid handle
hi frds i m very new in vc++ and i m founding too much difficulty in this so plz can any one solve this problem .... actully i m inserting a image in list box using this code ........ everything is returning write thing still it is saying invalid handle after ImageList_Add(hList,m_hBmpNew,0); when i m going to dibug it plz help me BOOL Fun() { // Create 256 color image lists HIMAGELIST hList = ImageList_Create(32,32, ILC_COLOR8 , 8, 1); HBITMAP m_hBmpNew = (HBITMAP) LoadImage( AfxGetInstanceHandle(), // handle to instance "c:\\img.bmp", /...

Blank emails with no header?
Hi All, I'm randomly getting a few blank emails a day in my inbox with sizes of a couple hundred bytes. When I open them, I see nothing. If I save them to a file I get a zero length file (even though outlook shows the mail to be a few hundred bytes). Neither Norton nor NOD32 showed a virus (Outlook integration). The only header info is that the x-envelope is to me. There is no from, subject, or the usual info about the other mail servers mail goes through. Any ideas? Thanks, -=Gina Gina <G@no.mail> wrote: > I'm randomly getting a few blank emails a day in my inbox...

Thunking a 32-bit HANDLE to a 64-bit HANDLE
Currently I am converting a 32-bit WDM driver to a 64-bit KMDF driver that will continue to work with our 32-bit DLL and our customer's 32-bit applications. The sample code for thunking 32-bit items shows the following Buffer->Handle = (HANDLE)Buffer32->Handle; see: http://msdn.microsoft.com/en-us/library/aa489604.aspx Buffer32->Handle is declared as UINT32 Handle The driver compiler issues error number C4312 for this cast. The code that I have adopted (to get it to compile) is: handlerInputs.hEvent = (HANDLE) (ULONG_PTR) p_handlerInputs3...

merged cells data
how to link text or data from a merged cell into another sheet or workbook? Stephen, Use something like =Sheet1!B2 where B2 is the upper left cell address of the merged cells. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Stephen" <kslance@pacbell.net> wrote in message news:D7A8A2FC-B7D9-468D-BADE-2C0C77EFE125@microsoft.com... > how to link text or data from a merged cell into another sheet or workbook? ...

Daily Bank Sweep
New GP Client that is reconciling for the first time their operating account. We have entered the last reconciled balance and dates as of 12/31/08. Operating account gets swept every night and redeposited the next day. With each sweep interest is calculated. My question is how to handle the last sweep of the month, which is a sweep in transit. We have tried entering as a decrease adjustment (not posting to GL as it should not effect the GL balance). We have also tried entering as an adjustment to see what the effect is. We don't even come close to the bank ending balance. Pl...

Comparing data in two workbooks
Old price book in Workbook A. New price book in Workbook B. How do I find, and return to a new workbook, the part numbers that are in workbook B but not A. Thanks in advance. -- Erwin Chip at www.cpearson.com has said it all on this topic -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Erwin" <erwin31@nospam.net> wrote in message news:OYbIuxZKFHA.1476@TK2MSFTNGP09.phx.gbl... > Old price book in Workbook A. > > New price book in Workbook B. > > How do I find, and return to a new workbook, the part numbers that are in > work...

CRM Contact to Outlook Contact Data Synchronization Mappings
Is it possible to modify the CRM Contact to the Outlook Contact Data Synchronization Mappings in version 4.x? We have a client who would like to map Address 2 on the CRM Contact to the Home Address fields on the Outlook Contact. If it is possible, does anyone know the specific settings to modify for this? Yes only through CRM SDK and outlook offline plugins you can achieve that. for more see crm sdk. let me know if you need any help -- Regards, Imran MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.c...

calculate data from a different file
Is it possible to have a cell return a value from another file that is saved on a server without that file being opened. Here is the formula I am using: =COUNTIF(OFFSET('P:\Manpower\[Copy of Manpower 10-25 to 12-30.xls]Daily MP'!$A:$A,0,MATCH(F$3,'P:\Ohio\Manpower\[Copy of Manpower 10-25 to 12-30.xls]Daily MP'!$5:$5,0)-1),"*" & $B5 & "*") but all I get is #VALUE! if this file is not open on the PC I'm using. ...

Handling blank data points
I have a chart which is has "" in a formula to clear contents when not applicable to show error. This results in the chart treating the cell as 0 and therefore ugly result in data point. All other post responses to this type of question suggest using NA() and conditional formation to hide the error.. Unfortunately when this is used this screws up my SUM() and AVG() formulas.. Any other suggestions? Thanks Jo Hi Jo, I don't know if this is the best way but what I have done under similar circumstances is use the #N/A for the chart series data column and then I use a hel...

Dynamic view of data based on cell content
Good day all. Excel 2007. I have (many) columns each displaying student data in rows below. On a separate sheet I have a data validated sellection from a dropdown menu of the students names. What I am trying to achieve is that when a particular student name is selected from the dropdown, the corresponding student data should appear in fixed cells on the top rows of same sheet where the student name was selected. The cells where the selction is made will move down the sheet row by row, but the data should appear (now for the new student selected) in the same fixed cells on the top...