#### How to chart a series of formulas?

```Hi, does anyone know how to chart a series of formulas?

I currently chart cells in column C.   Each cell in column C contains
a formula - "=A1+B1", "=A2+B2", "=A3+B3", etc.

I would like to do away with column C and just get the chart to
calculate the values that are being displayed automatically.  How can
you do this?

Thanks
```
 0
10/6/2004 6:31:54 PM
excel.charting 18370 articles. 0 followers.

3 Replies
626 Views

Similar Articles

[PageSpeed] 34

```Marcus -

Charts aren't that smart, and worksheet ranges are pretty cheap. Compute the data in
the worksheet, and plot the column with the formulas.

Of course, you could name your ranges (Adata and Bdata) by selecting each range,
typing a name in the Name box (just above cell A1 on the Formula bar), and pressing
Enter. Then define a new name (Ctrl+F3), call it Cdata, and define it as
=Adata+Bdata. When making a chart, in step 2 of the wizard, click on the Series tab,
click Add, and in the Values box, enter =MyBookName.xls!Cdata.

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

Marcus Leon wrote:

> Hi, does anyone know how to chart a series of formulas?
>
> I currently chart cells in column C.   Each cell in column C contains
> a formula - "=A1+B1", "=A2+B2", "=A3+B3", etc.
>
> I would like to do away with column C and just get the chart to
> calculate the values that are being displayed automatically.  How can
> you do this?
>
> Thanks

```
 0
10/7/2004 7:27:08 PM
```Thanks Jon, that works great.  How would I create a formula like
"=A1+B2", "=A2+B3", "=A3+B4", etc?

Marcus.

```
 0
10/11/2004 2:03:39 PM
```Use the worksheet. As soon as a defined formula gets at all complicated, it becomes
severely difficult to understand and maintain.

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

Marcus Leon wrote:

> Thanks Jon, that works great.  How would I create a formula like
> "=A1+B2", "=A2+B3", "=A3+B4", etc?
>
> Marcus.
>

```
 0
10/13/2004 1:30:38 AM

Similar Artilces:

Copying a formula to new workbook
Is there any way to copy a formula from one workbook to another without bringing over the reference to the originating workbook. If you're just bringing one formula, you can copy from the formula bar and paste into the formula bar. If you're doing lots then you can change the formulas to text, copy|paste, and then convert those text strings back to formulas (in both workbooks): select the range edit|replace what: = (equal sign) with: \$\$\$\$\$= (some unique string) replace all copy|paste edit|replace what: \$\$\$\$\$= with: = replace all (both workbooks) === Another option ...

Formulas in Spread sheet quit working???
I have a database in a spreadsheet that keeps track of stats for a couple of teams I am on. The formulas have quit working now. Every single formula has quit working since I last worked on the sheet last week. Even simple adding of columns. I can enter the information in the columns and then go to the cell with the formula and open and hit enter and it refreshes the cell and starts working. Any Ideas???? This is very frustrating. Thanks for any help. Richie Richie, Is Tools/Options/Calculation set to Automatic?? John Richie wrote: > I have a database in a spreadsheet tha...

Formulas to (a) pull month, year and (b) sum within date range?
I have a list of dates in column A, and numbers down cols B-E. Off to the right, I need a table that lists the month/year and the totals for each of the columns. It needs to be a separate table, rather than a pivot table, so it can be copied-and-pasted by others. What I _think_ I need is: -- a formula in H2:H10 (maybe more) that will look down the full range of dates (call it A2:A100) and put one month/year per row >> H2 = 02/09 H3 = 03/09 H4 = 04/09 etc. -- a formula in I2 that will sum all entries in B2:B100 with the month and year of H2 I think I could get the secon...

excel series charts
Hi, I am trying to create a line chart in excel with two series. Conside the following data: np time1 time2 1 1000 100 2 900 90 3 850 80 4 700 70 5 600 60 6 400 30 7 300 10 8 270 5 The first series is plotted correctly but the second series i completely wrong. I was wondering if someone else has encountered suc a problem with two series charts. Can anyone suggest a solution to thi problem. thanks, sami -- samit70 ----------------------------------------------------------------------- samit700's Profile: http://www.excelforum.com/memb...

