#### How do I use a single cell value to chart a horizontal line in Exc

```Background: I am making a chart where monthly expenses are represented by
vertical bars and the monthly budget is a constant, for the entire year,
which is represented by a horizontal line. If a vertical bar for a particular
month rises above the horizontal budget line then I have exceeded my budget
for that month.

What I can do: I can create this chart if I use a monthly budget row where
each cell in the row has the same value.

What I want to do: To keep my spreadsheet manageable, I want to have the
monthly budget specified by a single cell, rather than an entire row, because
it is a constant for the entire yerar.
```
 0
IRA (11)
4/27/2007 1:40:00 PM
excel.charting 18370 articles. 0 followers.

9 Replies
1114 Views

Similar Articles

[PageSpeed] 6

```On Fri, 27 Apr 2007, in microsoft.public.excel.charting,
Ira <Ira@discussions.microsoft.com> said:
>I am making a chart where monthly expenses are represented by
>vertical bars and the monthly budget is a constant, for the entire year,
>which is represented by a horizontal line.

>What I can do: I can create this chart if I use a monthly budget row where
>each cell in the row has the same value.

I don't see why this is such a problem; it looks like a very good
solution to me.

>What I want to do: To keep my spreadsheet manageable, I want to have the
>monthly budget specified by a single cell, rather than an entire row, because
>it is a constant for the entire year.

If you feel you must, then you can create a named array formula whose
size is the same as the number of columns in the expenses range, which
is entirely filled with the value of the budget. Let us imagine that the
expenses row is a range called EXPENSES and the budget value is held in
a single cell called BUDGETVALUE.

(you don't have to actually give these ranges names if you don't want
to, it just makes it easier for me to describe the formula I'm about to
recommend)

Then create a named range called BUDGET, using the following formula in
the "Refers to:" input box:

=MMULT(BUDGETVALUE,EXPENSES/EXPENSES)

(Explanation: EXPENSES/EXPENSES creates a row array the same size as
EXPENSES, but filled with ones. Multiplying it by BUDGETVALUE makes the
row array be filled with the budget value instead)

Then create your series in the chart, as usual, but using the named
range BUDGET instead of a cell range. I must repeat though, that the
first idea is actually much *more* manageable, not less. But this
solution gets you your single cell budget value with no wasted cells.

--
Del Cotter
NB Personal replies to this post will send email to del@branta.demon.co.uk,
```
 0
del1907 (586)
4/27/2007 5:16:15 PM
```On Fri, 27 Apr 2007, in microsoft.public.excel.charting, I said:
>Then create a named range called BUDGET, using the following formula in
>the "Refers to:" input box:
>
>=MMULT(BUDGETVALUE,EXPENSES/EXPENSES)

Sorry, that formula falls over if the expenses row contains any zero
values. This will work better:

=MMULT(BUDGETVALUE,ODD(EXPENSES)/ODD(EXPENSES))

The ODD() rounding function will ensure the number will never be zero,
no matter what. If anyone has a more elegant way of achieving the same
result, can they post it?

--
Del Cotter
NB Personal replies to this post will send email to del@branta.demon.co.uk,
```
 0
del1907 (586)
4/27/2007 5:47:54 PM
```http://peltiertech.com/Excel/Charts/AddLine.html

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

"Ira" <Ira@discussions.microsoft.com> wrote in message
news:B81EF299-F1C3-4BDD-91EE-93327A61AE91@microsoft.com...
> Background: I am making a chart where monthly expenses are represented by
> vertical bars and the monthly budget is a constant, for the entire year,
> which is represented by a horizontal line. If a vertical bar for a
> particular
> month rises above the horizontal budget line then I have exceeded my
> budget
> for that month.
>
> What I can do: I can create this chart if I use a monthly budget row where
> each cell in the row has the same value.
>
> What I want to do: To keep my spreadsheet manageable, I want to have the
> monthly budget specified by a single cell, rather than an entire row,
> because
> it is a constant for the entire yerar.

```
 0
