If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
How do I use a single cell value to chart a horizontal line in Exc
On Fri, 27 Apr 2007, in microsoft.public.excel.charting,
Ira 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 , which goes to a spam folder-- please send your email to del3 instead. |
#3
|
|||
|
|||
How do I use a single cell value to chart a horizontal line in Exc
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 , which goes to a spam folder-- please send your email to del3 instead. |
#4
|
|||
|
|||
How do I use a single cell value to chart a horizontal line in Exc
http://peltiertech.com/Excel/Charts/AddLine.html
- Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Ira" wrote in message ... 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. |
#5
|
|||
|
|||
How do I use a single cell value to chart a horizontal line in
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 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 , which goes to a spam folder-- please send your email to del3 instead. |
#6
|
|||
|
|||
How do I use a single cell value to chart a horizontal line in
On Sat, 28 Apr 2007, in microsoft.public.excel.charting,
Ira 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 , which goes to a spam folder-- please send your email to del3 instead. |
#7
|
|||
|
|||
How do I use a single cell value to chart a horizontal line in
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: http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Ira" wrote in message ... 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. |
#8
|
|||
|
|||
How do I use a single cell value to chart a horizontal line in
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: http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Ira" wrote in message ... 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. |
#9
|
|||
|
|||
How do I use a single cell value to chart a horizontal line in
Oops!
- Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Ira" wrote in message ... 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: http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Ira" wrote in message ... 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. |
#10
|
|||
|
|||
How do I use a single cell value to chart a horizontal line in
On Sat, 28 Apr 2007, in microsoft.public.excel.charting,
Ira 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 , which goes to a spam folder-- please send your email to del3 instead. |
Thread Tools | |
Display Modes | |
|
|