Formula help *
Office 2007 I am trying to display in a cell the last value in a selected column. Numbers are added daily to this column but I need this specific cell to show the last entry at all times - any ideas or help would be appreciated Thanks in advance Bryan Sun, 10 Feb 2008 20:53:28 +0900 from JohnWA <j_ustforfun@hotmail>: > Office 2007 I am trying to display in a cell the last value in a selected > column. Numbers are added daily to this column but I need this specific cell > to show the last entry at all times - any ideas or help would be appreciated =OFFSET(\$A\$1,COUNTA(\$...

Excel Charts #19
I am making a chart with weird X-values. Instead of single, whole numbers, I need each value to be as follows: 1-2, 3-5, 6-10, 11-15, etc. Please help me!! My chart keeps going to whole numbers instead and its been driving me crazy for days! How can I fix this problem?!! ...

Saving chart templates
I have created some charts and saved them as templates (crtx). I can apply them to existing chart and, subject to some occasional odd behaviour (plotting agains the ''wrong' axis) it works ok. Not a huge timesaver but you take what you can get. However, if i 'Insert' a new chart using any of three templates I have created - and then attempt to add data to it the workbook is corrupted when I try to save and ALL charts and Range names disappear. I am using the name manager from peltiertech but that has always worked before and even if i avoid using it the pr...

Excel formulas #9

= if formula needed
good day all, i am trying to get this formual ie: =if(q80="closed",q1=now())i cant get it right. i have tried various variations of the formula.any help is appreciated. thx wyn -- wynb in you example is the cell you want updated "Q1"? If so than it should read =IF(Q80="closed",NOW()) -- Kevin "WYN" wrote: > good day all, i am trying to get this formual ie: =if(q80="closed",q1=now())i > cant get it right. i have tried various variations of the formula.any help is > appreciated. thx wyn > -- > wynb In that formula yo...

charting performance speed
So am I correct in finding that the charting preformance speed of 2007 is an absolute dog? Trying to chart 16 datasets with 2055 data pointrs each on an x-y chart, with the colours of some of the lines adjusted to keep them in four "matching sets". I have to wait for nearly a minute for a redraw when you flick back a tab. Can't type any headers without taking a break for a coffee. Am I missing something please? Your impressions match my own. Excel 2007 with SP1 installed is slightly better in some situations. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials ...

Simple If, Then formula for excel
Hi all, So I am fairly weak when it comes to Excel formulas, so I'm sure what I require is really simple, I just have no idea how to formulate it. Basically, I have a column of values, some of which are negative values and some are positive, but they are changing from month to month (i.e. accruals and prepayments). I need to add up all the negative values from the column in a new cell, and all the positive values in another. So it will be something like If amount>0 then add to cell, else add to other cell. So there you have it, really simple I just have no idea what to do. So i...

Can you combine data from 2 worksheets into 1 chart?
My son is working on a science project. He had to track the temperature and precipitation levels in Daytona Beach for 9 years. I put the data in 2 worksheets, one for temperature and one for precipitation. He now has to make charts for the data. We've done the 2 separate charts but now we need to make 1 chart showing all the data. Is there a way to take the data from the 2 separate worksheets and put it into 1 chart? http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom So...

Is there an easy way to denote significance on charts
The only way I know how to add significance to charts, such as an asterisk above a bar chart, is by adding a text box. Any adjustments to the chart require rearranging the text box. Adding significance to charts is quite common and I can't believe Excel would not have an easier option for this. Another option is to apply shapes to hidden series. For example, if the shape were an asterisk you could then control the position of the asterisk based on your source data. The asterisk could always appear over the bar even if the bar moved when the source data changed. -- John Mansfie...

Overlapping 2 charts (XL2003)
I have a 5 row 3 column sheet in A1:C5. Here are the 15 values (first 3 are column headings): type day amount A,1,10 A,2,11 A,3,12 B,3,7 I'm aiming to have a chart with 1,2,3 on x axis, and 3 blue values: 10 units tall over the 1; 11 over the 2; 12 over the 3. Then I want a red 7 over the x value 3. So I want to see both 12 and 7 above the 3 on the x axis, either overlapping or side by side. I created a chart by highlighting C2:C4 and hitting F11. I can name that series as A. So far so good. Now I want to overlay B data, so under Source Data I add a series named B with values =sheet1!c5...

Excel Formula #8
I am trying to format spreadsheet. Need 3 counts. 1st count is for 90% and above =COUNTIF(H5:M16,">89.99"). Second count is for Under 80% =COUNTIF (H5:M16,"<79.99") . Can anyone help me with a formula for 3rd count, between 80% and 89.98? I can't seem to formulate it. Thanks in advance Hi Barb, Try =COUNT(H5:M16) - COUNTIF(H5:M16,">89.99") - COUNTIF(H5:M16,"<79.99") -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Barb" <...

Finding data for a chart in word
> This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3325082853_2433946 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit After inserting a chart in a word document I cannot seem to get back to the excel worksheet update data on the chart. Advice please. Thank you. Joe --B_3325082853_2433946 Content-type: text/html; charset="US-ASCII" Content-transfer-encoding: quoted-printable <HTML> <HEAD> <TITLE>Finding data for a chart in word<...

Perspective setting won't save in a 3-D column chart
I have a 3-D column chart that has the perspective setting as 20, the elevation as 24 degrees and the rotation as 198 degrees. I change the settings, close the dialog box and click save. Yet the perspective setting resets itself to 15 every time I open the file. I am using Excel 2007. Any suggestions? Thanks. Hi Kathi, I have been able to duplicate your problem, I suggest this is a bug, although there might be some reason for this behavior, I can't think of any. It's probably not worth it but I suppose you could attach a macro to the Open_Workbook event and reset it to...

User Defined Function vs. Named formula
I have used Named Formulas and am now learning about User Defined Functions. One thing I found with named formulas is that the naming conventions around global/local selections. Reading about UDFs makes me think they might be a better option for a lot of my work. Any insight as to When to use one over the other will be appreciated. Thanks Robert IMO they are different beasts. A named formula would be used to shorten formulae by including a name rather than the sub-formula (especially where it is repeated), and to improve readability/maintainability, as the name would/should be meanin...

Obtaining series of data that adds up to a particular large figure
I have a large figure and series of other small figures on differen rows. How do I get the different sets of small figures that exactly or almos add up to the large figure:confused ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com You can try to use solver under tools>solver (if it is not there select it from under tools>add-ins and keep the office/excel cd handy and follow instructions) Put the series of small figures in a range, select that range and click in the n...

XY Scatter Chart Not Showing X Axis
I'm trying to create a quadrant analysis. I have the four quadrants as stacked columns and the real data as a XY Scatter. The x axis for the XY Scatter is not showing. The real data series are marked as the secondary axis. Does anyone know how to make the X axis appear? Usually Excel gives you both secondary axes when you add an XY series to a column chart. Go to Chart menu > Options > Axes, and check the box for the secondary X axis. If you're using Excel 2007, it's found on the middle of the three charting tabs, under Axis. - Jon ------- Jon Peltier, Microsoft ...

Formatting Linked Charts Now An Issue
We just got upgraded to Micro. 2003 and let me tell you it's not pretty. I previously had a lengthy Word doc with a bunch of charts linked from an Excel worksheet I had. Since the upgrade I had to re-link every chart because the links no longer work, why is this?????? Also after I cut and paste special as a link each chart I can no longer freely move the chart around or even right click to get properties and select the size I want the chart to show. I paste it in the right column of a word doc table so I need to size it to fit into the column and I can no longer do this. This i...

How do I create a formula to copy and paste between workbooks?
I would be grateful for any help with this. I am trying to scan one column on a worksheet for a name e.g. Smith and then to copy all the information on the same row into another workbook. There would be many rows with the name Smith in that column and would like ALL of these rows to be copied into the other workbook. I saw the information posted by Bob Phillips on 10 Jan about programming excel to do this but I do not understand how the formula would actually look. Thanks a lot. Easiest way is to put an autofilter on the top of the row heading: Data>Filter>Autofilter: then c...

Data Bar / Progress Meter / Bar Chart
Hi, Could anyone suggest a method of having a text field (or similar) have the behaviour of a data bar in excel 2007. I have a field that displays a percentage and would like to present the information to the user in a bar graph / data bar / progress meter style appearance. The value is only ever updated when the form is first loaded, ie. the values will not require updating/refreshing. The data comes from a query. It would be really great if it could change to yellow at 75% and red at 90% as some sort of conditional format as well? Thankyou. You can insert the microsoft progress b...

Help with formula #14
Here is the formula =InventoryOrdered!D3-COUNTIF(InventorySold!B:B,"10-132500") What I want it to do is every time I enter numbers in cell D3 to CA3 in Inventory Ordered I want it to add those numbers, then subtract Inventory Sold countif function. Not quite sure, but... =sum(inventoryordered!D3:CA3) - COUNTIF(InventorySold!B:B,"10-132500") Crowraine wrote: > > Here is the formula > > =InventoryOrdered!D3-COUNTIF(InventorySold!B:B,"10-132500") > > What I want it to do is every time I enter numbers in cell D3 to CA3 in > Inventory O...

How could i higlight different intervals in the charts
How could i higlight different intervals in the charts. e.g. I have on the horizental axis time from 0 hrs to 24 hrs. and on the vertical axis body temprature. I have three different subjects data on this chart. I would like to higlight the area where those subjects were sleeping, but I could't get the solution in MS Excel. Does anyone helps me on this issue Hi, You could add another series plotted as column. Plotting the maximum value when shading is required. You will need to set the maximum Y axis value so the columns can reach the top of the plot area. Format the columns via t...