jonxlmvpNO (4558)
4/27/2007 9:13:10 PM
```Hi Del,

I was able create the named range called BUDGET as you described. However I
was unable to use BUDGET as a series value in the Source Data window of the
Chart Wizard.

-Ira

"Del Cotter" wrote:

> On Fri, 27 Apr 2007, in microsoft.public.excel.charting,
> Ira <Ira@discussions.microsoft.com> said:
> >I am making a chart where monthly expenses are represented by
> >vertical bars and the monthly budget is a constant, for the entire year,
> >which is represented by a horizontal line.
>
> >What I can do: I can create this chart if I use a monthly budget row where
> >each cell in the row has the same value.
>
> I don't see why this is such a problem; it looks like a very good
> solution to me.
>
> >What I want to do: To keep my spreadsheet manageable, I want to have the
> >monthly budget specified by a single cell, rather than an entire row, because
> >it is a constant for the entire year.
>
> If you feel you must, then you can create a named array formula whose
> size is the same as the number of columns in the expenses range, which
> is entirely filled with the value of the budget. Let us imagine that the
> expenses row is a range called EXPENSES and the budget value is held in
> a single cell called BUDGETVALUE.
>
> (you don't have to actually give these ranges names if you don't want
> to, it just makes it easier for me to describe the formula I'm about to
> recommend)
>
> Then create a named range called BUDGET, using the following formula in
> the "Refers to:" input box:
>
> =MMULT(BUDGETVALUE,EXPENSES/EXPENSES)
>
> (Explanation: EXPENSES/EXPENSES creates a row array the same size as
> EXPENSES, but filled with ones. Multiplying it by BUDGETVALUE makes the
> row array be filled with the budget value instead)
>
> Then create your series in the chart, as usual, but using the named
> range BUDGET instead of a cell range. I must repeat though, that the
> first idea is actually much *more* manageable, not less. But this
> solution gets you your single cell budget value with no wasted cells.
>
> --
> Del Cotter
> NB Personal replies to this post will send email to del@branta.demon.co.uk,
>
```
 0
IRA (11)
4/28/2007 3:22:02 PM
```On Sat, 28 Apr 2007, in microsoft.public.excel.charting,
Ira <Ira@discussions.microsoft.com> said:
>"Del Cotter" wrote:
>> create your series in the chart, as usual, but using the named
>> range BUDGET instead of a cell range. I must repeat though, that the
>> first idea is actually much *more* manageable, not less. But this
>> solution gets you your single cell budget value with no wasted cells.
>
>I was able create the named range called BUDGET as you described. However I
>was unable to use BUDGET as a series value in the Source Data window of the
>Chart Wizard.

I think that, as with all uses of a named range in chart series, you
need to explicitly refer to the sheet the data series is in. Because the
chart isn't in any sheet, it can't just assume the name is in its own
sheet, it has to be told.

So if you've written the data in Sheet1, then you need to refer to the
range as Sheet1!BUDGET instead of just BUDGET. See if that works for
you.

--
Del Cotter
NB Personal replies to this post will send email to del@branta.demon.co.uk,
```
 0
del1907 (586)
4/28/2007 5:43:33 PM
```Thanks Del,
That pretty much did it. It turns out that the value that woked was
='FILENAME.xls'!BUDGET
Now I have to get it so that the budget line will change when the BUDGET
cell value changes.
I think I have to read up on the Insert / Name functions.

-Ira

"Jon Peltier" wrote:

>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
>
>
> "Ira" <Ira@discussions.microsoft.com> wrote in message
> news:B81EF299-F1C3-4BDD-91EE-93327A61AE91@microsoft.com...
> > Background: I am making a chart where monthly expenses are represented by
> > vertical bars and the monthly budget is a constant, for the entire year,
> > which is represented by a horizontal line. If a vertical bar for a
> > particular
> > month rises above the horizontal budget line then I have exceeded my
> > budget
> > for that month.
> >
> > What I can do: I can create this chart if I use a monthly budget row where
> > each cell in the row has the same value.
> >
> > What I want to do: To keep my spreadsheet manageable, I want to have the
> > monthly budget specified by a single cell, rather than an entire row,
> > because
> > it is a constant for the entire yerar.
>
>
>
```
 0
IRA (11)
4/28/2007 7:48:01 PM
```Never mind what I said about the line not changing. I was changing the wrong
cell. Your solution works beautifully. Thanks so much for the help.

Ira

"Jon Peltier" wrote:

>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
>
>
> "Ira" <Ira@discussions.microsoft.com> wrote in message
> news:B81EF299-F1C3-4BDD-91EE-93327A61AE91@microsoft.com...
> > Background: I am making a chart where monthly expenses are represented by
> > vertical bars and the monthly budget is a constant, for the entire year,
> > which is represented by a horizontal line. If a vertical bar for a
> > particular
> > month rises above the horizontal budget line then I have exceeded my
> > budget
> > for that month.
> >
> > What I can do: I can create this chart if I use a monthly budget row where
> > each cell in the row has the same value.
> >
> > What I want to do: To keep my spreadsheet manageable, I want to have the
> > monthly budget specified by a single cell, rather than an entire row,
> > because
> > it is a constant for the entire yerar.
>
>
>
```
 0
