functions in charts

Is it possible to enter a user-defined function (that 
returns an array) for values in a chart instead of 
specifying a range in the worksheet?
0
bergevj (3)
9/12/2003 4:49:48 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
686 Views

Similar Articles

[PageSpeed] 49

Joe -

You can specify a named range, with the formula built in.  See the 
Dynamic Chart examples and links on my web site:

  http://www.geocities.com/jonpeltier/Excel/Charts/Dynamics.html

A good example of a formula in a named range can be found on Stephen 
Bullen's site:

  http://bmsltd.co.uk

Look for the ChtFmla.xls example.  Tushar mehta has a similar example on 
his site:

  http://tushar-mehta.com

In VBA it's possible to assign an array to the data values in a chart, 
but you are limited in the number of points you can include without errors.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Joe wrote:
> Is it possible to enter a user-defined function (that 
> returns an array) for values in a chart instead of 
> specifying a range in the worksheet?

0
jonpeltier (303)
9/12/2003 5:05:26 PM
[This followup was posted to microsoft.public.excel.charting with an 
email copy to Joe.
Please use the newsgroup for further discussion.]

Yes, in a limited fashion.  Named formulas cannot be parameterized.  
So, about the best one can do is the following.

Suppose you define a UDF as:

Function YVals(x As Range)
    Dim y As Variant, i As Long
    ReDim y(1 To x.Cells.Count)
    For i = 1 To UBound(y)
        y(i) = x(i) ^ 2
        Next i
    YVals = y
    End Function

Then, define an XL name as
YvalsName	=yvals(Sheet1!$A$1:$A$12)

Now, plot YvalsName in a chart.  As you change A1:A12, the chart will 
change.

You can take it a step further.  Change the definition of YvalsName to
YvalsName	=yvals(OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1))

Now, as you add or delete contiguous data points starting with A1, the 
chart will also change.

-- 
Trouble finding replies to your posts?  Use a newsreader.  See the 
tutorial 'Outlook Express and Newsgroups' on my web site

Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <287401c3794d$e0d257f0$a601280a@phx.gbl>, 
bergevj@wellsfargo.com says...
> Is it possible to enter a user-defined function (that 
> returns an array) for values in a chart instead of 
> specifying a range in the worksheet?
> 
0
ng_poster (159)
9/15/2003 9:38:22 PM
Reply:

Similar Artilces:

Excel 2007 Chart in VB6 Program
I have another thread in the VB group and was told to post to these two groups so sorryfor the multi-post. Ill post there again just in case folks want to follow the thread. So I've had a Visual Basic 6 app (as an EXE) that has worked great for the last 9 years or so. Up until Excel 2003 I have had to make minimial changes for it to work properly. The app has an OLE control which acts as a container for many different external sources .In this control I embed an Excel workbook and display a chart sheet. I can interact with the workbook through the normal means of Excel programming objects...

how do I recover charts within an excel workbook?
The workbook had many charts that were previously visible. Now, upon opening the file, the charts are no longer visible. How to I reactivate them to appear? thanks ...

Track in CRM function
Hello. Upon selecting the “Track In CRM” button from the CRM line in Outlook I have two questions based on situation: 1) If the contact exists in CRM it appears to attach the email but provides no notice unless you try to attach it again? True? 2) If the contact does not exist it attaches the email where? In v1.2 it opened a window and made the user create a record. I think it is attaching or tracking the email in CRM somewhere because trying to do it again, I'm asked if you want to attach/track it again. Does this make sense? By the way, we are using CRM 3.0 Thanks. After c...

Lookup Function in user form
I have a combo box (cboitem1) and a text box (txtprice1). I am using the code below to have txtprice1 fill with the corresponding price the worksheet: Private Sub Cboitem1_Change() If cboItem1 <> "" Then txtprice1.Value = Excel.WorksheetFunction.VLookup(cboItem1.Value, Sheets("Pizzas").Range("A1:B65536"), 2, False) End If End Sub It works perfectly when you enter an existing value in cboitem1. However if 'the user' accidently types a letter that is not the first letter of one of the existing items VB debugging pops up with error &qu...

Problem with Asset Allocator pie charts
Everything "seems" to be working fine with Money 2005 Premium (after restarting the program multiple times). I'm having a problem with many of the pie charts. They do not show up at all. I upgraded from Money 2004. Any ideas? I'm running Windows XP Professional if that's of any help. Thanks, Patrick I've seen them going on a new file on Win XP Pro - do you get them on a new file, or do they not appear on both a new and old one? -- Glyn Simpson, Microsoft MVP - Money http://money.mvps.org Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;...

