Autoupdate Charts

Good Evening All,

I am hoping that someone here will be able to help me out of a niggling problem
I have searched Google, but found no solution to my niggle.

I have created a Auto Updated LINE Chart, using named ranges utilizing the OFFSET function,
to look at a selected set of data, - eg below:

        A            B
        Month        Value

        01            100
        02            150
        03            200
        04            150
        05            250        etc,etc...

This works fine, as the chart updates as dates/values are added/changed/deleted.

However, the snag being that, the Coulmn B values are a calculated result from other cells,
(with the result being "" (blank) if source cell is blank, but the chart reads this cell as 0 (zero),
and the chart produces a line down to zero, when I would prefer the line on this part of the chart not to appear.

I hope I have explained this well enough, and hope that this peculiarity may be solved.

Thank you
Mathew
            





---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004
0
mpb14728 (11)
1/21/2004 7:21:50 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
442 Views

Similar Articles

[PageSpeed] 23

Mathew -

Change the formula so it returns NA() instead of "". ("" is not blank, 
and Excel offers no BLANK() function.)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Mathew Bennett wrote:

> Good Evening All,
> 
> I am hoping that someone here will be able to help me out of a niggling problem
> I have searched Google, but found no solution to my niggle.
> 
> I have created a Auto Updated LINE Chart, using named ranges utilizing the OFFSET function,
> to look at a selected set of data, - eg below:
> 
>         A            B
>         Month        Value
> 
>         01            100
>         02            150
>         03            200
>         04            150
>         05            250        etc,etc...
> 
> This works fine, as the chart updates as dates/values are added/changed/deleted.
> 
> However, the snag being that, the Coulmn B values are a calculated result from other cells,
> (with the result being "" (blank) if source cell is blank, but the chart reads this cell as 0 (zero),
> and the chart produces a line down to zero, when I would prefer the line on this part of the chart not to appear.
> 
> I hope I have explained this well enough, and hope that this peculiarity may be solved.
> 
> Thank you
> Mathew
>             
> 
> 
> 
> 
> 
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004

0
1/21/2004 7:43:09 PM
Hi Jon
Smashing thankyou, it works.
As an extra though, how can I force the cell to dispaly "" (blank), when #N/A appears.
I have tried additional If statements (circular refs) & conditionally formatting, but to no avail.
Cheers for your input.
Yours,
Mathew
"Jon Peltier" <jonxlmvpNOSPAM@peltiertech.com> wrote in message news:%23TrmIbF4DHA.2332@TK2MSFTNGP10.phx.gbl...
Mathew -

Change the formula so it returns NA() instead of "". ("" is not blank, 
and Excel offers no BLANK() function.)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Mathew Bennett wrote:

> Good Evening All,
> 
> I am hoping that someone here will be able to help me out of a niggling problem
> I have searched Google, but found no solution to my niggle.
> 
> I have created a Auto Updated LINE Chart, using named ranges utilizing the OFFSET function,
> to look at a selected set of data, - eg below:
> 
>         A            B
>         Month        Value
> 
>         01            100
>         02            150
>         03            200
>         04            150
>         05            250        etc,etc...
> 
> This works fine, as the chart updates as dates/values are added/changed/deleted.
> 
> However, the snag being that, the Coulmn B values are a calculated result from other cells,
> (with the result being "" (blank) if source cell is blank, but the chart reads this cell as 0 (zero),
> and the chart produces a line down to zero, when I would prefer the line on this part of the chart not to appear.
> 
> I hope I have explained this well enough, and hope that this peculiarity may be solved.
> 
> Thank you
> Mathew
>             
> 
> 
> 
> 
> 
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004
0
mpb2 (23)
1/21/2004 9:02:07 PM
Hi Mathew
To force a "blank" cell use conditional format: Enter the following
formula as condition for cell A1:
=ISNA(A1)
Use white as text color

HTH
Frank

Mathew wrote:
> Hi Jon
> Smashing thankyou, it works.
> As an extra though, how can I force the cell to dispaly "" (blank),
> when #N/A appears.
> I have tried additional If statements (circular refs) & conditionally
> formatting, but to no avail.
> Cheers for your input.
> Yours,
> Mathew

0
frank.kabel (11126)
1/21/2004 9:22:43 PM
Hi Jon
Smashing thankyou, it works.
As an extra though, how can I force the cell to dispaly "" (blank), when #N/A appears.
I have tried additional If statements (circular refs) & conditionally formatting, but to no avail.
Cheers for your input.
Yours,
Mathew

"Jon Peltier" <jonxlmvpNOSPAM@peltiertech.com> wrote in message news:%23TrmIbF4DHA.2332@TK2MSFTNGP10.phx.gbl...
Mathew -

Change the formula so it returns NA() instead of "". ("" is not blank, 
and Excel offers no BLANK() function.)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Mathew Bennett wrote:

> Good Evening All,
> 
> I am hoping that someone here will be able to help me out of a niggling problem
> I have searched Google, but found no solution to my niggle.
> 
> I have created a Auto Updated LINE Chart, using named ranges utilizing the OFFSET function,
> to look at a selected set of data, - eg below:
> 
>         A            B
>         Month        Value
> 
>         01            100
>         02            150
>         03            200
>         04            150
>         05            250        etc,etc...
> 
> This works fine, as the chart updates as dates/values are added/changed/deleted.
> 
> However, the snag being that, the Coulmn B values are a calculated result from other cells,
> (with the result being "" (blank) if source cell is blank, but the chart reads this cell as 0 (zero),
> and the chart produces a line down to zero, when I would prefer the line on this part of the chart not to appear.
> 
> I hope I have explained this well enough, and hope that this peculiarity may be solved.
> 
> Thank you
> Mathew
>             
> 
> 
> 
> 
> 
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004
0
mpb2 (23)
1/21/2004 9:55:59 PM
Hi Guys,
Great, thank you.
Between you both, you gave me exactly what I needed.
Cheers again, many thanks,
Mathew

"Frank Kabel" <frank.kabel@freenet.de> wrote in message news:u17Q3SG4DHA.2080@TK2MSFTNGP11.phx.gbl...
Hi Mathew
To force a "blank" cell use conditional format: Enter the following
formula as condition for cell A1:
=ISNA(A1)
Use white as text color

HTH
Frank

Mathew wrote:
> Hi Jon
> Smashing thankyou, it works.
> As an extra though, how can I force the cell to dispaly "" (blank),
> when #N/A appears.
> I have tried additional If statements (circular refs) & conditionally
> formatting, but to no avail.
> Cheers for your input.
> Yours,
> Mathew



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004
0
mpb2 (23)
1/21/2004 10:56:18 PM
Hi Guys,
Great, thank you.
Between you both, you gave me exactly what I needed.
Cheers again, many thanks,
Mathew

"Frank Kabel" <frank.kabel@freenet.de> wrote in message news:u17Q3SG4DHA.2080@TK2MSFTNGP11.phx.gbl...
Hi Mathew
To force a "blank" cell use conditional format: Enter the following
formula as condition for cell A1:
=ISNA(A1)
Use white as text color

HTH
Frank

Mathew wrote:
> Hi Jon
> Smashing thankyou, it works.
> As an extra though, how can I force the cell to dispaly "" (blank),
> when #N/A appears.
> I have tried additional If statements (circular refs) & conditionally
> formatting, but to no avail.
> Cheers for your input.
> Yours,
> Mathew



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004
0
mpb2 (23)
1/21/2004 11:00:00 PM
Reply:

Similar Artilces:

Column Charts
I would like to produce a stacked column chart to show two different types of income over two years. I cannot for the life of me work out how to do it. HELP!!! Enter the data on a worksheet, e.g.: 2001 2002 A 100 150 B 100 150 where the income types are A and B. Select a cell in the range, and click the Chart Wizard button Select the Column chart type, and the Stacked Column subtype Marion wrote: > I would like to produce a stacked column chart to show two different types of > income over two years. I cannot for the life of me work out how to do it. > HELP!!...

Making charts equivalent
We track the average patient value on a number of different analytes. Is there a standard way to make the graphs appear equivalent? If I track hemoglobin with a mean of 14.0 with the minimum set at 10 and the max set at 18, and then I have a second graph for hematocrit that has a mean of 38, but the max is set at 41 and the min at 35, the latter is a steep graph, and the former is flat, even though the hemoglobin values are approximately 1.3 the hematocrit. But the setting of the axis values can potentially make data look misleading. I am looking for a rule so that data will appear consist...

Standard Chart from PivotTable ?
Hello, (how) can I create a normal chart based on a PivotTable ? Thank you in advance. H.G.Lamy ...

URGENT: Creating a chart (Getting the correct input and output) unable to figure out
Hi, I have been trying to create this chart since a long time now and go no clue what am donig wrong. I have some machines in my company and all of them have a end of life for example: Machine 1, at location A with End of Life in 2004 Machine 2, at location B with End of Life in 2008 I want the years on x-axis and the machine locations on y-axis. Afte that I want horizantal lines representing machines which also show th end of life. For a better understanding, see the attached excel file. I have als created an image of the chart I'm looking to draw. Please advise. Thanks. Ker ...

Charting Question
Hello, Using Excel 2007, and Windows 7. Always something new, I guess; never had this before. Have a chart as a Chart 1 tab on the bottom to which I add data once a week into Sheet 1. Always worked just fine. Today, when I clicked on the Chart 1Tab on the bottom to see the new graph, all my graph lines and curves have totally disappeared. The x and y axes are still there, as are the axis labels. But no graph. Totally wiped out. What might have happened, please ? All I did was simply click on the bottom tab that said Chart 1. How can I get it back without having to go thru a whole new...

Annotate Chart with Text Boxes
I have some charts that are annotated with Text Boxes that line up with that Months data. If I add extra months to the Chart then I have to move all the Text boxes along a little to the left to remain matched up with the Data it corresponds to, can this be automatically done? Thanks Pete Hi, Textboxes by themselves will not track your monthly data. For that you would need to use data labels. To update the textboxes would require VBA code to calculate there position in relation to the chart and the data. Cheers Andy Pete wrote: > I have some charts that are annotated with Text Boxes ...

Export Chart for publication
I have a big problem with Excel chart 1. How can I export excel chart with eps or some other raster format 2. When you export a chart using vba with tiff, png,... the graphic quality is low an also it always shows the grid thicker than the original one. Would you please direct me how can I control size of charts for example in mm ? ...

Chart not charting right
I am using Excel 2000 I have made many a chart and never have had this problem before. I hope someone can help me. I am charting our scrap $ as a % of our Sales. I have my table with all of this calculated- by month for a 3 year period. My table is formated as %'s. The % are correct. I created a simple line graph to show the %'s. The first year graphs Ok. What I mean is that the Y axis has the % and the line points line up with the Y axis fine. Example: 4.5% is between 0% and almost to the 5% line. But the 2nd and 3 year do not graph correctly at all. Example: 1 mont...

individual charts
I have a lot of individual ROWS of data and would like to be able to click on a say (say at the beginning of the row) and automatically produce a chart, or to link the chart to a mail merge docu,emt so that each page of the document gets its own individual chart. Has any body any solutions or tips. Thanks Ray Willis See my post in a concurrent discussion on the same topic. It's at http://groups.google.com/group/microsoft.public.excel.charting/msg/9be8 2bef60a613a6 -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office producti...

help with charting in excel
I am fairly new at this so sorry if this is an obvious answer. I have created a spreadsheet with the following columns: Payee, Date and Amount Paid. It is simple, functional and working fine. I want to be able to create a chart so that i can easily view the amount paid by each Payee, and the percentage that each Payee represents the total amount of revenue. My problem is this: when i set up the chart, i am using a pie graph, and if Joe Smith plumbing is listed 10 different times(all different dates of course), the pie graph will show Joe Smith plumbing 10 separate times. What is w...

Hierarchy and Organizational Charts
We'd like to understand the hierarchy of individuals and roles within a given account (actually, ideally we'd have an org chart that could be visually generated). How can this be done? I haven't used their product, but I came across this add-on that provides the ability to make org charts within CRM entities: http://www.salescentric.com/glance.html There are probably others out there as well. -- Matt Wittemann, CRM MVP http://icu-mscrm.blogspot.com "Jasonh" wrote: > We'd like to understand the hierarchy of individuals and roles within a given > account...

Autoupdating Numbers
bump please anyone... help... : -- georgi ----------------------------------------------------------------------- georgio's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1644 View this thread: http://www.excelforum.com/showthread.php?threadid=31411 Your bump post doesn't have any connection with any other post (well, in my newsreader anyway). You may want to post back with your question. And if you didn't get a response, you may want to rephrase it. georgio wrote: > > bump > please anyone... help... :S > > -- > georgio > ---...

Combination Charts #2
I am trying to find a tutorial to help me understand the concept of Combination Charts. I don't know where to go to get a thorough explanation. Does anyone have any online resources that are free? Thanks in advance. Cindy - Try my website, particularly this page and the links from it: http://peltiertech.com/Excel/Charts/ComboCharts.html A combination chart is simply a chart with multiple series that displays the series using more than a single chart type. For example, you might have a chart that shows one series of data using lines and another with columns. There is a very ...

RedYellowGreen Color Banding in Chart Plot Area
I want to instruct sections of the chart plot area to display red, yellow and green bands based on stops, or thresholds. I have tried Format Plot Area>Gradient Fill>Gradient Stops, but it was very imprecise and hard-to-use. 'Suggestions? DOUG I've seen several ways to do this, but the one I prefer is to add 3 additional series to the chart. For example. Green >= 1 0.9 <= Yellow < 1 Red < .9 For the green series, do this =IF(A1>=1,A1,NA()) Yellow: IF(AND(A1>=.9,A1<1),A1,NA()) Red: IF(A1 < .9,A1,NA()) Then I add these three series to the ch...

Help 2nd Request with Microsoft AutoUpdate
This is my 2nd request for help. The Microsoft AutoUpdate.app keeps doing its thing downloading and installing the latest Office update. Where or what file does it check to see if you really need the update? The update has been installed and all of the Office apps are working perfect. Again, the problem is the Microsoft AutoUpdate seems to think I need the new Office update. Hello - Are you certain that the update is actually being installed, not simply downloaded? AFAIK, the update may very well self-extract, but it doesn't automatically install. It creates a disk icon on th...

Excel Chart Mystery
My partner-in-crime sent me this mystery earlier today, and I told her I would post it for this group. 1. A worksheet was created in Excel XP 2002. It contains a combination chart. 2. The worksheet was emailed to a user who has Excel 2003. 3. The Excel 2003 user opened the email and found the chart able to be viewed initially, but then the data in the chart dissappearred, leaving only the labels that are not in the Plot Area 4. If the file is subsequently opened with Excel XP 2002, the file and chart open just fine. Any input would be appreciated. Andrew Castillo andrew@REMOVEcastillo....

How to save chart
Do not use the chart feature regularly Have a simple time series I want to chart When I set the chart using the wizard all works fine When I am asked what location I want I do not use the embedded option so I name the chart But then the underlying data file disappears! Where is the trick because the new chart seems to overwrite the basic data file, in effect wiping out the information tks Henry Henry - Does the chart still show the correct data plotted? Or does it reflect the fact that the data is missing? Has the sheet with the data disappeared? No sheet tab where it ought to be? ...

chart menu
I am using Excel 2000 and am trying to calculate a trend line and extrapolate a data set using a polynomial function. My problem is two fold - 1st - I cannot find a built-in function on the pull down list. The formula is listed in the on-line help but I cannot find the actual function. 2nd - the on line help suggests going to the chart menu and merely clicking on the tab to add a trend line - but I cannot find the chart menu. The add a trendline option does not appear among the chart wizard choices. I suspect that this is a simple matter but I am completely flummoxed. Thanks in advan...

autoupdate
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel When I open entourage, autoupdate indicates updates available but I must close entourage, I do, and get the same message, close entourage, I cannot install any updates. On 4/22/08 5:10 41PM, in article ee994bf.-1@webcrossing.caR9absDaxw, "pt2393@officeformac.com" <pt2393@officeformac.com> wrote: > When I open entourage, autoupdate indicates updates available but I must close > entourage, I do, and get the same message, close entourage, I cannot install > any updates. Do you check for updates a...

Chart formatting
Hello, I have a vertical bar chart (ie. a 'column' chart) with staff names along the bottom (x) axis and their hours up the vertical (y) axis. The staff names are rotated by 90 degrees so they read 'upwards'. There are a large number of staff and to make the chart readable I split the staff into 2 equal groups and have a separate chart for each. The charts are identical in format (or at least I thought they were) and differ only in the data they display. On the 2nd chart, long staff names are sometimes not printed properly, the name is truncated. eg. 'Jeremiah Millhouse&#...

Analysing time and days patterns in a chart
Hi there What do you think would be best chart format to analyse some data that consists of: Date of Call out Day of call out Time Of Call out Call out Reference I am trying to see if there is any pattern in the days & times of call outs to improve rosters. Basically I want the time of callout & day of week mapped across dates/months to see any trends. For example if Fridays at 1am are a recurring callout time (after the pub!) or every other Friday etc. Do yo uthink Pivot tabe and charts is relevant or can normal chart provide me with the results? Many thanks I have the sam...

Updating Chart = Out-Of-RAM
I have a 10x10 table which lays to the table as 10 lines of 10 points. I am using VBA to generate values in the table. Each pass is reflected in the Chart. After 204 passes, the Plot area starts to flash 'Pink' and the table no longer gets updated (program stalls/freezes?) Pressing [ESC] or [BREAK] 'stops' the program, the plot area shows the latest values. Exiting Excel and re-opening the workbook then running the program causes the above problem immediatly, the table is not updated at all. Running "Resource Meter" reveals "64% System, 64% User, 75% GD...

Updating Multiple Chart Titles and Scaling to Identical Values
My worksheet has 14 charts that each reference the same company name (Title) and "Y" axis scaling. Can I reference a 'Name' in: Chart Options-> Titles... and on scale-> left click-> format axis-> scale... The 'Name' for each variable would be a cell reference ie., G37 How do I update all 14 chart at once? try use this macro (with yr maximum for Y axis in G1 and name in G37). to insert the macro press ALT+F11 to go to VBA and paste the code that follows Sub cus() For Each cho In ActiveSheet.ChartObjects cho.Activate With ActiveC...

organisation charts in excel spreadsheets
Is it possible to put spreadsheet cell references into a staff organisation chart so that if, say, the salary or grade in the chart is changed it updates a separate spreadsheet showing total staff costs. -- Tony No. I've encountered the same problem. Not even in MS Visio is there a solution for this. It's a re-typing job. "Tony" wrote: > Is it possible to put spreadsheet cell references into a staff organisation > chart so that if, say, the salary or grade in the chart is changed it updates > a separate spreadsheet showing total staff costs. > -- >...

Autoupdate 2.1.1 will not launch
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel Hi, when I try to launch the Autoupdate 2.1.1 installer downloaded from officeformac.com I get this error (translated from danish): The Application "AutoUpdate 2.1.1 Update" could not start because the was an error in "&lt;BaseInstaller&gt;&lt;CarbonLib&gt;&lt;CFMPriv_Help&gt;&lt;&gt;" I have the latets updates for OSX installed (10.5.2, 2008-002 v1.0, safari 3.1, etc.) installed. This is the error from the console: Mar 31 12:13:03 TobiiBook [0x0-0x3ad3ad].com.MindVi...