IRA (11)
4/28/2007 7:50:03 PM
```Oops!

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

"Ira" <Ira@discussions.microsoft.com> wrote in message
news:129838C0-0B76-47AF-9894-C7CA92B1C351@microsoft.com...
> Never mind what I said about the line not changing. I was changing the
> wrong
> cell. Your solution works beautifully. Thanks so much for the help.
>
> Ira
>
> "Jon Peltier" wrote:
>
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> http://PeltierTech.com
>> _______
>>
>>
>> "Ira" <Ira@discussions.microsoft.com> wrote in message
>> news:B81EF299-F1C3-4BDD-91EE-93327A61AE91@microsoft.com...
>> > Background: I am making a chart where monthly expenses are represented
>> > by
>> > vertical bars and the monthly budget is a constant, for the entire
>> > year,
>> > which is represented by a horizontal line. If a vertical bar for a
>> > particular
>> > month rises above the horizontal budget line then I have exceeded my
>> > budget
>> > for that month.
>> >
>> > What I can do: I can create this chart if I use a monthly budget row
>> > where
>> > each cell in the row has the same value.
>> >
>> > What I want to do: To keep my spreadsheet manageable, I want to have
>> > the
>> > monthly budget specified by a single cell, rather than an entire row,
>> > because
>> > it is a constant for the entire yerar.
>>
>>
>>

```
 0
jonxlmvpNO (4558)
4/28/2007 11:01:17 PM
```On Sat, 28 Apr 2007, in microsoft.public.excel.charting,
Ira <Ira@discussions.microsoft.com> said:
>Never mind what I said about the line not changing. I was changing the wrong
>cell. Your solution works beautifully. Thanks so much for the help.

You're welcome, and thanks for following up.

--
Del Cotter
NB Personal replies to this post will send email to del@branta.demon.co.uk,
```
 0
del1907 (586)
4/28/2007 11:32:28 PM

Similar Artilces:

A macro to increase line spacing
I'm trying to make a macro on my Word 2007 "Quick Access Toolbar" that will add 6 points after each line that I've selected. When I record a macro to do this, it adds the 6 points fine, but then sets the indent at "0", which was what the paragraph had when I recorded the macro. What I'd like it to do is ignore the indent of whatever paragraph I'm using the macro on, leave it as it is, and just add 6 points. Any suggestions how to do that? Here is how the Macro reads in VB--perhaps if I alter some of the parameters that would do it. Thanks for...

Hi I have workbook that contains a number of sheets. On a separate sheet I would like to be able to insert a hyperlink so that I can jump to a specific sheet. However, rather than inserting all of the hyperlinks manually (I will have to replicate this over many workbooks) I wondered if there was a formula to allow me to jump to a cell (say A1) in another worksheet, based on the name of that worksheet being entered in a cell reference. For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3", "Sheet4". In another sh...

Use Form to prompt for report criteria
I have a form that I am using to prompt for report criteria. When I run the query outside of the form, it works fine - prompting me for both criteria. However when I run from the form, I get #Error#. Can you see what I am doing wrong? Thanks in advance. I have two combo boxes that I have put in my underlying query. In the fields of the query are: [Forms]![frmSelection Criteria Form]![OfficeNumber] [Forms]![frmSelection Criteria Form]![Manager] *** On the OnClick event is the following: Private Sub Command6_Click() On Error GoTo Err_command6_Click Dim stDocName As String st...

How do I freeze or lock cells to show up on each page without typ.
I have a 4 page sheet. I have a header already. But I want to freeze the cells that head up the first page. I've done it before in school but can't remember what it is called or how to do it...that's why I'm doing this. Anyway, I want these cells to print off on each new page without having to type them on each page. I hope that makes sense and I hope that someone can help me! If you mean for printing do file>page setup>sheet and select rows to repeat at top otherwise for viewing you can select a2 if the headers start in row 1 and do window> freeze panes ...

Naming charts on own sheet
Hi. I have a series of charts (which are all contained on their own sheets). I need to name each of the charts (as they will be used by someone else in a macro). I have tried clicking on them and also pressing shift before clicking on them, and I am not able to change the name in the name combo box. Can anyone advise me of how I can change the names. Thanks for your help. Hi, If you have chart sheets you can change the name by simply changing the sheet tab name. What you described is the method used on chartobjects, which are usually on a worksheet. Cheers Andy -- Andy Pope, Mi...