Draw line in a chart
I have the following problem. I have a chart (date on x axis) on which I draw a line. How can I obtain the ending points of the line in order to compare, mathematically, their positions to corresponding values of chart at the same date? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements I'm sure your post makes perfect sense to you and might to other readers, but I am los...

how to chart who was working when
Hi there, I'm looking for some help in producing a chart. My organization has hired about 100 folks since 1991, each on contracts of varying lengths of time. Some peoples' contracts have ended, and some have been renewed. I set up some worksheets inputting their names, start and end dates with the intent of producing a graph that will tell me exactly how many people were working for us at one time, based upon 6 month intervals. For example: John Doe 7/29/91 - 3/31/05 Jane Doe 6/21/94 - 2/20/96 Jake Doe 8/2/01 - 4/18/03 I would like a chart that can tell me how many people w...

Tracked changes function misbehaving
I have a simple workbook (2002) in which the Track Changes feature is enabled. End users have reported periodic problems with the contents of cells "mysteriously" changing. In process of investigating these, have found that when tracked changes are highlighted, the comments that describe changes are not always associated with the correct cell. Example, comment on cell B114 states "Changed cell B114 from '<blank>' to '12345'". In fact, contents of cell B114 is "67890" and contents of cell B104 is actually "12345". Has anyone...

Pivot Chart: Stop skipping Dates on X-Axis
I have a pivot chart which pulls 2 pieces of information from one table 1 and 1 from Table 2. All three entries are included for each day. Except somedays which may have only the one piece of data from Table 2 or none at all. How do I get the table to automatically fill in all the days whether they have information or not? =?Utf-8?B?UHJpbmNl?= <Prince@discussions.microsoft.com> wrote in news:A5EF53AB-6063-434F-BFC3-43259301751D@microsoft.com: > I have a pivot chart which pulls 2 pieces of information from one > table 1 and 1 from Table 2. All three entries are included for &...

email charts
I have an excel workbook file containing many department sales charts that recieve data from a group of central data entry spreadsheets. Each department chart resides on its own sheet. Is there a way to email individual chart sheets (or snapshots of the chart sheet) to the respective departments without sending the entire parent file to all recipients? Thanks, Mnaut You could export the chart as a gif file, then email that file to the department. The following code creates a gif file from the active chart: '============================ Sub ExportChartGIF() ActiveChart.Export Filenam...

see chart from pushing button
how would I see a chart from pushing a button on the spreedsheet ? ...

Pivot charts formatting
Hi, I want to change the formatting imposed by default in a pivot chart and I want that changed formatting to be retained permanently. For example, for my bar chart, for the different series I have selected colours / patterns but every time I refresh the pivot table / chart; excel falls back to its own colour coding. Any way to get around this? Many thanks in anticipation. Not directly. The only way to simulate this is to record a macro of the formatting changes that you make and then set it up to run whenever the PivotChart calculates. Assuming your pivot chart is on a seperate shee...

Switching x-axis & y-axis on line chart
I am working with a large data spreadsheet with year, number and currency data. My problem is when I extract data to create a standard line chart, the chart is defaulting to a specific x-axis and y-axis format with data related to each axis. For visualization purposes, I would prefer to have the data that is currently presented on the x-axis to instead be presented on the y-axis. And likewise, I would like the y-axis data be presented in the x-axis. However, I have been unable to figure out how to do this and the MS Excel Help menu does not seem to address this. Can anyone help? If ...

pie-charting non-numeric data
I would like to chart and graph responses to a survey that are non-numeric in nature, such as a bar graph for "yes" and no" responses to a question. Or in particular a pie chart that shows how many people picked option "P" vs. "K", "J" or "N". For the pie chart I have a column of data cells, each containing one of the four letters. In short, how do I accomplish charting and/or graphing non-numeric responses? So you have a column of Yes/No or P/K or J/N? Construct a pivot table of this range, and you can produce counts of each value...

Excel 2003: How to make transparent columns in Excel chart?
If you create a bar plot froma given dataset you can format the columns by right clicking and choosing the desired options. In the tab that opens there is a slider which is supposed tho set the level of transparency of the column (selceted area). But so far i couldn't find a way how to use this slider. I know that there is an alternative way to get transparent bars by creating a rectangular object formating it and the use copy -> paste. But i wonder what is the slider for if you can't use it? Does anybody know have an idea? Cheers, Thomas ...

Set Out of Office (or a rule) to only function on weekends
Hello, I am trying to set up so that every weekend my internal e-mail gets forwarded to my home(smartphone) account. I can only find options to run a rule or out of office assistant to run for specific date ranges. How do I set up either a rule or the out of office assistant to 'push' mail to me only on weekends? HCA.CWT.RN wrote on Fri, 02 April 2010 09:04 > Hello, I am trying to set up so that every weekend my internal e-mail gets > forwarded to my home(smartphone) account. I can only find options to run a > rule or out of office assistant to run for spec...