Excel moving horizontal columns to vertical
I am trying to find a simple way of moving data in horizontal columns (referencing the key in column 1) to vertical colums (still referenceing the same key). This would mean automatically repeating the values in column one for every entry in the moved columns. I can do this through the pivot table, but this is a long drawn out process. Any ideas I'm not sure if this will work for you, but you can try this: - Highlight & copy on the horizontal value you want to make vertical - Now highlight the vertical area you want these value to got - Right Click when highlighted & choose...

Rename Cell
How can I rename column A to read "bills" instead of the letter A? You can't. The closest you will get is to hide column headings, via Excel Option, and then create your own. -- Regards Dave Hawley www.ozgrid.com "shoe" <shoe@discussions.microsoft.com> wrote in message news:DBA970DF-D928-41EE-9565-4639E7D49BCE@microsoft.com... > How can I rename column A to read "bills" instead of the letter A? you cant change the headers or row labels but you can define you data as a list (or table) and the headings can then be used to refer...

merging 2 cells without losing data?
How can I merge 2 cells without losing data from the other cell? Hi Bob Not possible I'm afraid. Try placing the dat from both cells into one and use "Center across selection" under Format>Cells>Alignment Merge cells always end up causing grief. they are best avoided. ***** Posted via: http://www.ozgrid.com Excel Templates, Training & Add-ins. Free Excel Forum http://www.ozgrid.com/forum ***** "bob" <bobree@hotmail.com> wrote in message news:%23JuOM9HGEHA.2308@tk2msftngp13.phx.gbl... > How can I merge 2 cells without losing data from the other...

Create static text from cell reference
Hey everyone... I have two columns of text which I'm combining in a third column using the formula (for C1, for example) =A1 & char(10) & B1 This gives me the contents of A1 on a line above the contents of B1 and works fine. What I NEED to do is somehow create column C as TEXT, not as a REFERENCED data from columns A and B. How do I create a cell that contains the actual TEXT content of another cell instead of a REFERENCE to the other cell? TIA... Select all the cells in "C" that have content. R-click them and select "Copy" then r-click again, sele...

loan amortisatio chart not updating
Hi, I use MS Monet 2007 premium. I have created a loan amortisation account which breaks up my monthly instslment into pricipal and interest. The loan commencd from 7 October 2005 and is for a period of 5 years. The problem is that the loan account does ot show any loan instalments beyond 7 October 2006 (exactly 1 year after the commencement). Why is this happening. Why is the account not updating with instalments which have been debited to my account after 7 October 2007. Please help. Were you depending on downloaded transaction data for this account? Did the download link br...

Insert,Update Data in sage (MS Access Linked tables) using Vb.net form
Hi folks, I am developing application using vb.net which requires integration with SAGE LINE 50 (Accounting software ) V11... The data which SAGE is using is MC ACCESS 2003 database... with linked tables in it... Now I Have developed the Sage connection using ODBC which works fine when reading the record but cannot Add or Update record into the Linked tables.... When i debug the program the error is at the line where it has... <br> MyodbcCommand.ExecutenonQuery() <br> Can anybody Help ????? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/acce...