List of kernel mode compatible functions
Hi, Is there a list of kernel mode compatible C/C++ functions (preferably for WDK 7600)? Thanks, Jan Wouldn't that be the WDK Help file? Thomas F. Divine "Jan M" <jan_m@jerseymail.co.uk> wrote in message news:e7fhXcanKHA.1548@TK2MSFTNGP02.phx.gbl... > Hi, > > Is there a list of kernel mode compatible C/C++ functions (preferably for > WDK 7600)? > > Thanks, > > Jan > Your right of course. I just didnt look hard enough. Jan ...

Using a OR() like function in an IIF statement
Hello, I am trying to create an IIF statement to test if the first character in a field is a 1,2,8 or 9. Something like the following: IIf(Left([possible_SO_match],1)="1 or 2 or 8 or 9",[Possible_SO_Match],"No Match") Is there a way to create it without going to a 4 level nested IIF statement? Thanks, Kerry -- Message posted via http://www.accessmonster.com kkulakow via AccessMonster.com wrote: > Hello, I am trying to create an IIF statement to test if the first > character in a field is a 1,2,8 or 9. > Something like the following: > > IIf(Left([possib...

FORECASTING SALES (please let this be a worksheet function)
Hi I have recently been given the task by my boss to forecast one of ou key customers monthly usage until monthly until end of 2006, I am starting with this customers indidual branches monthly usage fo the past 3 years, Some branches will have opened and closed during thi time, And i am looking to be able to predict monthly usage for the nex 18 months Has anyone got any idea's on the best way to forcast within excel, I a currently using trend (fomulae given to me on this sight) but people ar saying this is not the best way Exponentionally has been mentioned by a few of my collegues but...

Stack Bar Charts
I have yearly information that I would like to graph in a stacked bar chart. I have years 2005 to present Two companies I am comparing with two variables for each company each year. I would like to have the stack bar chart by year reflect the stack of Co A vs Co B on variable 1 + 2. Any help would be appreciated. Thanks. You want a clustered-stacked chart: http://peltiertech.com/WordPress/clustered-stacked-column-charts/ http://peltiertech.com/WordPress/clustered-stacked-bar-charts/ - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ Sean wrote: > ...

personal income and expenditure chart
can anybody give me a personal income and expediture chart please Thanks Mike mikeyboy87 wrote: > can anybody give me a personal income and expediture chart please > Thanks Mike You can do this in Excel but why re-invent the wheel? Quicken or Money have far more tools and options. If money is an issue try checking ebay. I purchased Quicken 2004 for about $10 including shipping. It isn't the latest and greatest but it does what I need it to do. gls858 ...

Need to insert a picture using a function
I would like to know if it is possible to insert a picture (.jpg) using a function. I have a simple quote sheet setup for my customers and would like to insert a picture of the product next to the quote information. Is it possible to insert a picture based on what i enter as my product number?? I know this is a vague question, sorry. Someone help please!!! Try this link for a possible solution:- http://www.mcgimpsey.com/excel/lookuppics.html -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 -------------...

Is any group function which concatenates data?
Hello. In a query as follows I would like to concatenate data instead of aggregating in a field which is not a part of group clause. SELECT invoice_no, device_type, concatenate_text(device_serial),customer FROM t1,t2,t3,t4 WHERE .... group by invoice_no, device_type, customer Full data set without grouping looks like: SRW/1/2010, AAASSSQQWE, sn000001, ABIX SRW/2/2010, AAASSSQQWE, MSD001XXX, ADA SRW/2/2010, AAASSSQQWE, sn000002, ADA SRW/3/2010, AAASSSQQWE, sn000001, ADA SRW/4/2010, AAASSSQQWE, sn000001, ADT I would like to have something like this after group by and "co...

Disable COPY function
Does anyone know how can I avoid/restrict people Copying-n- pasting data from my excel spread-sheet. I am trying to lock down an excel spread-sheet to "Read Only" - in its true sense and dis-allow even copying data from this sheet to any other. Basically I want to turn off the COPY function. Secondly, if a person saves this sheet locally under another file name, how would I still disallow the COPY function. Is there any VBA module (which I can later password protect) which takes care of this. I do not have admin rights to set folder/file server permissions and the hiararch...

Chart drilldown to data
I have a client that has MS Excel 2000 bar charts made from worksheet data series. He wants to be able to click on a chart object such as a bar and have the source data come to the front. I have tried to put buttons over the chart but I can't change the button color to match the chart. I can change the chart bar to be the gray color of the button but that is not desirable. Is their a way to send the button to the back behind the chart and still get the button to respond to a click? Is there any way to turn a worksheet cell into a button? The only way I can see is to place a button ov...