Cell Format #4
Is there a way to have a cell format based on contents of an i statement... Example if(C1="Input",and(C3,Format \$#.##),if(C1="% of Revenue",and(C5,Forma #.##%),na) I want the If statement to test a condition, return contents of th correct cell and format automatically. Any help is appreciated -- bforster ----------------------------------------------------------------------- bforster1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1177 View this thread: http://www.excelforum.com/showthread.php?threadid=26133 You can't change the fo...

I want to divide the y1-axis column and save it to radius (y1/2) column. How do I do that? x-axis y1-axis radius(y1/2) 0 0.00 8.0000 1 0.25 8.0242 2 0.50 8.0691 3 0.75 8.1281 4 1.00 8.1989 5 1.25 8.2803 6 1.50 8.3716 7 1.75 8.4729 8 2.00 8.5832 hi divide the y1-axis by what? 2 as an guess with y1-axis in column c in the y1/2 column(d?), enter =C2/2 copy down. regards FSt1 "Rocky" wrote: > I want to divide the y1-axis column and save it to radius (y1/2) column. How > do I do that? > > x-axis y1-axis radius(y1/2) > 0 ...

How do I make a chart with several times during a day
Hi, This seems like it may be a simple thing, yet I can not for the life of me figure out how to do this (or at least semi-easily with VBing it for a while). I have a simple table filled with the following information (example for simplicity): 12pm 5pm 10pm 11/20 5 4 7 11/21 5 4 7 11/23 5 4 7 11/24 5 4 7 11/25 5 4 7 So basically I am keeping track of a numeric value three times a day. I would like to make a chart of it with...

converting tabular structures in a Word document into an actual table or reading data from the tabular structures using VBA code
I have a macro which can read the last cell/column of all tables in a Word 2003/2007 document and store the data in an MS-Access table. But, some Word documents have the data in structures like a table format but are not actually tables. The structure looks like a table, but the table borders are actually line connectors. These documents were created by a software(VeryPDF PDF to Word converter) which converted the PDF documents(the original format these documents were) into Word documents. 1. Is there a way I can convert/replace the tabular structures with actual tables in Word so t...

Using atl based win dll with CString functions from the mfc projec #3
I have atl based general windows dll with class which contains functions which uses CString as parameters or return values. This dll might be used from the atl or mfc project. Dll can be used from ATL project without problems but whenever I try to use this class from the MFC project I get the following linker errors: error LNK2019: unresolved external symbol "__declspec(dllimport) public: int __thiscall MyClass::AddMenu(long,class ATL::CStringT<wchar_t,class StrTraitMFC_DLL<wchar_t,class ATL::ChTraitsCRT<wchar_t> > > const &,long)" ... If I replace CStri...

Adding a certain text label in a excel chart
I am plotting in regularly basis a certain set of data in excel. Based on some data analysis this set of data has to be fitted to these equations: y = 1/x^a (1) and /or y = b/x^c (2) from data analysis, constants a, b and c are found and are placed lets say in cells A1, B1, C1. On my graph, I am putting then two small text labels where the real equation is displayed: smth. like: y = 1/ x^3.45 and / or y = 0.256 / x^3.12 The whole process is similar with excel curve fitting, when the “show equation on chart” is checked. Thank you in advance My question is: Can ...

survival chart
Hi! Does anybody have an idea how it would be possible to trick Excel into making a survival plot? That is a stepped graph ploting cumulative survival on the y axis and time slots on the x. Any suggestion would be most appreciated. Andrej Andrej - I don't have a 'survival plot' per se, but you could look for ideas on my web site. The cumulative probability plot sounds like a possible candidate: http://www.geocities.com/jonpeltier/Excel/Charts/ProbabilityChart.html If it doesn't give you any idea, post back with some typical data, and we'll have a look. - Jo...

Not using zeros in graphing.
I have a running workbook that has tons of information. I have added a sum page in order to have all the data summed up in one simple place. I have formulas that read back into the workbook to link to a cell. Depending on what moth it is, that cell could be empty as it is a yearly wookbook. For example, if this is August, then there is information in the workbook up to August, but none after. With that said, the sum page has the #DIV/0! in the cell which essentially equals zero. I also have graphs that I have linked to this sum page. My problem is in order to keep the graphs up to...

Chart "Benchmark" Line Graph Question
I am generating a graph of many team's monthly audit productivity percentages (bar graph) with a "benchmark" (line graph) at 95% (y axis) across 6 month period (x axis). The problem is that the line graph appears on the graph but the ends of the line are centered in the end columns (left & right). Is there as simple way to extend the benchmark line out to the edges of the graph? MJ Have a great day and live life with passion! ...

cell selection gone crazy on Excel 2003
All of a sudden the mouse is acting like it is held down, and will not stop selecting cells. Have tried double clicking, playing with the Function keys, all sorts of things, but to no avail... don't want to force quit. Any clues? TIA, Geri Hi Geri, See David McRitchie's notes at: http://www.mvps.org/dmcritchie/excel/ghosting.txt --- Regards, Norman "Tweedie-Vaughan" <Tweedie-Vaughan@discussions.microsoft.com> wrote in message news:438C3854-C74C-410A-BD88-DAA146172E99@microsoft.com... > All of a sudden the mouse is acting like it is held down, a...

Chart printing issue in Excel 2007
A spreadsheet with charts was created using Excel 2003. I have Excel 2007 and saved it in compatibility mode. I inserted a couple colored lines on the chart and created my own legend based on these. A couple of issues: 1. When I close the file or even minimize, 2 of the colored lines on a couple of my legends disappear upon reopening. 2. When I try to print a chart, it looks good in Print Preview, but then looks magnified,half off the page, and only one of my drawn lines is printed. When someone with 2003 prints, the sizing is correct, but all of the colored drawn lines are missing...

Average of logic cells
I used a logic test to determine some levels from raw scores. For EG >120 =5, 119-110 = 4, etc. I now want to dtermine an average score of several of the the results from the logic tests but it doesnt seem to work. (AVG does not recognise cells with logic tests) Can anyone help, please? -- ckdkvk ------------------------------------------------------------------------ ckdkvk's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29245 View this thread: http://www.excelforum.com/showthread.php?threadid=489704 hi, ckdkvk ! > I used a logic test to determine so...